Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Backup databases stored procedure

EDIT: This stored procedure has been updated.

This stored procedure performs a full backup of the databases.  It has two input parameters.  The first input parameter is the path to where you would like to backup the databases.  It'll create a sub directory underneath that path for each of the databases.  It also deletes any files that are older than two days in that sub directory.  If a sub directory doesn't exist, it creates one for you.  The second input parameter is to control which databases you want to backup.  You can pass it All, System, or User.  All means backup all databases (except tempdb of course).  System means backup master, model, and msdb.  User means backup the user databases. 

I have heavily commented it so that it is easier to understand.  I am not normally so verbose in my coding.

I've also got a SQL Litespeed stored procedure that is very similar to this one.  I'll post that one soon.

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_Backup
--
-- AUTHOR               : Tara Duggan
--
-- INPUTS    : @Path - location of the backups
--        @dbType - which databases to backup - All, System, or User
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional)  : EXEC isp_Backup @Path = 'F:\MSSQL\Backup\', @dbType = 'All'
----------------------------------------------------------------------------------------------------
CREATE          PROC isp_Backup
(@Path VARCHAR(100), @dbType VARCHAR(6))
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 @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file

CREATE TABLE #WhichDatabase
(
 dbName SYSNAME NOT NULL
)

-- Get the list of the databases to be backed up
IF @dbType = 'All'

 INSERT INTO #WhichDatabase (dbName)
 SELECT [name]
 FROM master.dbo.sysdatabases
 WHERE [name] <> 'tempdb'
 ORDER BY [name]

ELSE
BEGIN

 IF @dbType = 'System'

  INSERT INTO #WhichDatabase (dbName)
  SELECT [name]
  FROM master.dbo.sysdatabases
  WHERE [name] IN ('master', 'model', 'msdb')
  ORDER BY [name]

 ELSE
 BEGIN

  IF @dbType = 'User'

   INSERT INTO #WhichDatabase (dbName)
   SELECT [name]
   FROM master.dbo.sysdatabases
   WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb')
   ORDER BY [name]

  ELSE
  BEGIN

   DROP TABLE #WhichDatabase

   RETURN -1

  END

 END

END

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbName
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 .BAK path and file name
 SELECT @filename = @Path + @DBName + '\' + @DBName + '_' + @Now + '.BAK'
 
 -- 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 '%

%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - 2

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

 -- Backup the database
 BACKUP DATABASE @DBName
 TO DISK = @filename
 WITH INIT

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

 -- Get the database to be backed up
 SELECT TOP 1 @DBName = dbName
 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 0

Print | posted on Wednesday, June 16, 2004 3:01 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# Backup databases stored procedure - SQL LiteSpeed version

6/17/2004 7:31 PM | Ramblings of a DBA
Gravatar

# Backup databases stored procedure - SQL LiteSpeed version

6/17/2004 7:33 PM | Ramblings of a DBA
Gravatar

# re: Backup databases stored procedure

How do you handle DIFF/TRANS backups?

And alerting when a backup fails?
6/17/2004 8:42 PM | Kristen
Gravatar

# re: Backup databases stored procedure

I don't do DIFFS. I've got another stored proc for tlogs, which I plan on posting here soon. Alerting when a backup fails is handled by the job failure notification.
6/18/2004 9:06 AM | Tara
Gravatar

# re: Backup databases stored procedure

>Alerting when a backup fails is handled by the job failure notification

Obvious now you've said it, thanks!

I am getting scared that all my stuff is so complex by comparison.

I don't delete files until the new backup is done (successfully) [could be a killer on a sytem tight for disk space, of course] so that I don't wind up with zero backups on disk if it fails repeatedly - I don't trust the Tape Backup Boys.

I log all backups to a table, and calculate when they should next be run etc. Bit like Maintenance Plan really!

Log each action to a table on start and remove row on completion - so if it fails there will be a record created which has not been deleted that external processes can interogate.

Have another process running on a different machine which queries the BackupLog table to see if there has been NOTHING run in, say, 60 minutes and send EMail - this traps catestrophic failure / SQL Agent not running / etc.

I wish I could think "simplificate and add lightness" or what ever it was that Henry Ford said.
6/18/2004 11:48 AM | Kristen
Gravatar

# re: Backup databases stored procedure

For space reason, I only want to keep 2 days on disk. How can I alter the SP to remove any files that are older than 2 days?

TIA,
Jeff
7/12/2004 7:27 AM | Jeff
Gravatar

# re: Backup databases stored procedure

What are the advatages to using your sp, as compared to just using a built-in database maintenance plan? I like what you did in your script, but I'm not knowledgeable enough to know the advantages to using it over what's provided with doing a Database Maintenance Plan.

Thanks for your knowledge sharing! :)
7/12/2004 7:31 AM | Stephen Modoy
Gravatar

# re: Backup databases stored procedure

What are the advatages to using your sp, as compared to just using a built-in database maintenance plan? I like what you did in your script, but I'm not knowledgeable enough to know the advantages to using it over what's provided with doing a Database Maintenance Plan.

Thanks for your knowledge sharing! :)
7/12/2004 7:32 AM | Stephen Moody
Gravatar

# Backup databases stored procedure - new version

8/4/2004 2:03 PM | Ramblings of a DBA
Gravatar

# Backup databases stored procedure - new version

8/4/2004 2:07 PM | Ramblings of a DBA
Gravatar

# Backup databases stored procedure - SQL LiteSpeed version

9/23/2004 6:31 PM | Ramblings of a DBA
Gravatar

# re: Backup databases stored procedure

Let´s suppose i don´t have a backuplog table to check when an error occurs in the procedure. If some of the backups in the stored procedure fails, isn´t there a way for the job to get the error from the procedure, like a ordinal SQL job failure?

tks

André Pizzi
5/11/2006 3:34 PM | André Pizzi
Gravatar

# re: Backup databases stored procedure

I don't understand what you mean by a backuplog table. My stored procedure doesn't have this functionality. If anything fails in it, the job fails.
5/11/2006 3:57 PM | Tara
Gravatar

# re: Backup databases stored procedure

how to do this on SQL Server 2005?
7/11/2006 12:56 AM | lence
Gravatar

# re: Backup databases stored procedure

There are a few changes that need to be made to get this to work for 2005. I've actually rewritten this stored procedure so that it supports both versions. I haven't gotten around to posting it yet though.

Wherever you see master.dbo.sysdatabases, you'll need to change it to master.sys.databases.
7/11/2006 9:53 AM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET