Good day reader. This post is coming to you from a busy week in Texas, where the heat is starting to subside.
The week’s topic will cover two options for restoring a SQL database.
Have you ever come across a BAK file or LOG file where you were unsure what data was contained in the file or the date of the data? Restore options allow you to investigate the backup file without trying to restore the backup.
HEADERONLY
RESTORE HEADERONLY FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLBackupWhatInTheWorldIsInThisFile.bak'
As you can see, we can get a log of information from the
HEADERONLY restore option. See below for a couple of important values in the output.
- BackupName = This value shows you the name given to the backup
- BackupType = This value is important because it will tell you the type of backup taken
- Compressed = Lets you know if backup compression was used
- UserName = This shows you the account used to take the backup
- ServerName = This is an important value as it shows the source of the backup
- DatabaseName = This is the name of the database the backup was taken
- BackupSize = This value gives you the original size of the backup before any compression is applied
- Multiple LSN = Several LSN columns let you know the LSN chain for the backup file
- Backup Date = The Start and Finish date and time of the backup
- Software Version = These fields are important because it tells what SQL version the backup is from
- IsCopyOnly = This lets you know if the backup is COPY_ONLY
- CompressedBackupSize = This value shows how large the backup is after being compressed if enabled
You can see how much information you can gather from running this restore command.
FILELISTONLY
RESTORE FILELISTONLY FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLBackupWhatInTheWorldIsInThisFile.bak'
Now we can review the output for the
FILELISTONLY command.
- LogicalName = This is the name for each file in the backup
- PhysicalName = This column is the filename and location of the actual data and log file
- Type = This is the file type, whether it is data, log or file stream
- Size = This is important to know so you can make sure the location you want to restore the file to has enough space
- TDEThumbprint = This information can be important if TDE protects the database
Conclusion
These two restore commands can be very powerful in getting metadata from backup files. This can be to help identify what is in the backup or to help you get a backup file restored to a SQL Server.
Resources
The post Restore HEADERONLY and FILELISTONLY appeared first on GarryBargsley.com.