Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Backup databases stored procedure - new version

EDIT: Please see my new version of this stored procedure.

I've been asked a few times if there was a way to change the number of days to retain the backup files for in my backup stored procedure.  Due to this, I have modified the sproc so that you can now input the number of days to retain the files for.  Here is the updated version:

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_Backup
--
-- AUTHOR               : Tara Duggan
-- DATE     : December 18, 2003
--
-- INPUTS    : @Path - location of the backups
--        @dbType - which databases to backup - All, System, or User
--        @Retention - numbers of days to retain backups
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional)  : EXEC isp_Backup @Path = 'E:\MSSQL\Backup\', @dbType = 'All', @Retention = 5
----------------------------------------------------------------------------------------------------
CREATE            PROC isp_Backup
(@Path VARCHAR(100), @dbType VARCHAR(6), @Retention INT = 2)
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' AND
    [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases)
 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') AND
      [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases)
   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, NO_OUTPUT

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

 


I will also post the updated LS and transaction log sprocs as well.

 

BTW, this sproc is designed for SQL Server 2000 Enterprise Edition only.  For Standard Edition, change this:

 INSERT INTO #WhichDatabase (dbName)
 SELECT [name]
 FROM master.dbo.sysdatabases
 WHERE [name] <> 'tempdb' AND
    [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases)
 ORDER BY [name]

to:

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

Print | posted on Wednesday, August 04, 2004 11:03 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# Backup databases stored procedure

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

# re: Backup databases stored procedure - new version

Thanks very much for a very informative website, it nice that you take some time out to help aspiring DBA's :) Looking forward to your transaction log & LS sprocs.

Best Regards

Gary
8/10/2004 2:01 AM | Gary
Gravatar

# re: Backup databases stored procedure - new version

Hi,

Sorry for the stupid question, but I having troubles trying to backup the database to a remote (network shared driver)

This is how I run it:
EXEC isp_Backup @Path = 'Z:\BACKUP\MTA_SQLTEST_BACKUP', @dbType = 'User', @Retention = 5

And this is the result:
Server: Msg 3201, Level 16, State 1, Procedure isp_Backup, Line 175
Cannot open backup device 'Z:\BACKUP\MTA_SQLTEST_BACKUPEQUOTE\EQUOTE_20040819101027.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Procedure isp_Backup, Line 175
BACKUP DATABASE is terminating abnormally.
Server: Msg 3201, Level 16, State 1, Procedure isp_Backup, Line 175
Cannot open backup device 'Z:\BACKUP\MTA_SQLTEST_BACKUPMTA_TEST\MTA_TEST_20040819101033.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Procedure isp_Backup, Line 175
BACKUP DATABASE is terminating abnormally.

Thanks
8/19/2004 7:17 AM | Ivan
Gravatar

# Never mind guys...I found the problem

I changed the command to

This is how I run it:
EXEC isp_Backup @Path = '\\SERVER\BACKUP\MTA_SQLTEST_BACKUP', @dbType = 'User', @Retention = 5

and it is working now

Thanks
8/19/2004 8:30 AM | Ivan
Gravatar

# re: Backup databases stored procedure - new version


Thanks for the wonderful stored procedure -- it is a godsend for jumpstarting my two-week-old database and application development career (former web designer with no comprehension of the back end of things).

Gordon
9/4/2004 4:41 PM | Gordon Withers
Gravatar

# Database maintenance routines

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

# re: Backup databases stored procedure - new version

Tara:

Great site with great info.

I modified the SP isp_Backup, to add support for single database backup. This change will help to take backup for single database also. Thanks again for the script.


-- 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'
AND [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases)
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')
AND [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases)
ORDER BY [name]
ELSE
BEGIN
IF @dbType != ''
IF EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @dbType)
INSERT INTO #WhichDatabase (dbName)
SELECT @dbType
ELSE
BEGIN
DROP TABLE #WhichDatabase
RETURN -1
END
ELSE
BEGIN
DROP TABLE #WhichDatabase
RETURN -1
END
END
END
END
9/24/2004 9:55 AM | Jeet
Gravatar

# re: Backup databases stored procedure - new version

I have wierd problem,, when i run this script to backup onto a network share -- it locks up that machine and the backup fails with this error

BackupDiskFile::ReruestDurableMedia: failure on backup device '\\fs-arc\ndb\srlbackup\nightlyBackup\arc\arc_20050706144259.BAK'. Operating system error 64(The specified network name is no longer available.).

as soon as i kill the job, fs-arc machine is back to normal ,, this machine is a network file server and shows no problem with anythign else,, just the backup,, i tried to define a manitainace plan but got the same error

any ideas? i am at loss

thanks
7/6/2005 1:13 PM | hassan mir
Gravatar

# re: Backup databases stored procedure - new version

You are probably running into a permission or network problem which is outside of the scope of this stored procedure.

I do not recommend backing up directly to a network share. Backup the database to the local disk using this stored procedure. Then use xcopy to move the backup file to the network share. Your backups will be more reliable if you use this method.

7/6/2005 1:16 PM | Tara
Gravatar

# re: Backup databases stored procedure - new version

one more thing, i tried to do the backup on local disk and then to a different network share (ohter than the machine that is causing the problem) ,, both worked just fine. thanks
7/6/2005 1:35 PM | hassan mir
Gravatar

# re: Backup databases stored procedure - new version

how do i use XCOPY from within the script,, so i can copy it to local disk and at the end of it XCOPY it to network share,, thanks
7/6/2005 2:21 PM | hassan mir
Gravatar

# re: Backup databases stored procedure - new version

