How to automate backups of SQL Server databases

Fixed version (minor):

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— =============================================
— Author: Microsoft
— Create date: 2010-02-06
— Update date: 2015-11-18 by Protiguous
— Description: Backup Databases
— Parameter1: databaseName
— Parameter2: backupType F=full, D=differential, L=log
— Parameter3: backup file location
— =============================================

CREATE 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] format 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 path\filename.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
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
EXEC(@sqlCommand)
— Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END

Advertisements

Author: Protiguous

C# Software Developer, Father, and seeker of Truth.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s