Tag Archives: restore

100 percent restored, what else is going on?

Starting this Monday some of my team members are working on a Disaster Recovery activity. This involves restoring some of the biggest Data Warehouse databases onto the DR server.

Today they were supposed to restore a database which is ~3 TB in size from disk backups. This instance is running SQL Server 2000. As usual they started the restore script using a SQL Agent job. The job output file showed the progress steadily and finally after 12 hours the output showed 100 percent completed. The output file looked something like this.

100 percent restored. [SQLSTATE 01000]
Processed 53369168 pages for database 'dbname', file 'dbname_Data' on file 1. [SQLSTATE 01000]
Processed 2945917 pages for database 'dbname', file 'dbname_Log' on file 1. [SQLSTATE 01000]

But the database restore is not complete until it prints RESTORE DATABASE successfully processed xxx pages in xxx seconds in the logs. When the DBA announced that the log had 100 percent completed in it, the Application Team were keen to do their part of testing and be done with the DR activity.

What's going on?

DBA told everyone on the bridge that it will take some “more” time for the database to be restored successfully. The obvious question was “How long?”. Since this instance is running SQL Server 2000, there is no way to estimate the time it takes to restore the database. If the instance was running SQL Server 2005 or above, it can be easily be tracked as I posted here. Three hours past, the database was still restoring and the nagging from the Application Team started getting more serious. The DBA on call was at the mercy of sysprocesses to check the status. All he could see was a restore session in which the waitresource column was changing. This confirmed that the Restore process was not hung but it was actually doing something.

What exactly the RESTORE session is doing? The RESTORE process has three phases.

  1. Data Copy Phase
  2. Redo phase
  3. Undo phase

In the Data Copy phase, all the data from the backup set is moved to the database files. Once this phase is complete, SQL Server reports that the restore is 100 percent complete and Processed xxx pages for database ‘dbname’, file ‘datafiles’ on file x.

In the Redo phase, all the committed transactions present in the transaction log when the database was being backed up are rolled forward.

In the Undo phase, all the uncommitted transactions in the transaction log while the database was being backed up are rolled back. If the database is being restored with NORECOVERY, the Undo phase is skipped. Unfortunately SQL Server does not print the progress during the Redo and Undo phases as it does in the Data Copy phase. This article on MSDN has good information regarding the Restore process.

With this information it is easy to understand why the restore was not showing any progress. Restore had progressed beyond the Data Copy phase and it was in the Redo/Undo phase. The database being restored was in simple recovery mode and the transaction log of the database inside the backup file was showing a size of 30 GB. The database backup had taken 12 hours to complete. Understandably the Redo/Undo phase had to roll forward/roll back the transactions that got logged in the transaction log during those 12 hours.

The DBA knew this and tried his best to convince the guys on the call. But there was no evidence to prove DBA’s argument. Finally the database restored completely after ~16 hours (12 hours of data copy + 4 hours of redo/undo). Everyone lived happily ever after 😉

Is that backup set valid?

The last ten days my team has been working on an issue where the Log Shipping broke due to DST change. During DST change some transaction log backup files got deleted and we had to re-sync the Secondary databases. So we took the FULL backup of the Primary databases and started copying them. Since the databases are huge in size and the connectivity is slow, by the time the FULL backup gets copied to the Secondary instance there would be several new transaction log backup generated on the Primary instance. Last weekend, we were left with only a couple of databases to be restored.

Like I said earlier, the connectivity between the two servers is slow and we were dependent on some other team to get the files copied to the Secondary server. Once they copy the files DBA would jump into action to restore them. During the copy process, some files would not get copied correctly and the devil will jump in when the DBA is trying to restore the database. In order to avoid the surprises during restore, we decided to verify the backup sets before restoring them.

The FULL backups were mostly striped across multiple drives. DBA chose to run RESTORE HEADERONLY on these backup files. The only reason for this choice was that HEADERONLY completes quickly when compared to other options of verifying the backup file validity. This is not a good choice to verify striped backups. Because in striped backups the important header information is only available in the First backup file. If the first file is valid then RESTORE HEADERONLY reports the Header information of the Backup set correctly. In the below example I take the backup of master database striped across multiple files.

DISK = 'C:DBBACKUPSmaster1.bak',
DISK = 'C:DBBACKUPSmaster2.bak',
DISK = 'C:DBBACKUPSmaster3.bak',
DISK = 'C:DBBACKUPSmaster4.bak',
DISK = 'C:DBBACKUPSmaster5.bak'

The files get backed up to C:DBBACKUPS folder.

I will replace master2.bak with an empty file with the same name. Then run RESTORE HEADERONLY on the backup set.

