This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration. RESTORE DATABASE is terminating abnormally. If I were to attempt to restore my bad database backup, I would have seen the following error: The file ID 1 on device ‘C:\BACKUPS\MYDB.BAK’ is incorrectly formed and can not be read. Even if RESTORE VERIFYONLY says your backup is good, in order to know if your backup (and database) is truly valid you need to restore it. In my particular case the fix was easy, I just downloaded the backup again (and ensured my laptop stayed on this time!)Ī positive result from RESTORE VERIFYONLY does not mean check your database for corruption, and it does not attempt to verify the structure of your data. Running RESTORE VERIFYONLY on the original backup on the server showed that it was fine, so my laptop must have shut down before my backup download completed, corrupting the file. Running RESTORE VERIFYONLY produced the following output, which confirmed what I suspected: The file on device ‘C:\backups\MyDB.bak’ is not a valid Microsoft Tape Format backup set. Syntax BACKUP DATABASE databasename TO DISK filepath The SQL BACKUP WITH DIFFERENTIAL Statement A differential back up only backs up the parts of the database that have changed since the last full database backup. I run this command as part of my regular backup routines (it’s built in to the maintenance solution that I use). The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database. RESTORE VERIFYONLY is very useful, and very important to run. This command does not restore the backup, it just checks to see if the backup is complete and that the entire backup is readable.
0 kommentar(er)
