KB: Backsqlserver 2005 expressdatabase

More...

This site is hosted and sponsored by hyve.com specialists in Cloud Hosting UK and VMware Hosting. If you are interested in our services please call us for chat on 0800 612 2524

Back up SQL Server MSDE and SQL Server Express 2005 Database#

This script backs up all database on the server.#

Add the following sql script into a file called D:\Scheduled Tasks\Backup_All_Databases2005.sql. Change the variable called @BackupDirectory to point to the folder you wish to copy the files into.

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200) 

--Change this variable
SET @BackupDirectory = 'D:\MSSQL data\MSSQL.1\MSSQL\Backup\'

--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

Create a batch file by copying the script below into a file called D:\Scheduled Tasks\BACKUP-SQL2005.bat which opens SQLCMD and executes the script above. Remember to change the path to point to the script above.

sqlcmd -S localhost\SQLEXPRESS -i "D:\Scheduled Tasks\Backup_All_Databases2005.sql"

Call the above script from windows task scheduler. Goto control panel, Scheduled Tasks, Add scheduled tasks. Set the task to be run at midnight daily. Remember to change the path to point to the batch file created above.