Thursday 30 August 2012

T-SQL Code to Backup System Databases

Database Administrators can use the below T-SQL code to backup all of the system databases to SystemDatabaseBackups folder on the E drive. You will need to change this path for your systems.
This is a simple script that includes code for each database to be backed up:
USE master
GO
SELECT GETDATE() AS 'System Database Backup Start Time'
GO
/* Backup Distribution Database */ 
BACKUP DATABASE Distribution 
TO DISK = 'E:\SystemDatabaseBackups\Distribution.BAK' 
WITH INIT
GO
/* Backup ReportServer Database */ 
BACKUP DATABASE ReportServer 
TO DISK = 'E:\SystemDatabaseBackups\ReportServer.BAK' 
WITH INIT
GO
/* Backup ReportServerTempDB Database */ 
BACKUP DATABASE ReportServerTempDB 
TO DISK = 'E:\SystemDatabaseBackups\ReportServerTempDB.BAK' 
WITH INIT
GO
/* Backup Master Model */ 
BACKUP DATABASE Model 
TO DISK = 'E:\SystemDatabaseBackups\Model.BAK' 
WITH INIT
GO
/* Backup Master Database */ 
BACKUP DATABASE Master 
TO DISK = 'E:\SystemDatabaseBackups\Master.BAK' 
WITH INIT
GO
/* Backup Master MSDB */ 
BACKUP DATABASE MSDB 
TO DISK = 'E:\SystemDatabaseBackups\MSDB.BAK' 
WITH INIT
GO
/* Copy Resource Database Files Using XP_CMDSHELL */ 
EXEC xp_cmdshell 'COPY /Y "D:\Program Files\Microsoft SQL Server\MSSQL10.
SQL2008\MSSQL\Binn\mssqlsystemresource.mdf" "E:\SystemDatabaseBackups"' 
GO
EXEC xp_cmdshell 'COPY /Y "D:\Program Files\Microsoft SQL Server\MSSQL10.
SQL2008\MSSQL\Binn\mssqlsystemresource.ldf" "E:\SystemDatabaseBackups"'
GO
SELECT GETDATE() AS 'System Database Backup End Time'
GO

No comments:

Post a Comment