Microsoft SQL Server 2005 Express – Backup system databases

-- Declare Session Variables
DECLARE @Now datetime
DECLARE @TodayStr varchar(20)
DECLARE @MasterBackupName varchar(100)
DECLARE @ModelBackupName varchar(100)
DECLARE @MSDBBackupName varchar(100)
DECLARE @MasterBackupFile varchar(100)
DECLARE @ModelBackupFile varchar(100)
DECLARE @MSDBBackupFile varchar(100)
DECLARE @SQL varchar(2000)
DECLARE @SQL2 varchar(2000)
DECLARE @SQL3 varchar(2000)
 
--  Set the current date and time
SET @Now = getdate()
 
-- Create backup file date and time in DOS format yyyy_hhmmss
Set @TodayStr = convert(varchar, @Now, 112)+ '_'+replace(convert(varchar, @Now, 108), ':', '')
 
-- Create a variable holding the total path\filename.ext for the backup
Set @MasterBackupFile = 'D:\Backups\system\Master_'+ @TodayStr + '_FULL.BAK'
Set @ModelBackupFile = 'D:\Backups\system\Model_'+ @TodayStr + '_FULL.BAK'
Set @MSDBBackupFile = 'D:\Backups\system\MSDB_'+ @TodayStr + '_FULL.BAK'
 
-- Provide the backup a SQL name and name in media
Set @MasterBackupName = 'Master full backup for ' + @TodayStr
Set @ModelBackupName = 'Model full backup for ' + @TodayStr
Set @MSDBBackupName = 'MSDB full backup for ' + @TodayStr
 
-- Generate the Dynamic SQL script variable to be executed
SET @SQL = 'BACKUP DATABASE [master] TO DISK = ''' + @MasterBackupFile + ''' WITH NOFORMAT, NOINIT, NAME = ''' +@MasterBackupName+''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
Print @SQL
SET @SQL2 = 'BACKUP DATABASE [model] TO DISK = ''' + @ModelBackupFile + ''' WITH NOFORMAT, NOINIT, NAME = ''' +@ModelBackupName+''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
Print @SQL2
SET @SQL3 = 'BACKUP DATABASE [msdb] TO DISK = ''' + @MSDBBackupFile + ''' WITH NOFORMAT, NOINIT, NAME = ''' +@MSDBBackupName+''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
Print @SQL3
-- Execute the SQL Command
EXEC(@SQL)
EXEC(@SQL2)
EXEC(@SQL3)

SQL Express Scripts

Run these SQL commands from a CMD file to automate backups

Full DB Backup

-- Declare Session Variables
DECLARE @Now datetime
DECLARE @TodayStr varchar(20)
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @SQL varchar(2000)
 
--  Set the current date and time
SET @Now = getdate()
 
-- Create backup file date and time in DOS format yyyy_hhmmss
Set @TodayStr = convert(varchar, @Now, 112)+ '_'+replace(convert(varchar, @Now, 108), ':', '')
 
-- Create a variable holding the total path\filename.ext for the backup
Set @BackupFile = 'D:\Backups\prod\CasesDB_'+ @TodayStr + '_FULL.BAK'
 
-- Provide the backup a SQL name and name in media
Set @BackupName = 'Cases full backup for ' + @TodayStr
 
-- Generate the Dynamic SQL script variable to be executed
SET @SQL = 'BACKUP DATABASE [Cases] TO DISK = ''' + @BackupFile + ''' WITH NOFORMAT, NOINIT, NAME = ''' +@BackupName+''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
Print @SQL

-- Execute the SQL Command
EXEC(@SQL)

Trans Log Backup

-- Declare Session Variables
DECLARE @Now datetime
DECLARE @TodayStr varchar(20)
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @SQL varchar(2000)
 
--  Set the current date and time
SET @Now = getdate()
 
-- Create backup file date and time in DOS format yyyy_hhmmss
Set @TodayStr = convert(varchar, @Now, 112)+ '_'+replace(convert(varchar, @Now, 108), ':', '')
 
-- Create a variable holding the total path\filename.ext for the log backup
Set @BackupFile = 'D:\Backups\prod\CasesTransLog_'+ @TodayStr + '_.BAK'
 
-- Provide the backup a SQL name and name in media
Set @BackupName = 'Cases trans log backup for ' + @TodayStr
 
-- Generate the Dynamic SQL script variable to be executed
SET @SQL = 'BACKUP LOG [Cases] TO DISK = ''' + @BackupFile + ''' WITH NOFORMAT, NOINIT, NAME = ''' +@BackupName+''', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
Print @SQL

-- Execute the SQL Command
EXEC(@SQL)