MSSQL: Unterschied zwischen den Versionen

Aus MeinWiki
Wechseln zu: Navigation, Suche
(Backupscript (Procedure))
(Backupscript (Procedure))
Zeile 2: Zeile 2:
 
== MSSQL ==
 
== MSSQL ==
 
=== Backupscript (Procedure) ===
 
=== Backupscript (Procedure) ===
 +
Backupscript sichert alle Datenbanken.
  
 
       ALTER PROCEDURE BackupDatabase
 
       ALTER PROCEDURE BackupDatabase
Zeile 12: Zeile 13:
 
        
 
        
 
       -- specify database backup directory
 
       -- specify database backup directory
       SET @path = '\\ssta-netap5.paricorp.net\BackupMSSQL2\I0\'  
+
       SET @path = '\\servername.domain\BackupMSSQL2\I0\'  
 
        
 
        
 
       -- specify filename format
 
       -- specify filename format

Version vom 23. August 2014, 20:58 Uhr

MSSQL

Backupscript (Procedure)

Backupscript sichert alle Datenbanken.

      ALTER PROCEDURE BackupDatabase
      AS
      DECLARE @name VARCHAR(256) -- database name  
      DECLARE @path VARCHAR(256) -- path for backup files  
      DECLARE @fileName VARCHAR(256) -- filename for backup  
      DECLARE @fileDate VARCHAR(20) -- used for file name
      DECLARE @recovery_model_desc VARCHAR(50) -- check recovery model
      
      -- specify database backup directory
      SET @path = '\\servername.domain\BackupMSSQL2\I0\' 
      
      -- specify filename format
      SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
      
      DECLARE db_cursor CURSOR FOR  
      SELECT name 
      FROM master.dbo.sysdatabases 
      WHERE name NOT IN ('tempdb')  -- exclude these databases
      
      OPEN db_cursor   
      
      FETCH NEXT FROM db_cursor INTO @name   
      WHILE @@FETCH_STATUS = 0   
      BEGIN   
             SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
             BACKUP DATABASE @name TO DISK = @fileName
      
             SELECT @recovery_model_desc = recovery_model_desc FROM sys.databases WHERE name = @name
             IF @recovery_model_desc = 'FULL'
             BEGIN        
                    SET @fileName = @path + @name + '_' + @fileDate + '.LOG'  
                    BACKUP LOG @name TO DISK = @fileName 
             END
             FETCH NEXT FROM db_cursor INTO @name   
      END   
      
      CLOSE db_cursor   
      DEALLOCATE db_cursor