SQL Server 2005 Express Backup Script

SQL 2005 (and Express) Backup Script

---

After the last few years of finding some good ideas and tweaking my script I wanted to let everyone use it. If you want.

Here are some directions on how to use it.

1) Create a text file and name it Backup_All_Databases.sql (or what ever you want).

2) Paste the below script in it:

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
SET @BackupDirectory = 'C:\Backuped_SQL_DB\'
--Add a list of all databases you don't want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

BEGIN
SET @Name = @DB + '( Daily BACKUP )'
SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'

IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END 
ELSE
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
BACKUP DATABASE @DB TO DISK = @BackupFile
WITH NAME = @Name, DESCRIPTION = @Description ,
MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
STATS = 10
FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

3) Open scheduler and create a new task that calls the below command line:

Code:

sqlcmd -S . -i "C:\Backups\BackupSettings\Backup_All_Databases.sql"

Note: I also made a batch file that had the same command line but added a pause so I can test it before the scheduler runs it.
EXAMPLE:

Code:

sqlcmd -S . -i "C:\Backups\BackupSettings\Backup_All_Databases.sql"
pause

4) Just run it every night in a scheduler and you are done.


Clean up Old Backup Files.

If you are running Windows Server 2003 you can also run a command utility to delete any files older then x number of days. This helps keep it cleaned up. Just paste this in a batch file and schedule the batch file.

Code:

echo on

rem First Delete old SQL Backup Files

FORFILES /p C:\Backuped_SQL_DB /s /m *.* /d -3 /c "CMD /C del /Q @FILE"

rem pause

This will also work on SQL Express