You wouldn't do this within the same script. Just add another step to your job. This second step would use CmdExec to execute xcopy. Run xcopy /? from a cmd window to see its options.
7/6/2005 2:53 PM | Tara
Gravatar

# re: Backup databases stored procedure - new version

Script needs to be updated to support use on a Case Sensitive installation of SQL server.

@Result needs to be consistently used (somtimes @result and sometimes @Result) and sysname needs to be lowercase..
8/31/2005 9:07 PM | Jim Hughes
Gravatar

# re: Backup databases stored procedure - new version

Hi thanks for your, sp works well. The only thing I had to do is change it a little to work on the system with British date format dd/mm/yyyy. Thanks again.

Your Code
-------
DELETE
FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @WhichFile
-------
Your Code End

to

Change Code Start
-------
DELETE FROM #DeleteOldFiles WHERE SUBSTRING(DirInfo, 3, 1) <> '/' OR ISNULL(DirInfo, '') = ''

DELETE
FROM #DeleteOldFiles
WHERE ISDATE(CONVERT(DATETIME, SUBSTRING(DirInfo, 1, 10), 103)) = 0 OR CONVERT(DATETIME, SUBSTRING(DirInfo, 1, 10), 103) >= GETDATE() - @Retention
-------
Change Code End
9/15/2005 10:30 PM | Dmitrey
Gravatar

# re: Backup databases stored procedure - new version

Hi,
I need a sp which give me result for a specified database not for all database on server.

Regards
Girish
10/13/2005 10:37 PM | girish
Gravatar

# re: Backup databases stored procedure - new version

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E09)
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
10/14/2005 1:06 AM | girish
Gravatar

# re: Backup databases stored procedure - new version

My stored procedures are designed from the perspective of a sysadmin. Since you got a permission denied error on xp_cmdshell, then you must not be a member of that role. My stored procedures are not designed for one individual database, but they certainly can be rewritten easily.
10/14/2005 9:36 AM | Tara
Gravatar

# re: Backup databases stored procedure - new version

Thanks for the cod. But it wouldn't work when running the stored proc in remote server and trying to take backup in the local machine.

Any solution is highly appreciable.

Thanks in advance.
12/12/2005 4:24 AM | Himansu
Gravatar

# re: Backup databases stored procedure - new version

Himansu,

You'll need to explain what you tried. The stored procedure is designed to run locally on the database server that you wish to backup. It can accept a network path as the location for the backups to be saved to. But it must be run locally. Running it remotely is not supported by my code.
12/15/2005 9:15 AM | Tara
Gravatar

# re: Backup databases stored procedure - new version

The prcedure does not work when backing up single database, and name of database contains "-"
5/18/2006 12:50 AM | mirel
Gravatar

# re: Backup databases stored procedure - new version

UPS SORRY,MY MISTAKE
ARGUMENT SIZE @dbType VARCHAR(6) IS TO SMALL

WHEN I REPLACE WITH THIS
@dbType VARCHAR(100) PROCEDURE WORK



5/18/2006 1:32 AM | mirel
Gravatar

# re: Backup databases stored procedure - new version

mirel, what are you passing into @dbType that requires a length greater than 6? The stored procedure does not support anything greater than 6, so I'm not sure what you are trying to do.
5/18/2006 9:19 AM | Tara
Gravatar

# re: Backup databases stored procedure - new version

Create Procedure BackupDatabase
(
@ID int OUTPUT
)
@Database Varchar(100),
@BackupDir Varchar(500)
As
If Right(@BackupDir,1)='\'
Set @BackupDir=@BackupDir + @Database +
(SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', ''))+'.Bak'
Else
Set @BackupDir =@BackupDir + '\' + @Database + (SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', ''))+'.Bak'
Backup Database @Database
To Disk =@Backupdir
With STATs =10



How to get results for sucess and failure for above sp
6/15/2006 12:14 AM | hrudhay
Gravatar

# re: Backup databases stored procedure - new version

Hi all.

I tried to run the modified Jeet's version, for a single database, and it backup nothing ... any ideas?... i recieved no error, but it does not backup either ..
7/13/2006 12:31 PM | Joe
Gravatar

# Databases with space on the name

Tara/All -
Just an FYI,
I loved the sp, however, there's only one small issue, the sp fails to back up the databases with spaces on the database name (i.e. 'Customer Database'). Unusual to all of us, but I thought you might want to know that.

Thanks!

igor
8/16/2006 8:06 AM | Igor
Gravatar

# Enabling xp_cmdshell in SQL Server 2005

9/14/2006 7:51 PM | Ramblings of a DBA
Gravatar

# Enabling xp_cmdshell in SQL Server 2005

9/14/2006 7:52 PM | Ramblings of a DBA
Gravatar

# re: Backup databases stored procedure - new version

I have this running on one server and it works great. I just set up a new SQL 2000 server and tried running it and I'm getting an error. It tells me the table msdb.dbo.log_shipping_databases doesn't exist, and it doesn't. Did I miss something during my installation? I'm not doing log shipping or anything special, just a standard SQL 2000 Server install. Any suggestions? Am I missing something stupid?
10/11/2006 1:22 PM | John Burns
Gravatar

# re: Backup databases stored procedure - new version

John, comment that part out of the code. I wrote it for Enterprise Edition which supports log shipping. Standard Edition does not.
10/11/2006 1:28 PM | Tara
Gravatar

# re: Backup databases stored procedure - new version

good
11/10/2006 1:52 AM |
Gravatar

# Enabling xp_cmdshell in SQL Server 2005

By default, xp_cmdshell is disabled in SQL Server 2005 for security reasons. I am currently using xp_cmdshell
11/21/2006 1:03 PM | SQL Server Feed Roller
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET