Saturday, March 04, 2006

SQL Administration

I run a small hosting provider and one of my issues is backing up SQL databases. Everytime I create a database I have to create a script to back them up. Here is a handy script that I found and modified a bit that will backup all databases to a specified folder. It also keeps a retention history for a user defineable number of days

here is a sample usage:
isp_FullBackup_UserDBs 'c:\backups', 10

this will backup all user databases (note it wil not backup the master database) and keep a history of 10 days.




----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_FullBackup_UserDBs
--
-- AUTHOR : Tara Duggan
-- DATE : December 18, 2003
--
-- INPUTS : @Path - location of the backups
@HistoryDays - Number of days back to keep

-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional) : EXEC isp_FullBackup_UserDBs @Path = 'C:\MSSQL\Backup\', @HistoryDays=14
--
-- MODIFICATION HISTORY :
-- Jan 3, 2005: David Woods - Added the @HistoryDays filter to allow a user specified time period of backups
-- Feb 2, 2005: David Woods - Fixed a bug that would not backup databases with dashes ('-') in the name.

----------------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------------------------------------
CREATE PROC isp_FullBackup_UserDBs
@Path VARCHAR(100),
@HistoryDays int --number of days to hold the database
AS

SET NOCOUNT ON

DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
DECLARE @SQL VARCHAR(7000) -- stores the dynamically created xp_backup_database command
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT

--make sure our path has a trailing slash otherwise we will have a mess of prefixed directories
if SUBSTRING(@path, len(@path), 1) != '\'
Set @Path = @Path + '\'

-- Get the list of the databases to be backed up, does not include master, model, msdb, tempdb, Northwind, or pubs
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind')
ORDER BY name

-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 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 dir command that will check to see if the directory exists
SELECT @cmd = 'dir ' + @Path + @DBName

-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master.dbo.xp_cmdshell @cmd

-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN

-- Build the mkdir command
SELECT @cmd = 'mkdir ' + @Path + @DBName

-- Create the directory
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

END
-- The directory exists, so let's delete files older than two days
ELSE
BEGIN

-- Stores the name of the file to be deleted
DECLARE @WhichFile VARCHAR(1000)

CREATE TABLE #DeleteOldFiles
(
DirInfo VARCHAR(7000)
)

-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @Path + @DBName + ' /OD'

-- Run the dir command and put the results into a temp table
INSERT INTO #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 DirInfo LIKE '%<dir>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @HistoryDays

-- 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 ' + @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

-- Build the xp_backup_database command dynamically
SELECT @SQL = ''
SELECT @SQL = @SQL + 'BACKUP DATABASE [' + @DBName + ']' + CHAR(10)
SELECT @SQL = @SQL + 'TO DISK = ''' + @Path + @DBName + '\' + @DBName + '_' + @Now + '.BAK''' + CHAR(10)
SELECT @SQL = @SQL + 'WITH INIT ' + CHAR(10)
print @SQL
-- Backup the database using xp_backup_database
EXEC (@SQL)

-- To move onto the next database, the current database name needs to be deleted from the temp table
DELETE
FROM #WhichDatabase
WHERE name = @DBName

-- Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'

END

DROP TABLE #WhichDatabase

SET NOCOUNT OFF

RETURN

GO

0 Comments:

Post a Comment

<< Home