Troubleshooting Backing Up and Restoring
Here are some problems you may encounter when backing up and restoring databases and transaction logs:
- A syntax error occurred when using the BACKUP or RESTORE statements, which indicates that the database is in Microsoft® SQL Server™ version 6.5 compatibility mode. The BACKUP and RESTORE keywords are valid only with SQL Server 7.0 or SQL Server 2000 databases.
Set the SQL Server compatibility level to 80 before using BACKUP or RESTORE statements. For more information, see Error 156.
- The BACKUP statement cannot be performed at the same time as creating or deleting database files.
Reissue the backup operation after the conflicting operation has finished. For more information, see Error 3023.
- A standby database cannot be backed up if it has not yet been recovered.
Use backups from your primary server until operations have switched to the standby. For more information, see Error 3036.
- The backup being restored is a valid Microsoft Tape Format, but is not a SQL Server backup.
To determine the backup contents, use RESTORE HEADERONLY. For more information, see Error 3143.
- The backup set is a backup of a database with the same name as the database to which you are restoring. However, the database being restored to was created by a different CREATE DATABASE statement than the database in the backup set.
Either overwrite the existing database or restore the backup set to a different database name. For more information, see Error 3154.
- An attempt was made to use a logical device that is not a defined backup device.
Either create the device or use the TAPE = or DISK = syntax of the BACKUP statement. For more information, see Error 3206 or Error 3209.
- The media family spans multiple volumes. The restore operation has already processed the data on the specified volume.
Replace the current volume with a volume not yet processed. For more information, see Error 3227.
- The backup device does not contain data in Microsoft Tape Format. For more information, see Error 3242.
- The media family spans multiple volumes. The restore operation expected to process the volume number specified in the error message, but found a different volume number instead.
To continue the restore operation, replace the current volume with the volume number specified in the error message. For more information, see Error 3247.
- The media family spans multiple volumes. The backup set to be processed by the restore operation starts on an earlier volume than the one inserted into the named device.
Replace the current volume with a volume containing the start of the target backup set. For more information, see Error 3249.
- The restore operation has completely processed the media family on the named device, and is now ready to reuse the device to restore one of the remaining media families.
Replace the current volume with the first volume of a media family that has not yet been processed. For more information, see Error 3251.
- The backup operation that created the backup set did not finish successfully.
Either restore a different database backup, if restoring a database backup, and apply transaction logs; or apply the next transaction log backup, if restoring a transaction log backup. For more information, see Error 3256.
- The volume on the named device does not belong to the same media set as the other volumes being processed.
Either remove the offending volume and insert the next volume of the media family, for tape media sets; or, for disks, reissue the command, naming only those backup devices part of the same media set. For more information, see Error 3258.
- The tape inserted into the named device is part of the current media set and may not be used as continuation media.
Replace the current volume with a fresh tape that can be overwritten. For more information, see Error 3263.
- The server is too busy to perform the backup or restore operation.
Retry the operation after reducing the server load. For more information, see Error 3267 or Error 3627.
- Some statements are not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.For more information, see Error 4208.
- To restore the database after failure, you must begin either with a full database backup or with a complete set of file backups. A log backup was created before the first database or file backup.
Perform a full database backup before backing up the transaction log. For more information, see Error 4214.
- The restore operation found a gap between the last restore and the transaction log that you attempted to apply.
Apply the transaction log backups in the order they were created originally. For more information, see Error 4305.
- No further restore operations may be performed after a database has been recovered.
Restart the restore sequence and use the NORECOVERY option on all but the final RESTORE statement. For more information, see Error 4306.
- Could not recover the database to the state that it was in at the time the current log backup was made. At least one file has been modified since this time. Therefore, recovery is not possible because the database would be left in an inconsistent state.
Recover the database either to its most recent state or to a specific point in time. For more information, see Error 4318.
- A backup file could not be used because it was originally formatted with one sector size and is now on a device with a different sector size.
SQL Server uses nonbuffered I/O, which requires sectors to be aligned. You must restore the backup set from a disk with the same sector size, or over a network, which uses buffered I/O. Alternatively, you can specify a WITH BLOCKSIZE clause when you back up the database.
See Also
Backing Up and Restoring Databases