The master database is the control database for a SQL Server instance, and restoring it is a slightly trickier task; we can't just restore master while SQL Server is running in standard configuration.
The first thing we need to do is turn the SQL Server engine service off! Go back to the services management tool, find the service named SQL Server (instance) and stop it, as described previously. You may be prompted with warnings that other services will have to be stopped as well; go ahead and let them shut down.
Once SQL Server is offline, we need to start it again, but using a special startup
parameter. In this case, we want to use the –m switch to start SQL Server in single-user mode. This brings SQL Server back online but allows only one user (an administrator) to connect, which is enough to allow the restore of the master database.
To start SQL Server in single-user mode, open a command prompt and browse to the SQL Server installation folder, which contains the sqlservr.exe file. Here are the default locations for both SQL Server 2008 and 2008 R2:
• <Installation Path>\MSSQL10.MSSQLSERVER\MSSQL\Binn
• <Installation Path>\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
From that location, issue the command sqlservr.exe –m. SQL Server will begin the startup process, and you'll see a number of messages to this effect, culminating (hopefully) in a Recovery is complete message, as shown in Figure 4-9.
Figure 4-9: Recovery is complete and SQL Server is ready for admin connection.
Once SQL Server is ready for a connection, open a second command prompt and connect to your test SQL Server with sqlcmd. Two examples of how to do this are given below, the first when using a trusted connection and the second for a SQL Login
Chapter 4: Restoring From Full Backup
• sqlcmd -SYOURSERVER –E
• sqlcmd –SYOURSERVER –UloginName –Ppassword
At the sqlcmd prompt, we'll perform a standard restore to the default location for the master database, as shown in Listing 4-10 (if required, we could have used the MOVE option to change the master database location or physical file).
RESTORE DATABASE [master] FROM
DISK = 'C:\SQLBackups\Chapter4\master_full.bak' GO
Listing 4-10: Restoring the master database.
In the first sqlcmd prompt, you should see a standard restore output message noting the number of pages processed, notification of the success of the operation, and a message stating that SQL Server is being shut down, as shown in Figure 4-10.
Figure 4-10: Output from restore of master database.
Since we just restored the master database, we need the server to start normally to pick up and process all of the internal changes, so we can now start the SQL Server in normal mode to verify that everything is back online and working fine. You have now successfully restored the master database!
Summary
Full database backups are the cornerstone of a DBA's backup and recovery strategy.
However, these backups are only useful if they can be used successfully to restore a database to the required state in the event of data loss, hardware failure, or some other disaster.
Hopefully, as a DBA, the need to restore a database to recover from disaster will be a rare event, but when it happens, you need to be 100% sure that it's going to work; your organi-zation, and your career as a DBA, my depend on it. Practice test restores for your critical databases on a regular schedule!
Of course, many restore processes won't be as simple as restoring the latest full backup.
Log backups will likely be involved, for restoring a database to a specific point in time, and this is where things get more interesting.
Chapter 5: Log Backups
When determining a backup strategy and schedule for a given database, one of the major considerations is the extent to which potential data loss can be tolerated in the event of an errant process, or software or hardware failure. If toleration of data loss is, say, 24 hours, then we need do nothing more than take a nightly full backup. However, if exposure to the risk of data loss is much lower than this for a given database, then it's likely that we'll need to operate that database in FULL recovery model, and supplement those nightly full backups with transaction log backups (and possibly differential database backups – see Chapter 7).
With a log backup, we capture the details of all the transactions that have been recorded for that database since the last log backup (or since the last full backup,if this is the first-ever log backup). In this chapter, we'll demonstrate how to capture these log backups using either the SSMS GUI or T-SQL scripts. However, we'll start by taking a look at how space is allocated and used within a log file; this is of more than academic interest, since it helps a DBA understand and troubleshoot certain common issues relating to the trans-action log, such as explosive log growth, or internal log fragmentation.
Capturing an unbroken sequence of log backups means that we will then, in Chapter 6, be able restore a full backup, then apply this series of log backups to "roll forward" the database to the state in which it existed at various, successive points in time. This adds a great deal of flexibility to our restore operations. When capturing only full (and differ-ential) database backups, all we can do is restore one of those backups, in its entirety.
With log backups, we can restore a database to the state in which it existed when a given log backup completed, or to the state in which it existed at some point represented within that log backup.