Guest User

T-SQL Backup Script

a guest
May 25th, 2013
3,993
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [master]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 5/25/2013 2:38:32 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9. -- =============================================
  10. -- Author: Microsoft
  11. -- Create date: 2010-02-06
  12. -- Description: Backup Databases for SQLExpress
  13. -- Parameter1: databaseName
  14. -- Parameter2: backupType F=full, D=differential, L=log
  15. -- Parameter3: backup file location
  16. -- =============================================
  17.  
  18. CREATE PROCEDURE [dbo].[sp_BackupDatabases]  
  19.             @databaseName sysname = null,
  20.             @backupType CHAR(1),
  21.             @backupLocation nvarchar(200)
  22. AS
  23.  
  24.        SET NOCOUNT ON;
  25.            
  26.             DECLARE @DBs TABLE
  27.             (
  28.                   ID int IDENTITY PRIMARY KEY,
  29.                   DBNAME nvarchar(500)
  30.             )
  31.            
  32.              -- Pick out only databases which are online in case ALL databases are chosen to be backed up
  33.              -- If specific database is chosen to be backed up only pick that out from @DBs
  34.             INSERT INTO @DBs (DBNAME)
  35.             SELECT Name FROM master.sys.databases
  36.             where state=0
  37.             and owner_sid != 1
  38.             AND name=@DatabaseName
  39.             OR @DatabaseName IS NULL
  40.             ORDER BY Name
  41.            
  42.             -- Filter out databases which do not need to backed up
  43.             IF @backupType='F'
  44.                   BEGIN
  45.                   DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
  46.                   END
  47.             ELSE IF @backupType='D'
  48.                   BEGIN
  49.                   DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
  50.                   END
  51.             ELSE IF @backupType='L'
  52.                   BEGIN
  53.                   DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
  54.                   END
  55.             ELSE
  56.                   BEGIN
  57.                   RETURN
  58.                   END
  59.            
  60.             -- Declare variables
  61.             DECLARE @BackupName varchar(100)
  62.             DECLARE @BackupFile varchar(100)
  63.             DECLARE @DBNAME varchar(300)
  64.             DECLARE @sqlCommand NVARCHAR(1000)
  65.         DECLARE @dateTime NVARCHAR(20)
  66.             DECLARE @Loop int                  
  67.                        
  68.             -- Loop through the databases one by one
  69.             SELECT @Loop = min(ID) FROM @DBs
  70.  
  71.       WHILE @Loop IS NOT NULL
  72.       BEGIN
  73.  
  74. -- Database Names have to be in [dbname] format since some have - or _ in their name
  75.       SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
  76.  
  77. -- Set the current date and time n yyyyhhmmss format
  78.       SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
  79.  
  80. -- Create backup filename in path\filename.extension format for full,diff and log backups
  81.       IF @backupType = 'F'
  82.             SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
  83.       ELSE IF @backupType = 'D'
  84.             SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
  85.       ELSE IF @backupType = 'L'
  86.             SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
  87.  
  88. -- Provide the backup a name for storing in the media
  89.       IF @backupType = 'F'
  90.             SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
  91.       IF @backupType = 'D'
  92.             SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
  93.       IF @backupType = 'L'
  94.             SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
  95.  
  96. -- Generate the dynamic SQL command to be executed
  97.  
  98.        IF @backupType = 'F'
  99.                   BEGIN
  100.                SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
  101.                   END
  102.        IF @backupType = 'D'
  103.                   BEGIN
  104.                SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
  105.                   END
  106.        IF @backupType = 'L'
  107.                   BEGIN
  108.                SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
  109.                   END
  110.  
  111. -- Execute the generated SQL command
  112.        EXEC(@sqlCommand)
  113.  
  114. -- Goto the next database
  115. SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
  116.  
  117. END
RAW Paste Data