Importance of SQL Server Backup History for a Database


Why SQL Server Backup History Important For A Database

What is the use with backup files? How will they make a way of help? Now, all the enterprise stores their information in the SQL Server database and it makes a way for backing up the data. Backup files are of great importance for any organization since when some problem strikes the backup files will help to restore the business information back. We should always keep track and see that the backup is properly taken, also should be aware whether latest backup files are there.

The SQL Server backup history will be seen in the msdb database.msdb contains SQL jobs, backup job, backup and restore history, maintain metadata, etc. What does the history table do? Let us look the contents of the history table.

History Table

The history table contents are listed below:

backupfile: Includes row which represents a data or log file that is backed up

backupfilegroup: Contains row that represents a filegroup in a backup set

Backupmediafamily: Each row in this represents a media family.

backupmediaset: Each row present represents a backup media set

backupset: The row contained represents a backup set

restorefile: Comprises one row for each restored file. It includes files restored indirectly from the filegroup name.

Restorefilegroup: Includes one row for each restored filegroup

restorehistory: Holds one row for each restore operation

The SQL Server backup history of all the backups can be of great help sometimes. It contains all the useful information that guides you with restoration. When you start restoring all the data in the table will be changed.

Systems associated with database backup are:

  • dbo.backupset: Provides information referring the details of the backup process
  • dbo.backupmediafamily: This system provides metadata for the physical backup files
  • dbo.backupfile: This provides the data content for the physical backup files

To get the history of any backup from the database, you can follow:

select * from msdb.backupset

Script ForFull/Differential/ Log Of Single Or All Database With Duration

SQL Server Backup History For A Database

We can replace TYPE=’D’ with TYPE=’I’ for differential backup and TYPE=’L’ for latest full backup.

By including AND DATABASE_NAME=’Your_Database_Name’ just before GROUP BY database_name we will get backup history from selected Database.

Users can access the backup history with the T-SQL statements as well. Let us see what are the statements used.

Backup History Access With T-SQL

The statements are:

  • RESTORE FILELISTONLY
  • RESTORE HEADERONLY
  • RESTORE LABELONLY
  • RESTORE VERIFYONLY

These statements give the information on the contents of the backup files.What do they do? These statements use the information from history table.

RESTORE FILELISTONLY: It uses information from backupfile table of history table for returning the result set, which comprises list of the database and the log files.

RESTORE HEADERONLY: This statements uses information from backupset table of backup history in-order to recover details of all backup header information.

Backup-Header Information

This helps you to check what the backups in the media are since it contains information on backup device used, time which the backup is done, etc.

Log Files For SQL Server Backup History

The transaction log files inside the backup will help you with the backup history since it contains the information as; logical name, type of file, filegroup membership, etc. In some situations, these will help you with restoration.

Situations

  • When you lost disk drive that had more errors in the files
    • You can list the files and determine which one is affected. Then, you can restore the files in another time, by the time restoring the all database.
  • It helps in case when you restore database from one server to other but,directory structure is not created on the server.

I have tried to bring an overall idea of SQL Server backup history for a database in this session. Backup files are the savior for an organization during the failure of their system since it contains all the copy of the information so far found.

Advertisements
This entry was posted in Technical descriptions and tagged . Bookmark the permalink.

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