In this post I’m going to show what the WITH FILE = X option does when backing-up or restoring a SQL Server database.
The first thing we need to know is that a backup file can contain multiple databases, and even multiples of the same database. If you run a quick backup command on a small database, then hit F5 again without amending the file path, you’ll end up with 2 database backups within the same media.
When restoring from a backup we can specify which version of the database we want by using the WITH FILE option, with the help of the RESTORE HEADERONLY command.
This post is a run-through of how the above works!
Backup & Restore With File
1. To get started, create a database & insert some test data.
2. Backup the database.
The backup file;
3. Insert some more data into the test table and then backup the file to the very same location.
The backup file;
4. Drop the database, readying for a restore.
5. Using RESTORE HEADERONLY, we can have a look into the backup media to find out more information. The Position column is what we need from this.
6. Restore the first backup performed above WITH FILE = 1.
7. Check the data.
8. Restore the database in position number 2, with REPLACE to avoid the need of a drop.
9. Verify again which backup it is.
10. Finally, restore again without WITH FILE.
We can see that the first backup taken has been restored as default.
If you are backing up multiple different databases into the same media, the above won’t work. You’ll have to specify the position. You’d also probably want to give it a backup name using WITH NAME = ‘backupName’.
Hope this is useful stuff!
Leave a Reply