We are now going to perform a second full database restore, this time using a T-SQL script, and the second full backup file from Chapter 3 (DatabaseForFullBackups_
Full_Native_2.bak), which was taken after we pushed another 500 MB of data into the database, bringing the total size of the database to just under 1 GB. You may recall from Chapter 3 that doubling the size of the database did increase the backup time, but it was not a linear increase. We'll see if we get similar behavior when performing restores.
Once again, we are going to overwrite the existing DatabaseForFullBackups
database. This means that we want to kill all connections that are open before we begin our RESTORE operation, which we can do in one of two ways. The first involves going through the list of processes in master.sys.sysprocesses and killing each SPID associated with the database in question. However, this doesn't always do the trick, since it won't kill connections that run on a different database, but access tables in the database we wish to restore. We need a global way to stop any user process that accesses the
Chapter 4: Restoring From Full Backup
For this reason, the second and most common way is to place the database into OFFLINE mode for a short period. This will drop all connections and terminate any queries
currently processing against the database, which can then immediately be switched back to ONLINE mode, for the restore process to begin. Just be sure not to kill any connec-tions that are processing important data. Even in development, we need to let users know before we just go wiping out currently running queries.
Here, we'll be employing the second technique and so, in Listing 4-2, you'll see that we set the database to OFFLINE mode and use option, WITH ROLLBACK IMMEDIATE, which instructs SQL Server to roll those processes back immediately, without waiting for them to COMMIT. Alternatively, we could have specified WITH ROLLBACK AFTER XX SECONDS, where XX is the number of seconds SQL Server will wait before it will automatically start rollback procedures. We can then return the database to ONLINE mode, free of connections and ready to start the restore process.
USE [master]
Listing 4-2: Dropping all user connections before a restore.
Go ahead and give this a try. Open two query windows in SSMS; in one of them, start the long-running query shown in Listing 4-3 then, in the second window, run Listing 4-2.
USE [DatabaseForFullBackups]
GO
WAITFOR DELAY '00:10:00' GO
You'll see that the session with the long-running query was terminated, reporting a severe error and advising that any results returned be discarded. In the absence of a third-party tool, which will automatically take care of existing sessions before performing a restore, this is a handy script. You can include it in any backup scripts you use or, perhaps, convert it into a stored procedure which is always a good idea for reusable code.
Now that we no longer have to worry about pesky user connections interfering with our restore process, we can go ahead and run the T-SQL RESTORE command in Listing 4-4.
USE [master]
GO
RESTORE DATABASE [DatabaseForFullBackups] FROM
DISK = N'C:\SQLBackups\Chapter3\DatabaseForFullBackups_Full_Native_2.bak' WITH FILE = 1, STATS = 25
GO
Listing 4-4: Native SQL Server full backup restore.
The RESTORE DATABASE command denotes that we wish to restore a full backup file for the DatabaseForFullBackups database. The next portion of the script configures the name and location of the backup file to be restored. If you chose a different name or location for this file, you'll need to amend this line accordingly. Finally, we specify a number of WITH options. The FILE argument identifies the backup set to be restored, within our backup file. As discussed in Chapter 2, backup files can hold more than one backup set, in which case we need to explicitly identify the number of the backup set within the file. Our policy in this book is "one backup set per file," so we'll always set FILE to a value of 1. The STATS argument is also one we've seen before, and specifies the time intervals at which SQL Server should update the "backup progress" messages. Here, we specify a message at 25% completion intervals.
Notice that even though we are overwriting an existing database without starting with a tail log backup, we do not specify the REPLACE option here, since
DatabaseForFull-Chapter 4: Restoring From Full Backup
Server will still overwrite any existing database on the server called DatabaseForFull-Backups, using the same logical file names for the data and log files that are recorded within the backup file.
In such cases, we don't need to specify any of the file names or paths for the data or log files. Note, though, that this only works if the file structure is the same! The backup file contains the data and log file information, including the location to which to restore the data and log files so, if we are restoring a database to a different machine from the original, and the drive letters, for instance, don't match up, we will need to use the WITH MOVE argument to point SQL Server to a new location for the data and log files. This will also be a necessity if we need to restore the database on the same server with a different name. Of course, SQL Server won't be able to overwrite any data or log files if they are still in use by the original database. We'll cover this topic in more detail later in this chapter, and again in Chapter 6.
Go ahead and run the RESTORE command. Once it is done executing, do not close the query session, as the query output contains some metrics that we want to record.
We can verify that the restore process worked, at this point, by simply opening a new query window and executing the code from Listing 4-1. This time the first query should return a million rows containing the same message, and the second query should also return a million rows containing the same, slightly different, message.