Section 18 - Backup / Restore Microsoft SQL Server

This section describes how to backup and restore Microsoft SQL Server with the OBM.

18.5.3 Restore Microsoft SQL Server 2012 database

Table of Contents:



• 18.5.3.1 Restore master database


• 18.5.3.2 Restore other system or user database

18.5.3.1 Restore master database

Back to top

Refer to the following instructions to restore the master database:




• Stop the SQL instance.
• Restart the SQL instance in single-user mode.


• In SQL Server Configuration Manager, click [SQL Server Services].
• In the right pane, right-click SQL Server, and then click [Properties].




• On the Startup Parameters tab, type the parameter .m in the specify startup parameter box and click on [Add] and click on [OK]




• Restore the database engine afterward.



Important:


When you start SQL Server in single-user mode, first stop SQL Server Agent.
Otherwise, SQL Server Agent might connect first, preventing you from connecting as a second user.






3. To restore a full database backup of master, use the following RESTORE DATABASE Transact-SQL statement:

>RESTORE DATABASE master FROM ${backup_device} WITH REPLACE




${backup_device} is the database file restored from the OBS:

C:\>sqlcmd
1>RESTORE DATABASE master FROM DISK = .C:\temp\master.bak. WITH REPLACE;
2>GO




4. After master is restored, the instance of SQL Server shuts down and terminates the sqlcmd process.

5. Before you restart the server instance, ensure to remove the single user startup parameter that you have added previously in Step 2.

6. Restart the server instance and continue other recovery steps such as restoring other databases.

18.5.3.2 Restore other system or user database

Back to Top

Refer to the following instructions to restore a backup:





• Open SQL Server Management Studio; connect to the server where the backup is to be restored.
• Expand [Databases].



Depending on the database, either selects a user database or expands [System Databases], and then selects a system database.






• Right click the database, point to [Tasks], clicks [Restore], and then click [Database].



Note:
SQL Server databases may be backed up on one server and restored to another server.

If the target database does not exist on the server, right click on any database instead. A new database can be created while restoring the backup.






• On the General page, the name of the restoring database appears in the [To database] list box.



To create a new database, enter its name in the list box.






• Select Source > Device and click the browse button to open the [Select backup device] dialog box. Click [Add].
  
• Browse to the backup file (restored from the OBS) that is to be restored.




• After adding the complete database file and transaction log files to the [Backup media] list box, click [OK] to return to the General page.
• In the [Backup sets to restore] grid, select the backups to restore.
  
  
  
  
• To restore the backup to the last backup state click [OK] button. If you want to restore the backup to a specific time line go to the next step.




With Timeline option




• Click on the [Timeline] button to restore the backup to a specific time line




• Choose the [Specific date and time] button, and you can select the specific time to restore. Click [OK] to confirm.




• Click [OK] to start restore, or if you need to relocate the restore files on another folder, you can go to the next option.




With relocate all file and folder option




• Click on the "Files" page on the left panel.




• Click on the check box "Relocate all files to folder" and you can select the folder path by clicking the folder selection button on the right hand side.




• Choose the folder path that you need the files to relocate and click OK.




• Click [OK] on the "Files" page to start the restore. If Taillog backup restore is required, go to the next option.




With Taillog backup restore option




• Click on the "Option" page on the left panel.




• Click on the check box of the "Take tail-log backup before restore" option, and also select the backup file destination from the right hand side.




• Click [OK] to start the restore.



Back to start