MSSQL Express Scheduled Backups

The Express edition of SQL server lacks automated scheduled backup functionality.  The following script will back up your databases.  It retains them for 7 days but can be easily edited:

Create a directory called c:\SQLBackup and go into it

Create a backup.sql file:

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘C:\SQLBackup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Then create a Backup.bat file with the following:
OSQL -Usa -PXXX -i”Backup.sql”
forfiles /p c:\SQLBackup /m *.bak /d -7 /c “cmd /c del @file”

Then schedule a nightly task to run SQLBackup.bat, ensuring it starts in c:\SQLBackup

Has been tested for 2003/2005 but not 2000

If you just wanted to backup single databases you can use:

BACKUP DATABASE [dbname1] TO  DISK = N’C:\SQLBackup\dbname1.bak’ WITH NOFORMAT, INIT,  NAME = N’dbname1-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

or

OSQL -Usa -Ppass -n -Q “BACKUP DATABASE [dbname] TO DISK = N’c:\SQLBackups\dbname.bak’ WITH RETAINDAYS = 8, NOINIT, NAME = N’dbname-Full’, SKIP, NOREWIND, NOUNLOAD, STATS = 10″

You can leave a response, or trackback from your own site.

Leave a Reply