How to Back Up and Restore Database in Microsoft SQL Server


Connecting To Microsoft SQL Server Database Engine

1. Open your Microsoft SQL Server
2. Connect to Server Window will prompt. Supply the necessary information.

[1] Server Type defaults to Database Engine
[2] Server Name
[3] Authentication. If SQL Server Authentication, supply the Login Name [4] and Password [5].

 

3. Once done, you may proceed with clicking Connect.

Backing Up Database

1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
2. On the left panel, expand the Databases. Select your database.

3. Right-click the database, point to Tasks > Backup > The Back-Up Database dialog box appears.

4.  In the Database drop-down list, verify the database name. Optionally, you can select a different database from the list.
5. The Recovery Model text box is for reference only. You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE).
6. In the Backup type drop-down list, select Full.

7. For the Backup component, select the Database radio button.


8. In the Destination section, use the Back Up To drop-down list to select the backup destination.
9. Click Add to add additional backup objects and/or destinations. Once clicked, the Select Backup Destination window will prompt. Click the ellipsis button to select the location for your back up.





10. Once Locate Database Files window displays, you may specify the File Name.



11. To remove a backup destination, select it and click Remove. To view the contents of an existing backup destination, select it and click Contents.





12. Once done, you may now click Ok
13. Wait for the backup process to finish. Execute progress is displayed on the lower left of the window.


14. Once backup process is done, simply Click Ok.


15. You may now check the file in the folder you’ve selected. 








Restoring Database

1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
2. On the left panel, expand the Databases. Select your database.
3. Right-click the database, point to Tasks > Restore > Database. The Restore Database window will prompt.

4.  On the General page, use the Source section to specify the source and location of the backup sets to restore.  You may either select a Database or Device. If Database, select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history. If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. In this case, select Device to manually specify the file or device to restore.

5. If Device, Click the browse (...) button to open the Select Backup Devices dialog box. In the Backup Media Type box, select one of the listed device types. To select one or more devices for the Backup Media box, click Add.




6. After you add the devices you want to the Backup Media list box, click Ok to return to the General page.

7. In the Backup Sets To Restore, select the name of the database which should be restored. This list is only available when the Device is selected. Only databases that have backups on the selected device will be available.

8. To view or select other advanced options, you may go to the Options page. In the Restore options panel, you can select any of the following options, if appropriate for your situation: 

  • Overwrite the existing database (WITH REPLACE)
  • Preserve the replication settings (WITH KEEP_REPLICATION)
  • Restrict access to the restored database (WITH RESTRICTED_USER)



9. Click OK.
10. Wait for the restore process to finish. Execute progress is displayed on the upper right of the window.

11. Once restoration of the database is finished, simply click Ok.







Was this answer helpful? 0 Users Found This Useful (0 Votes)