MS SQL Backup and Restore Strategies

Introduction

One of the most significant benefits of running MS SQL is the database recovery model. Your company’s information is its life-blood, and there are myriad methods to backup and preserve it. We will be discussing some basic techniques to preserve your data in a simple and efficient manner. We will not delve into Mirroring, Transaction Log Shipping or Replication; merely the actual backup of individual databases. The backup operations are dependent on the type of backup recovery model used.

The MS SQL default is Full recovery model, which is the model we will be discussing in this article as this is the most commonly used model. We will be using the MS AdventureWorks2008 database as our test database in this article. It is available for download from a Microsoft partner website AdventureWorks sample databases on github. Other backup strategies such as backing up full-text search catalogs and very large databases are not discussed in this article.

Types of Recovery Models

Types of backups

Creating a Full Backup

Using sqlcmd or SQL Query Analyzer in the SQL Query Management Studio tool, enter the following code to create a Full backup device and backup file:

USE MASTER
EXEC sp_addumpdevice 'disk', 'AdventureWorks2008 Full Test', 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLBackupAdventureWorks2008Full.dat'
USE MASTER
BACKUP DATABASE AdventureWorks2008 TO 'AdventureWorks2008 Full Test'
WITH DESCRIPTION='Full Adventureworks 2008 Backup' 

1. Highlight the database you wish to backup
   
   
2. Right-click the database and select “Tasks”, “Back Up…”
   
   
3. If you would like the backup to expire, enter an integer value.
   1. For general usage enter an integer between 7 and 30 days inclusive
      
      
4. The default device is a disk file with a “bak” file extension.
   1. If you have an off-line or on-line backup repository specify this now.
      
      
5. Click the <OK> button to create the backup.

Restoring a Full Recovery Model Backup Step-by-Step

Below we will give an example of a specific type of restore operation involving restoring the AdventureWorks2008 database using the Full recovery model from a Full backup, Differential backup and a transaction log backup.

As mentioned above, immediately prior to our Restore operation we need to backup any uncommitted transactions that may still be in the log. From the SQL Query window in the Management Studio logged into the correct SQL Server instance or from a SQLCMD command prompt logged into the correct SQL Server instance type (Note the filename and pathname are user defined; just make sure the directory path exists):

BACKUP LOG AdventureWorks2008 TO
DISK='c:tempAdventureWorks2008EmergencyLog.bak' WITH NORECOVERY

Next we need to restore the last good full backup. Make sure you use the NORECOVERY option. We cannot fully recover our database and allow users back in until we have applied all the files necessary to complete the Restore operation. Note also that since we have two files included in our Full backup we need to restore the second file (unless we are just initiated our backup job less than two weeks prior). In our example enter the following (referring to the files we created in our Backup Strategies session):

RESTORE DATABASE AdventureWorks2008 FROM "AdventureWorks2008 Full Test" WITH File = 1, NORECOVERY

The next step is to restore the last good differential backup. For our example we can assume we have a single Differential backup file to restore. Our command should be as follows:

RESTORE DATABASE AdventureWorks2008 FROM "AdventureWorks2008 Diff Test" WITH NORECOVERY

To restore the log file, run the following command:

RESTORE LOG AdventureWorks2008 FROM "AdventureWorks2008 Log1 Test" WITH NORECOVERY

The last SQL command verifies and activates the database for use:

RESTORE DATABASE AdventureWorks2008 WITH RECOVERY

Creating and scheduling automatic Backups

We will need to schedule each of our backup jobs using the task scheduler. We will be running a single command, sqlcmd with the appropriate parameters. A typical command would look like the following:

Sqlcmd /S <server nameinstance>/E /Q "EXEC sp_BackupAdventureWorksTest @backupType='F'"

Caveat running sqlcmd: sqlcmd is particular about the syntax and text case. Make sure you use single-quotes around T-SQL string parameters and double-quotes around the T-SQL query command we are running. The stored procedure parameter text case must match the variable declaration exactly. This is a pain in the neck, but much worse if you make an error and have to track it down! Although you can use a hyphen (“-“) in front of the sqlcmd parameters, I have had better success using a “/” character. Watch out, because this may change in the future.

Scheduling our Task

Use the “Task Scheduler” Windows utility to create a backup schedule for your database. If you do not know how to configure, please refer to this MSDN knowledgebase articles Windows XP Tasks and Windows Vista Tasks.