Note: This applies to Microsoft SQL Server 2005/2008...
Ever get sick of having a different SQL Job for each database backup? I created this method for backing up all databases (filtered) in a cursor. Just create a job with this as the Step and give the Job a schedule...
01: DECLARE @dbName varchar(50)
02: DECLARE @BKPDate varchar(10)
03: DECLARE @sql nvarchar(1000)
04: SET @BKPDate = replace(convert(varchar(10), getdate(), 102), '.', '')
05: DECLARE bkpCursor CURSOR FOR
06: 07: /* only backup non-system databases */
08: select [Name] from sys.databases where [owner_sid] <> 0x01
09: 10: OPEN bkpCursor
11: FETCH NEXT FROM bkpCursor INTO @dbName
12: IF @@FETCH_STATUS = 0
13: BEGIN
14: WHILE (@@FETCH_STATUS = 0)
15: BEGIN
16: set @sql =
17: ' BACKUP DATABASE '+ @dbName +
18: ' TO DISK = N''C:\SQLBackup\'+ @dbName +'_'+ @BKPDate +'.bak''' +
19: ' WITH NOFORMAT, NOINIT,'+
20: ' NAME = N'''+ @dbName +'-Full Database Backup'',' +
21: ' SKIP, STATS = 10;'
22: exec sp_executesql @statement = @sql
23: FETCH NEXT FROM bkpCursor INTO @dbName
24: END
25: END
26: ELSE
27: PRINT 'No Databases to Backup.'
28: CLOSE bkpCursor
29: DEALLOCATE bkpCursor
MS SQL Server Express Edition
But what if you're running MS SQL Server Express? That doesn't come with SQL Server Agent Service, which is required for creating Jobs. Use the osql command-line utility.
Here is a batch file I wrote which uses osql to call a sql script file (after saving the above syntax into a sql file called "backup_Databases.sql"). Then I zip up the backup files and delete them.
01: @ECHO OFF
02: osql -E -i "C:\backup\scripts\backup_Databases.sql" > NUL
03: 04: C:\util\zip -9 -j -q C:\backup\archive_db_%1.zip C:\SQLBackup\*%1.bak05: IF EXIST C:\backup\archive_db_%1.zip (
06: del C:\SQLBackup\*%1.bak
07: )So to call it I would type:
[ Note: I'm using Info-Zip to compress the backup files. ]
Stand-alone Batch File
I know what you're thinking... Why not make the batch file smart enough to get the date instead of having to pass it in?
Here is a revised batch file which does just that:
01: @ECHO OFF
02: osql -E -i "C:\backup\scripts\backup_Databases.sql" > NUL
03: 04: for /f "tokens=2-4 delims=/ " %%a in ('date/t') do (set datevar=%%c%%a%%b)
05: 06: C:\util\zip -9 -j -q C:\backup\archive_db_%datevar%.zip C:\SQLBackup\*%datevar%.bak07: IF EXIST C:\backup\archive_db_%datevar%.zip (
08: del C:\SQLBackup\*%datevar%.bak
09: )What's left?
All that's left is to create a scheduled task using Windows Task Scheduler, which will call the batch file (daily, weekly, etc.).
Comments