Advertisement
andrzejiwaniuk

tworzenie kopii zapasowane SQL Express 2014

Oct 2nd, 2014
1,550
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 4.68 KB | None | 0 0
  1. USE [master]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 02/10/2014 17:06:51 ******/
  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. ALTER 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 name=@DatabaseName
  38.             OR @DatabaseName IS NULL
  39.             ORDER BY Name
  40.            
  41.             -- Filter out databases which do not need to backed up
  42.             IF @backupType='F'
  43.                   BEGIN
  44.                   DELETE @DBs WHERE DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
  45.                   END
  46.             ELSE IF @backupType='D'
  47.                   BEGIN
  48.                   DELETE @DBs WHERE DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
  49.                   END
  50.             ELSE IF @backupType='L'
  51.                   BEGIN
  52.                   DELETE @DBs WHERE DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
  53.                   END
  54.             ELSE
  55.                   BEGIN
  56.                   RETURN
  57.                   END
  58.            
  59.             -- Declare variables
  60.             DECLARE @BackupName VARCHAR(100)
  61.             DECLARE @BackupFile VARCHAR(100)
  62.             DECLARE @DBNAME VARCHAR(300)
  63.             DECLARE @sqlCommand NVARCHAR(1000)
  64.         DECLARE @dateTime NVARCHAR(20)
  65.             DECLARE @LOOP int                  
  66.                        
  67.             -- Loop through the databases one by one
  68.             SELECT @LOOP = MIN(ID) FROM @DBs
  69.  
  70.       WHILE @LOOP IS NOT NULL
  71.       BEGIN
  72.  
  73. -- Database Names have to be in [dbname] format since some have - or _ in their name
  74.       SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @LOOP)+']'
  75.  
  76. -- Set the current date and time n yyyyhhmmss format
  77.       SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
  78.  
  79. -- Create backup filename in path\filename.extension format for full,diff and log backups
  80.       IF @backupType = 'F'
  81.             SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
  82.       ELSE IF @backupType = 'D'
  83.             SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
  84.       ELSE IF @backupType = 'L'
  85.             SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
  86.  
  87. -- Provide the backup a name for storing in the media
  88.       IF @backupType = 'F'
  89.             SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
  90.       IF @backupType = 'D'
  91.             SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
  92.       IF @backupType = 'L'
  93.             SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
  94.  
  95. -- Generate the dynamic SQL command to be executed
  96.  
  97.        IF @backupType = 'F'
  98.                   BEGIN
  99.                SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
  100.                   END
  101.        IF @backupType = 'D'
  102.                   BEGIN
  103.                SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
  104.                   END
  105.        IF @backupType = 'L'
  106.                   BEGIN
  107.                SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
  108.                   END
  109.  
  110. -- Execute the generated SQL command
  111.        EXEC(@sqlCommand)
  112.  
  113. -- Goto the next database
  114. SELECT @LOOP = MIN(ID) FROM @DBs WHERE ID>@LOOP
  115.  
  116. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement