Backup and Recovery Options of SQL Server

Database corruption is the part of many database user lives and it gives the chances to explore new things every day. This blog is about the advanced backup and restore options of SQL server database. All DBA’s understand the importance of backup and restore command but sometimes a lesser-known option can be very useful. Let’s see the different options:

Verifying the Backup Integrity: Most of the time; I found that experts easily neglect backup integrity while it plays an important role in database. SQL server 2005 introduced page checksums feature and it works as the page corruption detector of the data file. This function is by default enable in the SQL server 2005 and if you need to enable them then upgrade the database from previous version.

You can use the BACKUP command with CHECKSUM option to confirm that page checksums are checked. This option is very important because it notifies you when your backup was corrupted. If it finds page corruption then backup will fail by default. At this time user get the following error message on the screen:

Msg 3043, Level 16, State 1, Line 1

Backup ‘broken detected an error on page (1:143)

in file ‘C:\.....\broken.mdf’.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Some DBA’s use the RESTORE command with VERIFYONLY option to check the integrity of the database but, it only checks BACKUP header not the data inside BACKUP. You have to use RESTORE command with CHECKSUM and VERIFYONLY options to restore the process and recheck the all pages and entire BACKUP checksums.   

Minimize the Data Loss: When a corruption occurs in the database then, many users start restoring the database from backup. At this situation it is a possibility of the last portion of the transactional log disappears forever. This kind of backup is called tail-log backup. This backup is most useful when you are using synchronous database mirroring.

If you are using tail-log backup and the data files are missing then, you will get the following error message on your screen:

Msg 945, Level 14, State 2, Line 1

Database ‘DBMine2008’ cannot be opened due to

inaccessible files or insufficient memory or disk space.

See the SQL Server errorlog for details.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

In this case, you can use NO_TRUNCATE option with BACKUP statement because it allows the backup to proceed. 

Note: In SQL Server 2005 and later versions, a database that has tail-log backup cannot be overwritten by the restore option unless you have to use REPLACE option.

Point in Time Restoring: Let’s assume a scenario, you deleted the data and you need to restore the data from the log backup created right before the deletion. If you know the time of data deletion then simply you will choose the STOPAT option to restore it. If you don’t know about the timing of data deletion then, you have to use STOPAT and STANDBY options to progress through the log and check by small amount of time to reach on the correct point.

You can also use its alternative which is marked transaction. It creates log mark in transactional log. For example:

BEGIN TRANSACTION JohnsTran WITH MARK;

You can use this known point during database restoration. STOPATMARK can be used if you want to include marked transaction and STOPBEFOREMARK option to exclude marked transaction. To use these options you should have the information about the name of log mark. If you haven’t then you can find in the logmarkhistory table in msdb. If you lost this information then, it is difficult to find the log mark names manually. In this case you have to use a third party tool.

Restart Interrupted Restore Option: RESTORE command has many options and WITH RESTART is one of them. It is less-known option which uses to restart an interrupted restore option. This restores operation works on the checkpoint file which has the information of point to which restore has progressed. You can check this file on \InstanceName\MSSQL\Backup folder location.

A checkpoint file is updated by the following reasons:

1. After the processing of each and every backup set.
2. When the redo part of the recovery is finished.
3. At the time of database file creation after completion of zeroing.

You can read more about zeroing here: https://www.sqlpassion.at/archive/2014/02/18/improving-sql-server-performance-by-using-instant-file-initialization/

When you use this WITH RESTART option on the absence of checkpoint file then, you can get this error message:

The restart-checkpoint file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\RestoreCheckpointDB8.CKP’ was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified. 

This option is best for the large backup which has many taps because you can restart the restore option without going back to the first tape. It is also suitable to restore disk-based backups.

Other Options: There are many options which are lesser-known option which I said at the starting of blog so, you should try these options. By using BUFFERCOUNT and MAXTRANSFERSIZE options you can get the great performance to modify the I/O buffer. PAGE and PARTIAL options reduce the downtime when disaster occurs and these are used to single-page and piecemeal.

Conclusion: I hope you like these options and curious to know more about them. Database backup and restore is the very vast topic and used by many database users. Few corruptions are very hard to solve and at this point I can only suggest any trustworthy SQL database repair tool to repair your valuable database.