Troubleshooting SQL Server Backup/Restore Problems

November 25, 2002

Should you have problems with backup/restore, review this troubleshooting checklist to find potential solutions.

1. Ensure that you are using the latest SQL Server service pack.

To check which SQL service pack are you running, see this link: How can I check what SQL service pack am I running?

2. Ensure that you have the appropriate permissions to make a backup/restore.

To backup a database or transaction log, you should have BACKUP DATABASE or BACKUP LOG permissions granted to you, or you should be a member of the sysadmin fixed server role, or of the db_owner or db_backupoperator fixed database roles.

To restore a database, you should have CREATE DATABASE permissions if the database being restored does not exist. If the database being restored does exist, you should have RESTORE permissions granted to you, or you should be a member of the sysadmin or dbcreator fixed server roles, or you need to be the owner (dbo) of the database.

3. If you want to make a backup of the transaction log, check that the 'trunc. log on chkpt' option is turned off.

You cannot make a backup of the transaction log if the 'trunc. log on chkpt' option is turned on, because in this case the transaction log will be truncated on checkpoint and will be empty.

4. If you encountered Error 156 during backup or restore operations, before attempting additional BACKUP or RESTORE statements, set the SQL Server compatibility level to 70 if you work with SQL Server 7.0, or to 70 or 80 if you work with SQL Server 2000.

The error 156 indicates that the syntax of a Transact-SQL statement is incorrect. One of the causes for this error may be that the database is in SQL Server version 6.5 compatibility mode. Because the BACKUP and RESTORE keywords are valid only with SQL Server 7.0 and SQL Server 2000 databases, you should set the SQL Server compatibility level to 70 or 80.

5. If you encountered Error 1511 during restoring a database or transaction log dump, enable trace flag 3222 to disable the read ahead that is used by the recovery operation during roll forward operations.

To disable the read ahead, you can issue DBCC TRACEON (3222) on the same connection on which you attempt the restore operation. This is a known SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.

6. If you encountered Error 3023 during a backup operation, reissue the backup operation after the conflicting operation has completed.

The error 3023 indicates that the BACKUP statement cannot be performed at the same time as creating, deleting, or shrinking database files. So, to resolve this error you should reissue the backup operation after the conflicting operation has finished.

7. If you encountered Error 3036 during a backup operation, use backups from your primary server until operations have switched to the standby.

The error 3036 indicates that the database is in the warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed.

8. If you encountered Error 3143 during a restore operation, use RESTORE HEADERONLY to determine the backup contents.

The error 3143 indicates that the backup being restored is a valid Microsoft Tape Format but is not a SQL Server backup. This error may arise when the backup may have been written by another software product. In this case, use RESTORE HEADERONLY to determine the backup contents.

9. If you encountered Error 3154 during a restore operation, overwrite the existing database by reissuing the RESTORE DATABASE command using the WITH REPLACE clause, or restore the backup set to a different database name.

The error 3154 indicates that you tried to restore database over an existing database, but the existing database was created by a different CREATE DATABASE statement than the database in the backup set.

10. If you encountered error 3206 or error 3209 during backup operation, define the device using sp_addumpdevice, or refer to the physical device directly by specifying the TAPE = or DISK = syntax of the BACKUP statement.

These errors indicate that you have attempted to use a logical device that is not a backup device.

11. If you encountered Error 3249 during a restore operation, replace the current volume with a volume containing the start of the target backup set.

The error 3249 indicates that the media family spans multiple volumes, and the backup set to be processed by the restore operation starts on an earlier volume than the one inserted into the named device.

12. If you encountered Error 3256 during a restore operation, restore a different database backup and use log backups to roll forward, if you were restoring a database backup; otherwise, restore the next log backup if you were restoring a log backup.

This error indicates that the backup set is not complete because the backup operation that created the backup set did not finish successfully.

13. If you encountered Error 3257 during restoring a large database on Windows 98, try to create a database with the same size of the database from which the backup was created and then restore over the newly created database, or turn on trace flag 3104 and then restore the database.

This error occurs when the file size of the restored database is 2 GB or greater and the database is being restored over an existing database that is less than 2 GB. This error indicates that SQL Server cannot correctly check the free space. To bypass checking for free space, you can issue DBCC TRACEON (3104) on the same connection on which you are attempting the restore operation.

14. If you encountered Error 3267 or Error 3627 during a backup or restore operation, retry the operation after reducing the server load.

These errors indicate the server is too busy to perform the backup or restore operation.

15. Perform a full database backup before backing up the transaction log. Otherwise, you can encounter Error 4214 during the restore operation.

The error 4214 indicates that there is no current database backup. To restore the database after failure, you should have a full database backup or a complete set of file backups.

16. If you encountered Error 4305 during a restore operation, restore the transaction log backups in the order they were created.

The error 4305 indicates that the restore operation found a gap between the last restore and the transaction log that you attempted to apply. So, to resolve this error you should restore transaction logs in the same order in which they were backed up.

» See All Articles by Columnist Alexander Chigrik