DISK = 'C:DBBACKUPSmaster1.bak',
DISK = 'C:DBBACKUPSmaster2.bak',
DISK = 'C:DBBACKUPSmaster3.bak',
DISK = 'C:DBBACKUPSmaster4.bak',
DISK = 'C:DBBACKUPSmaster5.bak'

Since the first file i.e. master1.bak file is intact, as expected the query would claim that the backup set is valid which is not the case.

If the first file was corrupt, then the BackupName in the above output would be marked Incomplete. The best approach to verify the validity of the backup files is to run RESTORE VERIFYONLY on the backup files. This verifies that the entire backup set is complete and the contents of the backup files are readable. However this does not guarantee that the data structure within the backup files are valid. The other downside to this command is that it may take the same or more time to complete than it to backup the database! All these options are useful to find if the backup media is valid but the data structure issues will come into picture while doing the actual Restore.

Does RESTORE VERIFYONLY command help in this example. It certainly does.

DISK = 'C:DBBACKUPSmaster1.bak',
DISK = 'C:DBBACKUPSmaster2.bak',
DISK = 'C:DBBACKUPSmaster3.bak',
DISK = 'C:DBBACKUPSmaster4.bak',
DISK = 'C:DBBACKUPSmaster5.bak'

The output correctly states that the master2.bak file is empty.

Msg 3254, Level 16, State 1, Line 1
The volume on device 'C:DBBACKUPSmaster2.bak' is empty.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Suppose there are multiple files which did not get copied properly. Say master2.bak and master4.bak files are corrupt. VERIFYONLY will terminate stating that the master2.bak file is corrupt. By default RESTORE VERIFYONLY uses the CONTINUE_AFTER_ERROR arguement where as RESTORE command defaults to STOP_ON_ERROR. The CONTINUE_AFTER_ERROR argument forces SQL Server to restore whatever it can. The pages that fail verification in this process are written to errorlog and to suspect_pages table in MSDB which can hold only 1000 rows. My fellow SME who was working on this issue this weekend told me about this very useful feature which I was unaware of. More information on how SQL Server responds to restore errors can be found in this article. But a corrupt backup file is something which renders even the CONTINUE_AFTER_ERROR argument invalid.

In our case, we started using RESTORE VERIFYONLY on the striped backup files and whichever files were reported to be corrupt were copied again. This process was repeated until all the VERIFYONLY command completed successfully. Hopefully by now all the databases on that instance are synched. I am yet to check my mails.

With this I conclude that it is worth the time to run VERIFYONLY on the backups at least once in a while to avoid surprises at the time disaster.

Differential backup of master database?

Someone posted a question yesterday. “Is it possible to take the differential backup of master database?”. The answer is No. Why not try taking the differential backup of master database instead. Open SSMS and the backup option looks like this.

Only FULL backup type is listed. So SQL Server does not allow any other type of backup operation to be performed on master database. Let us try out the script to take the differential backup of master database.

Here as well SQL Server states the same. You can only perform a full backup of the master database. It is confirmed that SQL Server is designed to allow only FULL backup of master database.

But why? For differential backups to be applied on a database, the database has to be in NORECOVERY (Recovering) state. But SQL Server does not allow master database to be restored without recovery. Master database has to be in Recovered status always.

Moral of the story is you can only take FULL backup of master database.

Restore only the data file from a database backup

Yesterday I came across a question “Is it possible to restore only the .mdf file from a database backup? “. Apparently, the user wanted to restore the database on a new server which had limited space and the transaction file was not required to restored on the test server.

Now the answer to this question is a big Yes. It is possible to restore only a few files from the database backup using the FILE syntax explained here on MSDN.

I am reproducing the same using this example. First I am creating a new database named MDFRestoreExample.

( NAME = N'MDFRestoreExample' , FILENAME = N'C:MDFRestoreExample.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
( NAME = N'MDFRestoreExample_log' , FILENAME = N'C:MDFRestoreExample_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10 %)

After that I am taking the database backup to a local drive.

BACKUP DATABASE MDFRestoreExample TO DISK = 'C:MDFRestoreExample.bak'

Now that we have the backup, I will drop the database.


Now before restoring, I will check what files does the database backup contain, using this query.


The output will be as in the below screen shot.

Here the requirement is to restore only the Data File (MDFRestoreExample) from the backup files. The FileID is 1 for the data file and we want to restore only that file. Here is the query which does it.

RESTORE DATABASE MDFRestoreExample FROM DISK = 'C:MDFRestoreExample.bak'
       FILE = 1 ,

In this query I am instructing SQL Server to restore only the File 1 in the backup set. However a database cannot be ONLINE unless it has a transaction log associated with it. So the RECOVERY option will force SQL Server to rebuild the transaction log and brings the database ONLINE.

After executing this query, sp_helpfile confirmed that the new database was online with a new transaction log.