MSSQL Database Backup and Recovery

MSSQL transaction management and Recovery models

Why do we need transaction logs to be able to recover a database to any point in time?

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 MSSQL 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. No point in time recovery is possible.
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.

In SQL Server 2012 SP1 CU2, MSSQL introduced the ability to back up to Azure, Microsoft's cloud offering, using the TO URL command. This initial offering allowed backups to Azure page blob storage, with a size limit of 1TB. SQL Server 2016 extends this to block blobs, where each block blob can be up to 200 GB and backups can be striped across multiple blobs. This increases the size limit to 12.8 TB. Another benefit is that block blobs cost less per month than page blobs.

SQL Server 2014 introduced Managed Backup to Azure, the purpose being to simplify the effort needed to plan your backup strategy and designing the scripts needed to manage it. A traditional point in time restore process involves restoring the full backup, then maybe the most recent differential, then restoring the logs in the correct order. SQL Server 2016 simplifies managed backups further creating snapshots of the entire database every time a log backup is taken. This works with new commands; BACKUP DATABASE…WITH FILE_SNAPSHOT and BACKUP LOG…WITH FILE_SNAPSHOT.
A database restore then just needs one command to restore the snapshop, not several restore commands that all have to happen in the correct sequence, so restores are faster and more reliable. You can still restore to a point in time by restoring the last log backup snapshot before the point in time with NORECOVERY, then restoring the log backup snapshot that contains the time you want using STOPAT, to get to a specific point in time.

This feature is restricted to Azure VMs for the initial release of SQL Server 2016. As snapshots cost money, you will need to consider how many snapshops you need to retain.
Some limitations to the managed backup feature in SQL Server 2014 have been removed in SQL Server 2016, including; Support for Databases in Full, Bulk logged, and Simple recovery models, System databases can be backed up with managed backups and Block blobs are now used. Also you can schedule backups by day of the week, instead of them being based on the transaction activity in the 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, tape or URL. URL is used to store your backup in Microsoft's Cloud offering, the Windows Azure blob storage. Azure has the capability to restore a backup of a local database to a Windows Azure hosted VM, if you have a complete disaster and lose your site.
The options typically relate to differential backups and switching off compression.
T-JCL is suitable for scheduled backups. but manual backups might be easier with the SSMS.
If you want to create backup scripts that can be scheduled to run, then you can generate T-SQL statements in SQL 2014 by clicking on the 'script' button at the top of the 'Backup Database window.
BACKUP LOG is used to backup the transaction logs. (Note that you cannot recover to a point in time from a full backup only. You need a full backup plus log file backups to do this)

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 'Tasks', and then click on 'Backup' to see the backup database screen.
You get the following options in the backup database screen

First a quick word on recovery models.
A Simple recovery means you can just recover from a Full database backup, or a Full, then a Differential backup. The recovery point is the end of that backup and any subsequent changes must be re-applied.
A Full recovery means you can recover from a Full database backup, apply a Differential backup then forward recover through transaction logs to any point in time. This is the most flexible model and the one that results in least data loss.
Some transactions involve bulk updates to a database and these can result in big log files with the Full model. Examples would be create index or bulk copy. The Bulk-logged model tries to cope with this by not logging bulk transactions. So it is possible to recover to a point in time, provided no bulk transactions were applied between the last backup, and that point in time. Otherwise the recovery point is the end of the most recvent backup.

  • The recovery model in the second drop down menu can be Simple or Bulk-logged or Full
  • The backup type in the third drop down menu can be Full or Differential
  • If you can select a Bulk Logged or Full recovery model, then you can select Logfile or File Group Components. A Simple model defaults to Database
  • 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

Recovering from a Snapshot

Snapshot recovery has a few pre-requisites and complications to consider.
You will overwrite your original source database with the snapshot database and any updates that you made to the original database after the snapshot is taken will be lost. Also, the snapshot restore operation will overwrite the old log file and rebuild the log, which means that you cannot use the logs to roll the database forward to the point where the error occured.
Of course, you can back up the log before you run the restore and even though you cannot use that original log for roll-forward, you can get at the information in the log file if you must reconstruct the log data.
As the restore breaks the log backup chain, you need to take a full database backup before you can take any log backups of the restored database.
You can only have one active snapshot when you run the restore, so you need to check out what snapshots are available, mark the one you want then delete the rest of the snapshots.
If you are happy with this then the command to run the restore is

RESTORE DATABASE database_name
FROM DATABASE_SNAPSHOT='snapshot_name'

Traditional 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 'Tasks', and then click on 'Restore' 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. With SQL 2012 click the 'Timeline' button and a new window opens that allows you to use sliders to pick out the exact time you want.
  • 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
  • From SQL 2005 onwards, it is possible to restore individual database pages rather than the entire database

Traditional 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 FROM backup device WITH REPLACE.
  • 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