MSSQL: Unterschied zwischen den Versionen

Aus MeinWiki
Wechseln zu: Navigation, Suche
(Truncate Logfile (Procedure))
(Backupscript (Procedure))
Zeile 42: Zeile 42:
 
       CLOSE db_cursor   
 
       CLOSE db_cursor   
 
       DEALLOCATE db_cursor
 
       DEALLOCATE db_cursor
 +
 +
* Backup with Logfile.
 +
Table for Logentry.
 +
    GO
 +
    CREATE TABLE dbo.BackupLog
 +
      (
 +
        ID bigint NOT NULL IDENTITY (1, 1),
 +
        TYPE nchar(10) NOT NULL,
 +
        LOGDATE datetime NOT NULL,
 +
        DBNAME nchar(256) NOT NULL,
 +
        FILENAME nchar(512) NOT NULL
 +
      )  ON [PRIMARY]
 +
    GO
 +
    ALTER TABLE dbo.BackupLog ADD CONSTRAINT
 +
PK_BackupLog PRIMARY KEY CLUSTERED
 +
(
 +
ID
 +
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 +
Stored Procedures
 +
ALTER PROCEDURE [dbo].[BackupDatabase]
 +
AS
 +
DECLARE @subDirectory VARCHAR(2)-- Subdirectory for Backup !! must change!!
 +
DECLARE @dbName VARCHAR(256) -- database name 
 +
DECLARE @countName INT  -- Count of database
 +
DECLARE @filePath 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 @subDirectory  = 'I0' 
 +
SET @filePath = '\\ssta-netap5.paricorp.net\BackupMSSQL2\' + @subDirectory + '\' 
 +
 +
-- specify filename format
 +
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
 +
 +
 +
SELECT @countName = Count(name)
 +
FROM master.dbo.sysdatabases
 +
WHERE name NOT IN ('tempdb')  -- exclude these databases
 +
 +
--Logfile
 +
INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
 +
VALUES ('START-SC', GETDATE(), 'Backup to ' + @subDirectory, 'Countdatabase = ' + Cast(@countName as VARCHAR(10)) )
 +
 +
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 @dbName 
 +
 +
WHILE @@FETCH_STATUS = 0 
 +
BEGIN 
 +
      SET @fileName = @filePath + @dbName + '_' + @fileDate + '.BAK' 
 +
      INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
 +
VALUES ('START', GETDATE(), @dbName, @fileName)
 +
      BACKUP DATABASE @dbName TO DISK = @fileName
 +
      INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
 +
VALUES ('STOP', GETDATE(), @dbName, @fileName)
 +
 +
      SELECT @recovery_model_desc = recovery_model_desc FROM sys.databases WHERE name = @dbName
 +
      IF @recovery_model_desc = 'FULL'
 +
      BEGIN       
 +
SET @fileName = @filePath + @dbName + '_' + @fileDate + '.LOG' 
 +
INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
 +
VALUES ('START', GETDATE(), @dbName, @fileName)
 +
BACKUP LOG @dbName TO DISK = @fileName
 +
INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
 +
VALUES ('STOP', GETDATE(), @dbName, @fileName)
 +
  END
 +
      FETCH NEXT FROM db_cursor INTO @dbName 
 +
END 
 +
 +
CLOSE db_cursor 
 +
DEALLOCATE db_cursor
 +
--Logfile
 +
INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)
 +
VALUES ('STOP-SC', GETDATE(), 'Backup to ' + @subDirectory, 'Countdatabase = ')
 +
--Cean Logfile
 +
delete BackupLog where Logdate < (select GETDATE() -30);
  
 
=== Truncate Logfile (Procedure) ===
 
=== Truncate Logfile (Procedure) ===

Version vom 16. Dezember 2014, 10:22 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
  • Backup with Logfile.

Table for Logentry.

   GO
   CREATE TABLE dbo.BackupLog
      (
       ID bigint NOT NULL IDENTITY (1, 1),
       TYPE nchar(10) NOT NULL,
       LOGDATE datetime NOT NULL,
       DBNAME nchar(256) NOT NULL,
       FILENAME nchar(512) NOT NULL
      )  ON [PRIMARY]
   GO
   ALTER TABLE dbo.BackupLog ADD CONSTRAINT

PK_BackupLog PRIMARY KEY CLUSTERED ( ID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] Stored Procedures ALTER PROCEDURE [dbo].[BackupDatabase] AS DECLARE @subDirectory VARCHAR(2)-- Subdirectory for Backup !! must change!! DECLARE @dbName VARCHAR(256) -- database name DECLARE @countName INT -- Count of database DECLARE @filePath 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 @subDirectory = 'I0' SET @filePath = '\\ssta-netap5.paricorp.net\BackupMSSQL2\' + @subDirectory + '\'

-- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)


SELECT @countName = Count(name) FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb') -- exclude these databases

--Logfile INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME) VALUES ('START-SC', GETDATE(), 'Backup to ' + @subDirectory, 'Countdatabase = ' + Cast(@countName as VARCHAR(10)) )

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 @dbName

WHILE @@FETCH_STATUS = 0 BEGIN

      SET @fileName = @filePath + @dbName + '_' + @fileDate + '.BAK'  
      INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)

VALUES ('START', GETDATE(), @dbName, @fileName)

      BACKUP DATABASE @dbName TO DISK = @fileName 
      INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME)

VALUES ('STOP', GETDATE(), @dbName, @fileName)

      SELECT @recovery_model_desc = recovery_model_desc FROM sys.databases WHERE name = @dbName
      IF @recovery_model_desc = 'FULL'
      BEGIN        

SET @fileName = @filePath + @dbName + '_' + @fileDate + '.LOG' INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME) VALUES ('START', GETDATE(), @dbName, @fileName) BACKUP LOG @dbName TO DISK = @fileName INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME) VALUES ('STOP', GETDATE(), @dbName, @fileName) END

      FETCH NEXT FROM db_cursor INTO @dbName   

END

CLOSE db_cursor DEALLOCATE db_cursor --Logfile INSERT INTO BackupLog (TYPE, LOGDATE, DBNAME, FILENAME) VALUES ('STOP-SC', GETDATE(), 'Backup to ' + @subDirectory, 'Countdatabase = ') --Cean Logfile delete BackupLog where Logdate < (select GETDATE() -30);

Truncate Logfile (Procedure)

      DECLARE c CURSOR FOR SELECT database_id, name, recovery_model_desc FROM sys.databases -- WHERE name='sharepoint_config'
      DECLARE @dbname VARCHAR(1024)
      DECLARE @rmod VARCHAR(1024)
      DECLARE @id INT
      DECLARE @lfile VARCHAR(1024) 
      OPEN c
      
      FETCH NEXT FROM c INTO @id, @dbname, @rmod WHILE @@FETCH_STATUS = 0
      BEGIN
             IF @rmod = 'FULL'
             BEGIN
                    SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
                    PRINT @lfile
                    EXEC('ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE')
                    EXEC('USE ['+@dbname+']; DBCC SHRINKFILE(['+@lfile+'], 1)')
                    EXEC('ALTER DATABASE [' + @dbname + '] SET RECOVERY FULL	')
             END ELSE
             IF @rmod = 'SIMPLE'
             BEGIN
                    SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
                    PRINT @lfile
                    EXEC('USE ['+@dbname+']; DBCC SHRINKFILE(['+@lfile+'], 1)')
             END
             FETCH NEXT FROM c INTO @id, @dbname,@rmod
      END
      
      CLOSE c
      DEALLOCATE c