Western Mass Hosting's Source Code Articles

Backup Procedure and Script for SQL Server

This procedure and script are meant for use in situations where your SQL server may not have SQL Agent installed.

Whether it is because it’s SQL Express being use, or because it simply was not an option at the time of install.

First thing you will need to do is login to the SQL server using SQL Management Studio.  Make sure your login is able to create stored procedures at the system level, and add the following to the ‘master’ database

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] Script Date: 08/17/2011 10:55:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
 
ALTER PROCEDURE [dbo].[sp_BackupDatabases] 
 @databaseName sysname = null,
 @backupType CHAR(1),
 @backupLocation nvarchar(200) 
AS 
 
 SET NOCOUNT ON; 
 
 DECLARE @DBs TABLE
 (
 ID int IDENTITY PRIMARY KEY,
 DBNAME nvarchar(500)
 )
 
 -- Pick out only databases which are online in case ALL databases are chosen to be backed up
 -- If specific database is chosen to be backed up only pick that out from @DBs
 INSERT INTO @DBs (DBNAME)
 SELECT Name FROM master.sys.databases
 where state=0
 -- AND name=@DatabaseName
 -- OR @DatabaseName IS NULL
 ORDER BY Name
 
 -- Filter out databases which do not need to backed up
 IF @backupType='F'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
 END
 ELSE IF @backupType='D'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE IF @backupType='L'
 BEGIN
 DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
 END
 ELSE
 BEGIN
 RETURN
 END
 
 -- Declare variables
 DECLARE @BackupName varchar(100)
 DECLARE @BackupFile varchar(100)
 DECLARE @DBNAME varchar(300)
 DECLARE @sqlCommand NVARCHAR(1000) 
 DECLARE @dateTime NVARCHAR(20)
 DECLARE @Loop int 
 
 -- Loop through the databases one by one
 SELECT @Loop = min(ID) FROM @DBs
 
 WHILE @Loop IS NOT NULL
 BEGIN
 
-- Database Names have to be in [dbname] formate since some have - or _ in their name
 SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 
-- Set the current date and time n yyyyhhmmss format
 SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 
 
-- Create backup filename in pathfilename.extension format for full,diff and log backups
 IF @backupType = 'F'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_' + @dateTime + '.BAK'
 ELSE IF @backupType = 'D'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_' + @dateTime + '.BAK'
 ELSE IF @backupType = 'L'
 SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_' + @dateTime + '.TRN'
 
-- Provide the backup a name for storing in the media
 IF @backupType = 'F'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
 IF @backupType = 'D'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
 IF @backupType = 'L'
 SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
 
-- Generate the dynamic SQL command to be executed
 
 IF @backupType = 'F' 
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
 END
 IF @backupType = 'D'
 BEGIN
 SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END
 IF @backupType = 'L' 
 BEGIN
 SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT' 
 END
 
-- Execute the generated SQL command
 EXEC(@sqlCommand)
 
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 
END

Once created, create a *.bat file on the same server, and add the following code to it.  Make sure to replace YOUR PATHS with the actual paths on your server

rmdir /s /q "C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupbackup-bak"
move "C:Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\backup" "C:Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\backup-bak"
mkdir "C:Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\backup" 

sqlcmd -S localhost -E -Q "EXEC sp_BackupDatabases @backupLocation='C:Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\backup\', @backupType='F'"

Now, setup a Scheduled Task to run at your desired intervals.  Make sure it runs as administrator

That’s all for today folks!

~ Happy Coding