MSSQL Database Backup and Recovery

MSSQL transaction management and Recovery models

The MSSQL DBMS records all its update activity in a set of transaction logs, at least one for each database. The data stored includes both the state of the data before the update was applied and also the update itself. This means that the DBMS can use the data in the log to back out, or 'roll back' partially committed transactions. This might be necessary after a hardware error or a database problem. When the updates are written out to the database, the transaction log records become 'inactive', but are still retained for 'roll forward' recovery.

Say you take a backup at 22:00 then get a file corruption at 08:00. You would restore the database from the backup, then roll forward using the transaction logs to recover the database right up to the last committed transaction. Transaction logs are often kept for a week or more to permit recovery from data corruption. They can grow rapidly if the database gets a lot of updates, so the logs themselves are often backed up, then deleted to maintain free space.

This leads to the three types of recovery model, and you have to decide which recovery model you are going to use before you plan your backups.
The Simple Recovery Model just restores a database from the last backup, and all updates made since that last backup will be lost. It can use Full, Differential or File backups
The Full Recovery Model restores a database from the last backup and then applies the transaction logs to get the database to a specific point in time. It needs Full backups and available transaction logs.
The Bulk Logical Recovery Model is a specialised type of backup and recovery for coping with the very large updates that happen on a database load (for example) and is not discussed further here.

Backup

SQL server supports several kinds of backup

• A Full database backup is a full copy of the database.
• A Partial backup excludes selected portions of the database, usually because they are read-only
• A Transaction Log backup copies only the transaction log.
• A differential backup copies only the database pages modified after the last full database backup.
• A Partial Differential backup is the differential equivalent of a partial backup
• File based backups copy parts of multi-file databases

Backups are recorded in the backup history tables in the msdb system database.

Backups using Transact-SQL

The backup command and syntax is quite simply

BACKUP DATABASE databasename
 TO device details
 WITH options  

The devices can be disk or tape, or logically defined devices. The options typically relate to differential backups and switching off compression. BACKUP LOG is used to backup the transaction logs.
Transact-JCL is suitable for scheduled backups. but manual backups might be easier with the SSMS.

Backups using SQL Server Management Studio (SSMS)

First, you need to get to the list of databases. Within the SSMS, expand a server group and then expand a server. Expand Databases and you see a list of all databases defined to that server. Right-click on the database you wish to backup, point to 'All Tasks', and then click on 'Backup Database' to see the backup database screen.
You get the following options in the backup database screen

• Select the backup type that you want from the Backup type box, one of Full, Differential, Logfile or File Group
• click on the Database backup component
• Give the backup an optional name
• Give the backup an optional description
• Specify when you want the backup set to expire
• Define the backup destination, tape drive or disk path
• Decide if this will overwrite an existing backup at that destination, or append to it.
• Define a schedule so this backup will run automatically in a regular time slot

You can also opt not to compress the backup, and say if you want the backup data to be verified as complete.

Backups using Third Party Products

Many third party products can interface with the MSSQL server to provide automated backup management. A couple of examples are

• Tivoli Storage Manager from IBM, which can use MSSQL commands to run backups and restores, and also interfaces directly to the Enterprise Manager. See the TSM MSSQL TDP section for more details.
• FDRupstream from InnovationDP which uses the MSSQL.exe program to interface to the SQL server.

The main advantage of using a third party product is that you can use their facilities for storing data over the network. Ideally you keep your backup server in a different building several kilometers from your database. You then have automatic off-site backups for disaster recovery. That is a lot easier than going around your servers every day changing tapes and shipping them offsite. The third part product also keeps an independent catalog of the backups, so you can find them if you lose your SQL server, or even your msdb.

Recovery

Recovery using SQL Server Management Studio

First, you need to get to the list of databases. Within the SSMS, expand a server group and then expand a server. Expand Databases and you see a list of all databases defined to that server. Right-click on the database you wish to restore, point to 'All Tasks', and then click on 'Restore Database' to see the restore database screen.
You get the following options in the restore database screen

