Created
April 9, 2014 06:00
-
-
Save iamrommel/10230188 to your computer and use it in GitHub Desktop.
Create a stored procedure that will backup all selected database in sql server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [master] | |
GO | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
-- ============================================= | |
-- Parameter1: databaseName, setting null backup all databases | |
-- 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 @newBackupLocation nvarchar(200); | |
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 | |
DELETE @DBs where DBNAME NOT LIKE 'TMM%' | |
-- 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 | |
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') | |
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 @newBackupLocation = @backupLocation + @dateTime + N'\' | |
EXEC master.dbo.xp_create_subdir @newBackupLocation | |
-- Create backup filename in path\filename.extension format for full,diff and log backups | |
IF @backupType = 'F' | |
SET @BackupFile = @newBackupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK' | |
ELSE IF @backupType = 'D' | |
SET @BackupFile = @newBackupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK' | |
ELSE IF @backupType = 'L' | |
SET @BackupFile = @newBackupLocation+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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment