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