MSSQL: Unterschied zwischen den Versionen
(→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