• Select the database to recover from and optionally a new name to recover to.
• Restore to a point in time, if you are using the Full Recovery Model. The default is the latest available, but you can pick your own point in time by clicking a browse button, then selecting the time you want from those available.
• The 'backup sets to restore' grid will present you with a suggested recovery plan complete with a set of backup locations. You can override these with your own selection
• The 'Restore the database files as' grid allows you to restore the database to a different place
• You also get the option to decide what status the database will be in when it is recovered. It can be read-only, non-operational with uncommitted transaction not rolled back or fully available, with all backups and logs applied

Recovery using third party products

If you took your backup with a third party product, you will need to use the same product for a restore. If the product interfaces with the SSMS, then this can be totally transparent. Otherwise, you may use third party commands that contain embedded MSSQL commands. If you want to use TSM to recover to a different file server, then there is no 'set access' function within the SQLTDP to allow you to do this. You need to change to dsm.opt file so you connect to the TSM Server as the NODENAME that originally performed the backup.

Rebuilding and Recovering the Master Database

If you lose your master database then you will probably not be able to start your SQL Server. To recover from a master database failure, you need to go through the following steps.

1. Rebuild the master database
2. Restore the master database
3. Restore or re-attach the other databases

Rebuild the master database

You cannot recover any database unless the SQL server is started, and you cannot start the SQL server without a master database.In MSSQL 2000 You did this with the SQL server command rebuildm.exe and the MSSQL installation CD. For later versions of MSSQL you have to run the install process. Either way, when the rebuild is complete you will have a clean but empty master database that does not know other databases exist, or who is allowed to log into the server.

Restore the master database

If you restore the master from the last good backup, it will contain the locations of other databases on the server, SQL Server logins, etc. To restore the master database, you may need to add backup devices, or you may just use a third party product which supplies all this information for you. You must run SQL server in single user mode to recover the master database, so the recovery process is -

• Add backup devices
• Stop the SQL Server
• execute the following command from a DOS prompt, C:\Program Files\MSSQL Server\MSSQL\Binn> sqlservr.exe -c -m
  This will start SQL Server in single user mode as specified by the -m switch, and the output will be directed to the console.
  Alternatively, use the transact JCL commands ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK AFTER N SECONDS
• Restore the master database using SQL command RESTORE DATABASE MASTER.
• No log rollforward is possible so any updates made after the last backup must be applied again.

When(or if) the restore is successful, the SQL server shuts itself down automatically. Bring it up just as you normally would unless you need to make more repairs, like reconnecting databases created since the master backup, or deleting databases dropped since the last backup.

Restore or re-attach the other databases

You will want to recover the msdb first, as it contains the recovery information for the other databases. If it is intact, simply re-attach it using the SQL Server Management Studio. Otherwise restore it from backup. You can them re-attach or restore the rest of your databases as required. You may also need to resynchronize SQL Server logins and users if there are orphans.

Potential Problems in Rebuilding and Restoring the master Database

The rebuildm.exe command (SQL2000) should only take a few minutes. It it gets stuck then it may have problems with the read-only attributes on the CD for the master data files. Copy them to a local hard drive and then run the following command:

C:\tmp\master> attrib -r *.*

This will take the read-only attributes off and the rebuildm.exe should run.

You may get the following error when restoring the master database

 RESTORE DATABASE must be used in single user mode when trying 
 to restore the master database.
 Server: Msg 3013, Level 16, State 1, Line 1
 RESTORE DATABASE is terminating abnormally.

Put the SQL Server into single-user mode via the command line. Execute the following from the BINN directory:

sqlservr.exe -c -m

Trying to put the master database into single-user mode via sp_dboption or Alter database results in the following error

sp_dboption master, 'single user', true
go
/*
Server: Msg 5058, Level 16, State 5, Line 1
Option 'SINGLE_USER' cannot be set in database 'MASTER'.
sp_dboption command failed.
*/

Place the SQL Server into single-user mode via the command line. You need to stop SQL Server and restart it by executing the following from the BINN directory:

sqlservr.exe -c -m

back to top

Copyright © Lascon Storage Ltd. 2000 to present date. By entering and using this site, you accept the conditions and limitations of use