|
 |
About Us | |
|
SSWUG Articles | |
 |
Articles |
 |
Administering | |
|
Comparison | |
|
General | |
|
Know How | |
|
Replication | |
|
Tuning | |
|
Undocumented | |
|
UDF | |
|
SQL 6.5 |
|
FAQ | |
|
Scripts | |
|
Tips | |
|
Test Exams | |
|
Advertise | |
|
Download | |
|
History | |
|
Search | |
|
Traffic | |
|
Related Links | |
|
||
|
|
||
How to troubleshoot backup/restore problemsAlexander Chigrik Should you have problems with backup/restore, review this troubleshooting checklist to find potential solutions. 1. Check that you use the latest SQL Server service pack. To check what SQL service pack are you running, see this link: How can I check what SQL service pack am I running? 2. Check that you have appropriate permissions to make backup/restore. To backup 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 db_owner, or db_backupoperator fixed database roles. To restore database, you should have CREATE DATABASE permissions, if the database being restored does not exist. If the database being restored exists, you should have RESTORE permissions granted to you, or you should be a member of the sysadmin or dbcreator fixed server roles, or the owner (dbo) of the database. 3. Check that 'trunc. log on chkpt' option is turned off, if you want to make a backup of the transaction log. 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 it will be empty. 4. If you encountered error 156 during backup or restore operations, 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 before using BACKUP or RESTORE statements. The error 156 indicates that the syntax of a Transact-SQL statement is incorrect. One of the causes for this error may be 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 restore 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 SQL Server 7.0 bug; SQL Server 2000 does not contain such problems. 6. If you encountered error 3023 during backup operation, reissue the backup operation after the conflicting operation has finished. 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 backup operation, use backups from your primary server until operations have switched to the standby. The error 3036 indicates that the database is in 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 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 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 existing database, but the existing database were 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 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 restore operation, restore a different database backup, and use log backups to roll forward, if you were restoring a database backup; 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 restore 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 the 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 attempt the restore operation. 14. If you encountered error 3267 or error 3627 during 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 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 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. |