SQL server, SQLServerPedia Syndication

Backup file information.

There are times when I see a large backup file with a very ambiguous name like ‘20100102_backup_000001.bak’. These may be the names created by the SQL backup jobs. When free space has to be created, deleting some of these huge backups will help. But which of these files can I delete? A simple query for this is:

RESTORE HEADERONLY
FROM DISK = N’e:\stemp\DB_backup_201007270001.bak’
WITH NOUNLOAD;

This query will show only the header information of the SQL backup file.  Some of the useful columns for this post are: ServerName, DatabaseName, MachineName, and BackupFinishDate. With this data, I get some information as to when the backup was taken and from where. Then I can decide on weather to keep or turf the backup.

There was a situation in which I could not restore a DB via SSMS. I was creating my TSQL to do the restore when I realized: How do I know what are the names of the different logical files of the DB. Luckily there is another backup command that can be used to see a list of the logical files of the database:

RESTORE FILELISTONLY
FROM DISK = N’e:\stemp\DB_backup_201108020001.bak’

Now that I know the logical names, I can use them in my restore script and place the files at the proper file system location.

If you come across a backup file that you cannot restore, you can verify the integrity of the backup. The command I use is: restore verifyonly from disk=N’Z:\backup\DB_backup_201108020001.bak’. This will return an error if the backup file is corrupt.

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s