Logo
programming4us
programming4us
programming4us
programming4us
Windows XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
 
Windows Server

SQL Server 2008 : Backing Up the System Databases & Backup History

7/13/2011 9:15:54 AM

1. Backing Up the System Databases

You need to make sure you include the system databases as a part of your backup strategy. If you have to rebuild a server, you will be able to use the system database backups to restore any custom configurations, including jobs, logins, and so on. We generally include the system databases in a full daily backup routine and take special one-off backups after certain information in the database changes.

Following are the system databases and some things that you need to know about backing them up:

  • master: The master database uses the simple recovery model, and you can only perform full database backups of the master database. You should back up this database whenever certain operations cause it to be updated, including the following:

    • Adding and removing user databases

    • Adding and removing logins

    • Adding and removing a backup device

    • Adding and removing linked servers and remote logins

    • Modifying metadata for user databases, such as file locations or adding and removing filegroups

  • msdb: The msdb database uses the simple recovery model by default, but it can be configured to use any recovery model. For example, you can change the recovery model of the msdb to full and take advantage of transaction log backups if you are highly dependent on information that is stored in this database. The information in the msdb database changes often because it logs historical information for items like backups, SQL Agent jobs, and database mail. At minimum, you should take a full backup of the msdb after making configuration changes, including the following:

    • Adding and removing SQL Agent jobs

    • Adding and removing maintenance plans

    • Importing SSIS packages

    • Adding and removing Policies and central management servers

    • Configuring log shipping

    • Configuring database mail

    • Configuring operators and alerts

  • model: This database uses the full recovery model by default, but it can be configured to use any recovery model. Remember, since the model database serves as a template for all new user databases, if you change the recovery model in the model database, any newly created databases will be created with this recovery model as well. You only need to perform full database backups of the model database whenever you make custom configuration changes. Even though the recovery model is set to full, there is no activity in the model database to warrant transaction log backups.

  • tempdb: The recovery model for the tempdb is simple and cannot be changed. Every time you restart SQL Server, the tempdb is recreated, and any data stored in it is lost. Since the tempdb is recreated every time SQL Server is restarted, you are not allowed to perform any type of backup operations on it.

  • Resource: This is a read-only database and is not visible in SQL Server Management Studio. The Resource database is only updated whenever service packs are applied to SQL Server. You cannot back up the Resource database using a SQL Server backup operation. To backup this database, you must perform a file-based backup operation by copying the mssqlsystemresource.mdf file to your backup location. Unlike other data files, you do not have to stop the SQL Server service in order to copy the Resource data files.


2. Backup History

SQL Server maintains a set of tables that hold the backup history that is performed on each server instance in the msdb database. You can use these tables to gather useful information, such as the last time a backup was performed on each database. The backup history tables include the following:

  • backupfile: Contains information about the data and log files for a database backup.

  • backupfilegroup: Contains information about every filegroup in a database when the backup was performed.

  • backupmediafamily: Holds one row for each media family, including the logical device name, physical device name, and device type.

  • backupmediaset: Holds one row for each backup media set. Each media set can have multiple media families.

  • backupset: Holds a single row for each backup set created by a successful backup operation. The backupset table holds information like the database name, the backup start and end times, and log sequence numbers at the time of backup.

After a while of capturing backup information, the backup history tables can become rather large depending on the number and frequency of backups performed. We have seen the msdb grow to a fairly substantial size because of the amount of data stored in the backup history tables. Microsoft provides the sp_delete_backuphistory system stored procedure that you can use to limit the amount of history stored in these tables. The sp_delete_backuphistory procedure enables you to enter a date/time parameter that will remove all data created before a certain date or time. For example, the statement Exec msdb.dbo.sp_delete_backuphistory '1/1/2009' will remove all the records from the backup history tables in the mdsb that were created before January 1, 2009.

If your msdb database already contains a lot of data in the history tables, it is best to delete the data for smaller date ranges and test the impact on the server instead of trying to remove several months or years all at once.


Listing 1 shows a script you can use to keep a specified amount of history in the database without deleting all of the history at once. If you have a lot of history in your backup tables, you should start deleting the data in smaller chunks until you see the impact that removing the data has on your server. We maintain six months of history and never delete more than three months of history each time the script is executed. Let's say you schedule this script to run once a week. Every week you will delete three months of history until you eventually delete all the backup data you have built up prior to implementing this script and only have to delete a single week of backup history.

Example 1. Script to Remove Backup History in the msdb in Three-Month Intervals While Maintaining Six Months of History
USE msdb
GO

--Keep 6 months of history, but never delete more than 3 months at a time.
Declare @dte Datetime
SET @dte = (SELECT MIN(backup_start_date) FROM backupset WITH (nolock))
SET @dte = (SELECT dateadd(mm,3,@dte))

IF (dateadd(mm,-6,getdate()) < @dte )
SET @dte = dateadd(mm,-6,getdate())

PRINT @dte

EXEC sp_delete_backuphistory @dte


If you want to remove all the backup history for a specific database, you can execute the sp_delete_database_backuphistory system stored procedure followed by the database name. For example, the statement Exec msdb.dbo.sp_delete_database_backuphistory 'AdventureWorks2008' will remove all the records from the backup history tables in the msdb for the AdventureWorks2008 database.
Other -----------------
- SQL Server 2008 : Managing Backups - Using Encryption
- SQL Server 2008 : Managing Backups - Backups from T-SQL
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 4)
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 3) - What to Keep in Mind When Tuning
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 2) - Create, Execute, and Analyze
- BizTalk 2009 : Ready-to-Bake BizTalk Performance Plan (part 1) - Plan Your Tests
- Windows Small Business Server 2011 : Planning the Network Infrastructure (part 2)
- Windows Small Business Server 2011 : Planning the Network Infrastructure (part 1)
- Introducing Windows Small Business Server 2011
- Microsoft Dynamics GP 2010 : Streamlining payables processing by prioritizing vendors
 
 
Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
 
programming4us
Women
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone