SQL Server Backup proc (VDS)

Root > Virtual Dedicated Server (VDS) > SQL Server (VDS)
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
Article ID: 680, , Modified: 10/14/2010 at 10:48 AM

Share this article