Troubleshooting SQL Server Backup/Restore Problems


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

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles