dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted August 5, 2019

What is in My Database Backup File

By Gregory Larsen

Suppose you are a new DBA in an environment. You are tasked with restoring a database, but you don’t have a single clue as to what is in any of the database backup files. Maybe the backup file names might give you a clue, and then again maybe not. Possibly the names are so cryptic that they are meaningless as to the content of the backup.  

The Microsoft SQL Server team has made it easy for you to retrieve the information in a backup file by using one of the following two commands:

  • RESTORE HEADERONLY
  • RESTORE FILELISTONLY

The RESTORE HEADERONLY command displays information about all the backup sets stored in a backup device. Consider a situation where I have a backup named “C:\temp\MyBackup.bak”. This backup name is meaningless and provides no clue as to what is contained in the backup. In order to determine what is in this backup file, I can run the following command:

RESTORE HEADERONLY FROM DISK = N'C:\temp\MyBackup.bak'

When I run this command, I will get a row returned for every backup file that is contained in this operating system file. Remember SQL Server allows you to have multiple database backups that are stored within a single operating system file. 

For each row returned from the RESTORE HEADERONLY command, I can find out things like:

  • The database that was backed up
  • The time the backup was taken
  • The type of backup (full, differential, or transaction log)
  • The version of the backup
  • The size of the backup

The RESTORE HEADERONLY command only provides information about the different backups contained in a single operating system backup file. If you wanted to find out information about a particular file you can use the RESTORE FILELISTONLY command. Here is an example where I reviewed the third file in the MyBackup.bak file looking for more information.

RESTORE FILELISTONLY FROM DISK = N'C:\temp\MyBackup.bak' WITH FILE = 3;

Following is some of the information you can find out about a single backup file using the RESTORE FILELISTONLY command:

  • The logical and physical names of the database
  • Type of file (data, log, full text, or filestream)
  • The file group name where the database lives
  • The max size of the database

Next time you want to find out information about what is contained in a backup file consider using one of these two different RESTORE commands.

# # #

See All Articles by Columnist Gregory A. Larsen



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM