USE msdb |
GO |
CREATE PROC [dbo].[sp_backupDatabases] (@Path VARCHAR(128), @Retry INT = 0) |
AS |
BEGIN |
|
SET NOCOUNT ON |
|
DECLARE @DBMode CHAR(64) -- status of the database |
DECLARE @RC INT -- command output handling |
DECLARE @Retention INT -- number of days in the past backups are kept |
DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss |
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed |
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command |
DECLARE @Result INT -- stores the result of the dir DOS command |
DECLARE @NumProcs INT -- stores the number of processors that the server has registered |
DECLARE @RowCnt INT -- stores @@ROWCOUNT |
DECLARE @desc VARCHAR(512) -- stores the description of the backup |
DECLARE @filename VARCHAR(1024) -- stores the path and file name of the bkp file |
DECLARE @BackupCommand VARCHAR(2048) -- Long Varchar for backup Command |
DECLARE @cmdlinepath VARCHAR(512) --Command line path to the LightSpeed Executable |
DECLARE @RetryCount INT |
DECLARE @Continue BIT |
|
-- Create table if it does not exist to store information on backed up databases |
IF NOT EXISTS (SELECT * from msdb.dbo.sysobjects where id = object_id(N'[msdb].[dbo].[Backups]')) BEGIN |
CREATE TABLE [msdb].[dbo].[Backups] ( |
[id] [int] IDENTITY (1, 1) NOT NULL , |
[dbname] [varchar] (64) COLLATE Latin1_General_CI_AI NOT NULL , |
[dt] [datetime] NOT NULL , |
[status] [varchar] (32) COLLATE Latin1_General_CI_AI NOT NULL , |
[result] [varchar] (32) COLLATE Latin1_General_CI_AI NOT NULL , |
[destination] [varchar] (512) COLLATE Latin1_General_CI_AI NOT NULL |
) ON [PRIMARY] |
END |
|
EXECUTE sp_configure 'show advanced options', 1 |
RECONFIGURE WITH OVERRIDE |
|
EXECUTE sp_configure 'xp_cmdshell', '1' |
RECONFIGURE WITH OVERRIDE |
|
EXECUTE sp_configure 'show advanced options', 0 |
RECONFIGURE WITH OVERRIDE |
|
--how long to keep backups |
SELECT @Retention = 7 |
|
--Delete old records from table |
DELETE |
FROM dbo.Backups |
WHERE (DateDiff(day, dt, GETDATE()) > @Retention) |
|
-- If Retry is set to 1 run only db's that have not been completed that day |
DECLARE DatabaseCursor CURSOR FOR |
SELECT name as DBName |
FROM master.dbo.sysdatabases |
WHERE name NOT IN ('tempdb') |
ORDER BY name |
|
-- Open Cursor to loop thought the dbbases to be backed up |
OPEN DatabaseCursor |
|
FETCH NEXT FROM DatabaseCursor |
INTO @DBName |
|
WHILE @@FETCH_STATUS=0 BEGIN |
|
IF @Retry = 1 BEGIN |
|
SELECT @RetryCount = COUNT(*) |
FROM msdb.dbo.Backups |
WHERE dbname = @dbname |
AND DateDiff(day, dt, GETDATE()) = 0 |
AND result = 'Completed' |
|
IF @RetryCount < 1 BEGIN |
SELECT @Continue = 1 |
END ELSE BEGIN |
SELECT @Continue = 0 |
END |
END |
|
IF @Continue = 1 or @Retry = 0 BEGIN |
|
-- Get the current date using style 120, remove all dashes, spaces, and colons |
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '') |
|
-- Build the .bkp path and file name |
SELECT @filename = REPLACE(@Path + @DBName + '\' + @DBName + '_db_' + @Now + '.bak', ' ', '') |
|
-- Build the description of the backup |
SELECT @desc = 'Full backup of ' + @DBName + ' - ' + CONVERT(VARCHAR(50), GETDATE()) |
PRINT '-' |
PRINT '-' |
PRINT @desc |
|
-- Build the dir command that will check to see if the directory exists |
SELECT @cmd = 'dir ' + REPLACE(@Path + @DBName, ' ', '') |
|
-- Run the dir command, put output of xp_cmdshell into @result |
EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT |
|
-- If the directory does not exist, we must create it |
IF @result <> 0 BEGIN |
-- Build the mkdir command |
SELECT @cmd = 'mkdir ' + REPLACE(@Path + @DBName, ' ', '') |
|
-- Create the directory |
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT |
|
-- The directory exists, so let's delete files older than two days |
END ELSE BEGIN |
|
-- Stores the name of the file to be deleted |
DECLARE @WhichFile VARCHAR(1024) |
|
CREATE TABLE ##DeleteOldFiles (DirInfo VARCHAR(7000)) |
|
-- Build the command that will list out all of the files in a directory |
SELECT @cmd = 'dir ' + REPLACE(@Path + @DBName, ' ', '') + ' /OD' |
|
-- Run the dir command and put the results into a temp table |
INSERT ##DeleteOldFiles EXEC master.dbo.xp_cmdshell @cmd |
|
-- Delete all rows from the temp table except the ones that correspond to the files to be deleted |
DELETE |
FROM ##DeleteOldFiles |
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @Retention |
|
-- Get the file name portion of the row that corresponds to the file to be deleted |
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) |
FROM ##DeleteOldFiles |
|
SET @RowCnt = @@ROWCOUNT |
|
-- Interate through the temp table until there are no more files to delete |
WHILE @RowCnt <> 0 BEGIN |
|
-- Build the del command |
SELECT @cmd = 'del ' + REPLACE(@Path + + @DBName + '\' + @WhichFile, ' ', '') + ' /Q /F' |
|
-- Delete the file |
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT |
|
-- To move to the next file, the current file name needs to be deleted from the temp table |
DELETE |
FROM ##DeleteOldFiles |
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @WhichFile |
|
-- Get the file name portion of the row that corresponds to the file to be deleted |
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) |
FROM ##DeleteOldFiles |
|
SET @RowCnt = @@ROWCOUNT |
|
END |
|
DROP TABLE ##DeleteOldFiles |
|
END |
|
-- Check database status |
SELECT @DBMode = 'OK' |
|
IF DATABASEPROPERTY(@DBName, 'IsDetached') > 0 |
SELECT @DBMode = 'Detached' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsInLoad') > 0 |
SELECT @DBMode = 'Loading' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsNotRecovered') > 0 |
SELECT @DBMode = 'Not Recovered' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsInRecovery') > 0 |
SELECT @DBMode = 'Recovering' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsSuspect') > 0 |
SELECT @DBMode = 'Suspect' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsOffline') > 0 |
SELECT @DBMode = 'Offline' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsEmergencyMode') > 0 |
SELECT @DBMode = 'Emergency Mode' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsShutDown') > 0 |
SELECT @DBMode = 'Shut Down (problems during startup)' |
ELSE IF DATABASEPROPERTY(@DBName, 'IsInStandBy') > 0 |
SELECT @DBMode = 'Standby' |
|
Print 'Database: ' + @DBName + ' is ' + @DBMode |
|
-- Backup the database using xp_backup_database only if databate is able |
IF @DBMode = 'OK' BEGIN |
|
PRINT 'Backing up Database...' |
|
BACKUP DATABASE @DBName |
TO DISK = @filename |
WITH |
DESCRIPTION = @desc, |
NAME = @DBName |
|
SELECT @RC = Count(@DBName) |
FROM backupset |
WHERE database_name = @DBName |
AND datediff( minute, backup_finish_date, GETDATE()) = 0 |
AND flags = 512 |
|
-- Write to Database based on result |
IF @RC > 0 BEGIN |
INSERT INTO msdb.dbo.Backups (dbname, dt, status, result, destination) |
Values (@DBName, GETDATE(), @DBMode, 'Completed', @filename) |
|
PRINT @DBName + ' backup COMPLETED' |
|
END ELSE BEGIN |
INSERT INTO msdb.dbo.Backups (dbname, dt, status, result, destination) |
Values (@DBName, GETDATE(), @DBMode, 'Failed', @filename) |
|
PRINT @DBName + ' backup FAILED' |
|
END |
|
END ELSE BEGIN |
INSERT INTO msdb.dbo.Backups (dbname, dt, status, result, destination) |
Values (@DBName, GETDATE(), @DBMode, 'Skipped', @filename) |
|
PRINT @DBName + ' backup SKIPPED' |
|
END |
|
END |
|
--Move to next db in the cursor |
FETCH NEXT FROM DatabaseCursor |
INTO @DBName |
|
END |
|
SET NOCOUNT OFF |
|
EXECUTE sp_configure 'show advanced options', 1 |
RECONFIGURE WITH OVERRIDE |
|
EXECUTE sp_configure 'xp_cmdshell', '0' |
RECONFIGURE WITH OVERRIDE |
|
EXECUTE sp_configure 'show advanced options', 0 |
RECONFIGURE WITH OVERRIDE |
|
RETURN 0 |
|
END |
|
|
|