Recently I was asked to install SQL Server for a new project. I was not able to get a hold of the company key for SQL Server 2008 (don’t get me started on that), so I had to install the Express Edition. Unfortunately the SQL Server 2008 Express Edition does not provide a simple way to configure backups like I recall the regular version providing (note: I’m not a SQL Server DBA). As a result I had to write a script and create a scheduler job to perform the backup of my databases.
I found some scripts online, but none worked out of the box and most used a counter loop than when implemented resulted in multiple backups of a database that had no prior database in the ID order. This is the solution I arrived at. It has been working quite well and I was able to destroy one of my databases (intentionally) and recover from this backup.
Note: I didn’t bother to fix the one failure condition I recognized. If you drop the database with the highest ID while this script is running it’ll loop infinitely backing up the last one it reaches.
-- SQL Server backup routine to backup all databases -- Jed S. Walker, 2011/10/03 -- to automate via job scheduler -- sqlcmd -S theSQLserver\theWeather -i C:\SQLSERVERBACKUP\scripts\backup_db_all.sql -o C:\SQLSERVERBACKUP\backup_db_all.log PRINT '=>Setup base variables' DECLARE @today VARchar(20) DECLARE @dbname nvarchar(255) DECLARE @backupname nvarchar(255) DECLARE @backuploc nvarchar(255) SET @backuploc = 'C:\SQLSERVERBACKUP\' PRINT @backuploc --SET @today = CONVERT(CHAR(8), GETDATE(), 112) SET @today = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 126),'-',''),':',''),'.','') PRINT @today ---- backup all online databases -- first get all databases to be backed up DECLARE @dbList TABLE ( dbid INT, dbname NVARCHAR(256) ) INSERT INTO @dbList ( dbid, dbname ) SELECT dbid, name FROM master.dbo.sysdatabases WHERE ( name NOT IN ( 'tempdb' ) ) AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' ORDER BY dbid ------ Starting backup, one by one DECLARE @maxid int; DECLARE @curid int; SELECT @curid = MIN(dbid)-1, @maxid = MAX(dbid) from @dbList; PRINT '=> Perform backups' WHILE @curid != @maxid BEGIN SELECT TOP(1) @curid = dbid, @dbname = dbname FROM @dbList WHERE dbid > @curid; PRINT 'Debug: curid= ' + str(@curid) + ' maxid=' + str(@maxid) SET @backupname = @backuploc + @dbname + '_' + @today + '.bak' PRINT N'=> Backup: ' + @dbname + ' to ' + @backupname BACKUP DATABASE @dbname TO DISK = @backupname; END PRINT '' PRINT '=>Done with backups'
Then just use the Microsoft Windows Task Scheduler to schedule the job with a command like this:
sqlcmd -S theSQLserver\theWeather -i C:\SQLSERVERBACKUP\scripts\backup_db_all.sql -o C:\SQLSERVERBACKUP\backup_db_all.log