Tara Kizer Blog

Tara Kizer

Backup databases stored procedure - new version

EDIT: A new version of this stored procedure is available here.

I've completely modified my isp_Backup stored procedure.  The new version supports both SQL Server 2000 and 2005.  It also gets rid of the need to use my other backup stored procedures, such as isp_Backup_TLog and isp_Backup_LS.

The code is heavily commented to help others understand what I am doing in the code.  I don't normally comment this much, but I realize that non-DBAs may be using this stored procedure instead of maintenance plans. 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

————————————————————————————————– – OBJECT NAME : isp_Backup – AUTHOR : Tara Kizer – INPUTS : @path - location of the backups, default backup directory used if @path is null – @dbType - which database(s) to backup – All, System, User, or dash followed by database name (ex. -Toolbox) – @bkpType - type of backup to perform – Full, TLog, Diff – @retention - number of days to retain backups, -1 to retain all files – @liteSpeed - perform backup using LiteSpeed (Imceda product) – N, Y – OUTPUTS : None – RETURN CODES : 0-10 (see @error table variable at the end for the messages) – DEPENDENCIES : None – DESCRIPTION : Performs backups. – EXAMPLES (optional) : EXEC isp_Backup @path = 'C:\MSSQL\Backup', @dbType = 'All', @bkpType = 'Full', @retention = 5, @liteSpeed = 'N' ————————————————————————————————– CREATE PROC [dbo].[isp_Backup] (@path varchar(100), @dbType sysname = 'All', @bkpType char(4) = 'Full', @retention smallint = 2, @liteSpeed char(1) = 'N') AS

SET NOCOUNT ON

DECLARE @now char(14) – current date in the form of yyyymmddhhmmss DECLARE @dbName sysname – database name that is currently being processed DECLARE @cmd nvarchar(4000) – dynamically created DOS command DECLARE @result int – result of the dir DOS command DECLARE @rowCnt int – @@ROWCOUNT DECLARE @fileName varchar(200) – path and file name of the BAK file DECLARE @edition int – edition of SQL Server (1 - Personal or Desktop Engine; 2 - Standard; 3 - Developer or Enterprise) DECLARE @rc int – return code DECLARE @extension char(4) – extension for backup file DECLARE @version char(1) – one digit version number, i.e. 8 (2000) or 9 (2005)

– log shipping tables have been renamed in 2005 SET @version = CONVERT(char(1), SERVERPROPERTY('ProductVersion'))

IF @version NOT IN ('8', '9') BEGIN SET @rc = 1 GOTO EXIT_ROUTINE END

– Enterprise and Developer editions have msdb.dbo.log_shipping* tables, other editions do not SET @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

– validate input parameters IF @dbType IS NOT NULL AND @dbType NOT IN ('All', 'System', 'User') AND @dbType NOT LIKE '-%' BEGIN SET @rc = 2 GOTO EXIT_ROUTINE END

IF @dbType LIKE '-%' AND @version = '8' BEGIN IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType))) BEGIN SET @rc = 3 GOTO EXIT_ROUTINE END END ELSE IF @dbType LIKE '-%' AND @version = '9' BEGIN IF NOT EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType))) BEGIN SET @rc = 3 GOTO EXIT_ROUTINE END END

IF @bkpType IS NOT NULL AND @bkpType NOT IN ('Full', 'TLog', 'Diff') BEGIN SET @rc = 4 GOTO EXIT_ROUTINE END

IF @dbType = 'System' AND @bkpType <> 'Full' BEGIN SET @rc = 5 GOTO EXIT_ROUTINE END

IF @liteSpeed IS NOT NULL AND @liteSpeed NOT IN ('N', 'Y') BEGIN SET @rc = 6 GOTO EXIT_ROUTINE END

– use the default backup directory if @path is null IF @path IS NULL EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output, 'no_output'

– we need the backslash after the path, so add it if it wasn't provided in the input parameter IF RIGHT(@path, 1) <> '&#39; SET @path = @path + '&#39;

CREATE TABLE #WhichDatabase(dbName SYSNAME NOT NULL)

– put the databases to be backed up into temp table IF @dbType LIKE '-%' BEGIN IF @bkpType = 'TLog' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE' BEGIN SET @rc = 7 GOTO EXIT_ROUTINE END

<span class="kwrd">IF</span> @edition = 3
<span class="kwrd">BEGIN</span>
    <span class="kwrd">IF</span> @version = <span class="str">&#39;8&#39;</span>
    <span class="kwrd">BEGIN</span>
        <span class="kwrd">IF</span> <span class="kwrd">EXISTS</span> (<span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> msdb.dbo.log_shipping_databases <span class="kwrd">WHERE</span> database_name = <span class="kwrd">SUBSTRING</span>(@dbType, 2, DATALENGTH(@dbType)))
        <span class="kwrd">BEGIN</span>
            <span class="kwrd">SET</span> @rc = 8
            <span class="kwrd">GOTO</span> EXIT_ROUTINE
        <span class="kwrd">END</span>
    <span class="kwrd">END</span>
    <span class="kwrd">ELSE</span> <span class="kwrd">IF</span> @version = <span class="str">&#39;9&#39;</span> 
    <span class="kwrd">BEGIN</span>
        <span class="kwrd">IF</span> <span class="kwrd">EXISTS</span> (<span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> msdb.dbo.log_shipping_primary_databases <span class="kwrd">WHERE</span> primary_database = <span class="kwrd">SUBSTRING</span>(@dbType, 2, DATALENGTH(@dbType)))
        <span class="kwrd">BEGIN</span>
            <span class="kwrd">SET</span> @rc = 8
            <span class="kwrd">GOTO</span> EXIT_ROUTINE
        <span class="kwrd">END</span>
    <span class="kwrd">END</span>
<span class="kwrd">END</span>

<span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #WhichDatabase(dbName)
<span class="kwrd">VALUES</span>(<span class="kwrd">SUBSTRING</span>(@dbType, 2, DATALENGTH(@dbType))) 

END ELSE IF @dbType = 'All' BEGIN IF @edition = 3 AND @version = '8' INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.dbo.sysdatabases WHERE [name] NOT IN ('tempdb', 'ReportServerTempDB') AND [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] ELSE IF @edition = 3 AND @version = '9' INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('tempdb', 'ReportServerTempDB') AND [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] ELSE IF @version = '8' INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.dbo.sysdatabases WHERE [name] NOT IN ('tempdb', 'ReportServerTempDB') AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] ELSE – version is 9 INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('tempdb', 'ReportServerTempDB') AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] END ELSE IF @dbType = 'System' BEGIN IF @version = 8 INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.dbo.sysdatabases WHERE [name] IN ('master', 'model', 'msdb') ORDER BY [name] ELSE INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.sys.databases WHERE [name] IN ('master', 'model', 'msdb') ORDER BY [name] END ELSE IF @dbType = 'User' BEGIN IF @edition = 3 AND @version = '8' INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.dbo.sysdatabases WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] ELSE IF @edition = 3 AND @version = '9' INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] ELSE IF @version = '8' INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.dbo.sysdatabases WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] ELSE INSERT INTO #WhichDatabase (dbName) SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' ORDER BY [name] END ELSE – no databases to be backed up BEGIN SET @rc = 9 GOTO EXIT_ROUTINE 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

<span class="kwrd">IF</span> @bkpType = <span class="str">&#39;TLog&#39;</span> <span class="kwrd">AND</span> @dbType <span class="kwrd">IN</span> (<span class="str">&#39;All&#39;</span>, <span class="str">&#39;User&#39;</span>) <span class="kwrd">AND</span> DATABASEPROPERTYEX(@dbName, <span class="str">&#39;RECOVERY&#39;</span>) = <span class="str">&#39;SIMPLE&#39;</span>
    <span class="kwrd">PRINT</span> <span class="str">&#39;Skipping transaction log backup of &#39;</span> + @dbName
<span class="kwrd">ELSE</span> <span class="kwrd">IF</span> @bkpType = <span class="str">&#39;Diff&#39;</span> <span class="kwrd">AND</span> @dbName <span class="kwrd">IN</span> (<span class="str">&#39;master&#39;</span>, <span class="str">&#39;model&#39;</span>, <span class="str">&#39;msdb&#39;</span>)
    <span class="kwrd">PRINT</span> <span class="str">&#39;Skipping differential backup of &#39;</span> + @dbName
<span class="kwrd">ELSE</span>
<span class="kwrd">BEGIN</span>
    <span class="rem">-- Build the dir command that will check to see if the directory exists</span>
    <span class="kwrd">SET</span> @cmd = <span class="str">&#39;dir &#39;</span> + @<span class="kwrd">path</span> + @dbName

    <span class="rem">-- Run the dir command, put output of xp_cmdshell into @result</span>
    <span class="kwrd">EXEC</span> @<span class="kwrd">result</span> = master..xp_cmdshell @cmd, NO_OUTPUT

    <span class="rem">-- If the directory does not exist, we must create it</span>
    <span class="kwrd">IF</span> @<span class="kwrd">result</span> &lt;&gt; 0
    <span class="kwrd">BEGIN</span>
        <span class="rem">-- Build the mkdir command        </span>
        <span class="kwrd">SET</span> @cmd = <span class="str">&#39;mkdir &#39;</span> + @<span class="kwrd">path</span> + @dbName

        <span class="rem">-- Create the directory</span>
        <span class="kwrd">EXEC</span> master..xp_cmdshell @cmd, NO_OUTPUT

        <span class="kwrd">IF</span> <span class="preproc">@@ERROR</span> &lt;&gt; 0
        <span class="kwrd">BEGIN</span>
            <span class="kwrd">SET</span> @rc = 10
            <span class="kwrd">GOTO</span> EXIT_ROUTINE
        <span class="kwrd">END</span>
    <span class="kwrd">END</span>
    <span class="rem">-- The directory exists, so let&#39;s delete files older than two days</span>
    <span class="kwrd">ELSE</span> <span class="kwrd">IF</span> @retention &lt;&gt; -1
    <span class="kwrd">BEGIN</span>
        <span class="rem">-- Stores the name of the file to be deleted</span>
        <span class="kwrd">DECLARE</span> @whichFile <span class="kwrd">VARCHAR</span>(1000)

        <span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #DeleteOldFiles(DirInfo <span class="kwrd">VARCHAR</span>(7000))

        <span class="rem">-- Build the command that will list out all of the files in a directory</span>
        <span class="kwrd">SELECT</span> @cmd = <span class="str">&#39;dir &#39;</span> + @<span class="kwrd">path</span> + @dbName + <span class="str">&#39; /OD&#39;</span>

        <span class="rem">-- Run the dir command and put the results into a temp table</span>
        <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #DeleteOldFiles
        <span class="kwrd">EXEC</span> master..xp_cmdshell @cmd

        <span class="rem">-- Delete all rows from the temp table except the ones that correspond to the files to be deleted</span>
        <span class="kwrd">DELETE</span> <span class="kwrd">FROM</span> #DeleteOldFiles
        <span class="kwrd">WHERE</span> ISDATE(<span class="kwrd">SUBSTRING</span>(DirInfo, 1, 10)) = 0 <span class="kwrd">OR</span> DirInfo <span class="kwrd">LIKE</span> <span class="str">&#39;%&lt;DIR&gt;%&#39;</span> <span class="kwrd">OR</span> <span class="kwrd">SUBSTRING</span>(DirInfo, 1, 10) &gt;= GETDATE() - @retention

        <span class="rem">-- Get the file name portion of the row that corresponds to the file to be deleted</span>
        <span class="kwrd">SELECT</span> <span class="kwrd">TOP</span> 1 @whichFile = <span class="kwrd">SUBSTRING</span>(DirInfo, LEN(DirInfo) -  PATINDEX(<span class="str">&#39;% %&#39;</span>, REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
        <span class="kwrd">FROM</span> #DeleteOldFiles        

        <span class="kwrd">SET</span> @rowCnt = <span class="preproc">@@ROWCOUNT</span>
        
        <span class="rem">-- Interate through the temp table until there are no more files to delete</span>
        <span class="kwrd">WHILE</span> @rowCnt &lt;&gt; 0
        <span class="kwrd">BEGIN</span>
            <span class="rem">-- Build the del command</span>
            <span class="kwrd">SELECT</span> @cmd = <span class="str">&#39;del &#39;</span> + @<span class="kwrd">path</span> + + @dbName + <span class="str">&#39;\&#39;</span> + @whichFile + <span class="str">&#39; /Q /F&#39;</span>
            
            <span class="rem">-- Delete the file</span>
            <span class="kwrd">EXEC</span> master..xp_cmdshell @cmd, NO_OUTPUT
            
            <span class="rem">-- To move to the next file, the current file name needs to be deleted from the temp table</span>
            <span class="kwrd">DELETE</span> <span class="kwrd">FROM</span> #DeleteOldFiles
            <span class="kwrd">WHERE</span> <span class="kwrd">SUBSTRING</span>(DirInfo, LEN(DirInfo) -  PATINDEX(<span class="str">&#39;% %&#39;</span>, REVERSE(DirInfo)) + 2, LEN(DirInfo))  = @whichFile

            <span class="rem">-- Get the file name portion of the row that corresponds to the file to be deleted</span>
            <span class="kwrd">SELECT</span> <span class="kwrd">TOP</span> 1 @whichFile = <span class="kwrd">SUBSTRING</span>(DirInfo, LEN(DirInfo) -  PATINDEX(<span class="str">&#39;% %&#39;</span>, REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
            <span class="kwrd">FROM</span> #DeleteOldFiles
        
            <span class="kwrd">SET</span> @rowCnt = <span class="preproc">@@ROWCOUNT</span>
        <span class="kwrd">END</span>
        <span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> #DeleteOldFiles
    <span class="kwrd">END</span>
    <span class="rem">-- Get the current date using style 120, remove all dashes, spaces, and colons</span>
    <span class="kwrd">SET</span> @now = REPLACE(REPLACE(REPLACE(<span class="kwrd">CONVERT</span>(<span class="kwrd">VARCHAR</span>(50), GETDATE(), 120), <span class="str">&#39;-&#39;</span>, <span class="str">&#39;&#39;</span>), <span class="str">&#39; &#39;</span>, <span class="str">&#39;&#39;</span>), <span class="str">&#39;:&#39;</span>, <span class="str">&#39;&#39;</span>)

    <span class="kwrd">SET</span> @extension =
        <span class="kwrd">CASE</span>
            <span class="kwrd">WHEN</span> @bkpType = <span class="str">&#39;Full&#39;</span> <span class="kwrd">THEN</span> <span class="str">&#39;.BAK&#39;</span>
            <span class="kwrd">WHEN</span> @bkpType = <span class="str">&#39;TLog&#39;</span> <span class="kwrd">THEN</span> <span class="str">&#39;.TRN&#39;</span>
            <span class="kwrd">ELSE</span> <span class="str">&#39;.DIF&#39;</span>
        <span class="kwrd">END</span>

    <span class="rem">-- Build the backup path and file name, backup the database</span>
    <span class="kwrd">IF</span> @liteSpeed = <span class="str">&#39;N&#39;</span>
    <span class="kwrd">BEGIN</span>
        <span class="kwrd">SET</span> @fileName = @<span class="kwrd">path</span> + @dbName + <span class="str">&#39;\&#39;</span> + @dbName + <span class="str">&#39;_&#39;</span> + @now + @extension
        <span class="kwrd">IF</span> @bkpType = <span class="str">&#39;FULL&#39;</span>
            <span class="kwrd">BACKUP</span> <span class="kwrd">DATABASE</span> @dbName
            <span class="kwrd">TO</span> <span class="kwrd">DISK</span> = @filename
            <span class="kwrd">WITH</span> INIT
        <span class="kwrd">ELSE</span> <span class="kwrd">IF</span> @bkpType = <span class="str">&#39;DIFF&#39;</span>
            <span class="kwrd">BACKUP</span> <span class="kwrd">DATABASE</span> @dbName
            <span class="kwrd">TO</span> <span class="kwrd">DISK</span> = @filename
            <span class="kwrd">WITH</span> INIT, DIFFERENTIAL
        <span class="kwrd">ELSE</span>
            <span class="kwrd">BACKUP</span> LOG @dbName
            <span class="kwrd">TO</span> <span class="kwrd">DISK</span> = @filename
            <span class="kwrd">WITH</span> INIT    
    <span class="kwrd">END</span>
    <span class="kwrd">ELSE</span>
    <span class="kwrd">BEGIN</span>
        <span class="kwrd">SET</span> @fileName = @<span class="kwrd">path</span> + @dbName + <span class="str">&#39;\&#39;</span> + @dbName + <span class="str">&#39;_LS_&#39;</span> + @now + @extension

        <span class="kwrd">DECLARE</span> @numProcs <span class="kwrd">INT</span> <span class="rem">-- stores the number of processors that the server has registered</span>

        <span class="rem">-- Get the number of processors that the server has</span>
        <span class="kwrd">EXEC</span> master..xp_regread 
          @rootkey = <span class="str">&#39;HKEY_LOCAL_MACHINE&#39;</span>, 
          @<span class="kwrd">key</span> = <span class="str">&#39;SYSTEM\CurrentControlSet\Control\Session Manager&#39;</span>,
          @value_name = <span class="str">&#39;RegisteredProcessors&#39;</span>,
          @<span class="kwrd">value</span> = @numProcs <span class="kwrd">OUTPUT</span>
        
        <span class="rem">--  We want n - 1 threads, where n is the number of processors</span>
        <span class="kwrd">SET</span> @numProcs = @numProcs - 1

        <span class="kwrd">IF</span> @bkpType = <span class="str">&#39;FULL&#39;</span>
            <span class="kwrd">EXEC</span> master.dbo.xp_backup_database
                @<span class="kwrd">database</span> = @dbName,
                @filename = @fileName,
                @threads = @numProcs,
                @init = 1
        <span class="kwrd">ELSE</span> <span class="kwrd">IF</span> @bkpType = <span class="str">&#39;DIFF&#39;</span>
            <span class="kwrd">EXEC</span> master.dbo.xp_backup_database
                @<span class="kwrd">database</span> = @dbName,
                @filename = @fileName,
                @threads = @numProcs,
                @init = 1,
                @<span class="kwrd">with</span> = <span class="str">&#39;DIFFERENTIAL&#39;</span>
        <span class="kwrd">ELSE</span>
            <span class="kwrd">EXEC</span> master.dbo.xp_backup_log
                @<span class="kwrd">database</span> = @dbName,
                @filename = @fileName,
                @threads = @numProcs,
                @init = 1
    <span class="kwrd">END</span>
<span class="kwrd">END</span>
    <span class="rem">-- To move onto the next database, the current database name needs to be deleted from the temp table</span>
    <span class="kwrd">DELETE</span> <span class="kwrd">FROM</span> #WhichDatabase
    <span class="kwrd">WHERE</span> dbName = @dbName

    <span class="rem">-- Get the database to be backed up</span>
    <span class="kwrd">SELECT</span> <span class="kwrd">TOP</span> 1 @dbName = dbName
    <span class="kwrd">FROM</span> #WhichDatabase

    <span class="kwrd">SET</span> @rowCnt = <span class="preproc">@@ROWCOUNT</span>
    
    <span class="rem">-- Let the system rest for 5 seconds before starting on the next backup</span>
    <span class="kwrd">WAITFOR</span> DELAY <span class="str">&#39;00:00:05&#39;</span>

END

SET @rc = 0

EXIT_ROUTINE:

IF @rc <> 0 BEGIN DECLARE @rm varchar(500) DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))

<span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> @error(returnCode, returnMessage)
<span class="kwrd">SELECT</span>  0, <span class="str">&#39;Success&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  1, <span class="str">&#39;Version is not 2000 or 2005&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  2, <span class="str">&#39;Invalid option passed to @dbType&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  3, <span class="str">&#39;Database passed to @dbType does not exist&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  4, <span class="str">&#39;Invalid option passed to @bkpType&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  5, <span class="str">&#39;Only full backups are allowed on system databases&#39;</span>
<span class="kwrd">SELECT</span>  6, <span class="str">&#39;Invalid option passed to @liteSpeed&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  7, <span class="str">&#39;Can not backup tlog when using SIMPLE recovery model&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  8, <span class="str">&#39;Will not backup the tlog on a log shipped database&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span>  9, <span class="str">&#39;No databases to be backed up&#39;</span> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span>
<span class="kwrd">SELECT</span> 10, <span class="str">&#39;Unable to create directory&#39;</span>

<span class="kwrd">SELECT</span> @rm = returnMessage 
<span class="kwrd">FROM</span> @error 
<span class="kwrd">WHERE</span> returnCode = @rc

<span class="kwrd">RAISERROR</span>(@rm, 16, 1)

END

RETURN @rc

Legacy Comments


Waterski
2007-02-28
re: Backup databases stored procedure - new version
When I try to use this to create the proceedure in a maint database that I use I get the errors:
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.xp_backup_database'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.xp_backup_log'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

Tara
2007-02-28
re: Backup databases stored procedure - new version
Waterski, those aren't errors but rather warnings. The stored procedure was still successfully created and thus can be run.

Scott
2007-03-15
re: Backup databases stored procedure - new version
I get the following error when try to pass in a specific database.
Database passed to @dbType does not exist [SQLSTATE 42000] (Error 50000). The step failed.
My execution procedure is:
EXEC isp_Backup
@path = '\\PLC2\SQL\NT20 4DL',
@dbType = '-plc',
@bkpType = 'Full',
@retention = 5,
@liteSpeed = 'Y'

SQL 2000 & LiteSpeed 2005 Version 4.0.3.1

??
Thanks
Scott

Tara
2007-03-15
re: Backup databases stored procedure - new version
Scott, I've got a bug in my code. I'm not sure when it stopped working as this stored procedure was thoroughly tested. I must've introduced bad code into it after I did my testing and didn't test this part out again. I don't know though!

I'll have it fixed shortly.

Tara
2007-03-15
re: Backup databases stored procedure - new version
Scott,

I've fixed the code. Try it out and let me know if you still have problems with it.

~Tara Kizer

Scott
2007-03-15
re: Backup databases stored procedure - new version
That works great now.
Thanks
Scott

Scott
2007-03-16
re: Backup databases stored procedure - new version
I have setup 3 jobs using this procedure, one each for Full, TLog and Diff. I want to retain my Full backups for 5 days and my TLog and Diff only for a day.

I haven't been running this long enough to test but by the looks of the code as soon as a TLog or Diff runs my previous days, or older, full backup will also be deleted. ?
Noting that you are using different suffixes on the file names for the different backup types shouldn't the retention filter only on that file type for which the procedure was run?

Probably asking for something that you don't need but it would be nice.
Thanks
Scott

Tejal
2007-03-26
re: Backup databases stored procedure - new version
Tara,

How can i set the retention period to 12hrs please. What happens is that sometimes the backup overruns and then fails to delete old files.

Tara
2007-03-26
re: Backup databases stored procedure - new version
Tejal, setting the retention period to less than 1 day doesn't make sense to me. Do you have these files being swept to tape that often anyway? If you don't, then you should keep the files on disk until they are copied to tape.

Tejal
2007-03-27
re: Backup databases stored procedure - new version
Tara,

Thank you for your reply, yes the files get swept away to a storage device.

Is there a way to set the retention period to less than a day as i keep getting the file incremented and this is no good to us.

Tara
2007-03-27
re: Backup databases stored procedure - new version
Tejal, you will need to modify the code if you want to set the retention period to less than a day.

Coolerbob
2007-04-04
re: Backup databases stored procedure - new version
Msg 3202, Level 16, State 2, Procedure isp_Backup, Line 340
Write on "C:\mybu_20070404155829.BAK" failed: 112(error not found)
Msg 3013, Level 16, State 1, Procedure isp_Backup, Line 340
BACKUP DATABASE is terminating abnormally.

Any ideas?

Coolerbob
2007-04-04
re: Backup databases stored procedure - new version
Problem was it was still at version 70. Silly me

Coolerbob
2007-04-04
re: Backup databases stored procedure - new version
Actually, that didn't fix it...

Tara
2007-04-04
re: Backup databases stored procedure - new version
Run the same backup command that the stored procedure would run in Query Analyzer/Management Studio. It should fail there too as the stored procedure isn't doing anything fancy.

Coolerbob
2007-04-04
re: Backup databases stored procedure - new version
That's where I ran it, didn't work. Found this though, it worked quite well: http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

Tara
2007-04-04
re: Backup databases stored procedure - new version
You missed my point about what to run. Don't run isp_Backup in Query Analyzer/Management Studio. Run the same backup database or backup log command that it would run to see if the problem is outside of the stored procedure.

I don't understand how Bill's application helped you out with backing up a database, but okay.

Coolerbob
2007-04-05
re: Backup databases stored procedure - new version
I'll try that, all I actually needed was to save all the SProcs to a file that I can search. That's why it helped.

Tara
2007-04-05
re: Backup databases stored procedure - new version
Then you definitely don't need my stored procedure. My stored procedure is for taking a snapshot of the database, not for saving individual objects.

To do what you want, Bill's tool works great. You can also use the scripter inside Enterprise Manager or Management Studio (depending on the version of SQL Server).

Magnus
2007-04-20
re: Backup databases stored procedure - new version
Coolerbob wrote:
"Write on "C:\mybu_20070404155829.BAK" failed: 112(error not found)"

Error 112 means "There is not enough space on the disk."

Tara
2007-04-20
re: Backup databases stored procedure - new version
Magnus,

You don't have enough free disk space on your C drive to backup your mybu database. So you don't need to free up some space first.

Hitesh Shah
2007-05-25
re: Backup databases stored procedure - new version
Running this isp_backup procedure , get this error .

Server: Msg 2715, Level 16, State 7, Procedure isp_Backup, Line 106
Column or parameter #1: Cannot find data type SYSNAME.

What can I do .

Tara
2007-05-25
re: Backup databases stored procedure - new version
sysname is a valid SQL Server data type. I believe it's the same as nvarchar(256).

What version of SQL Server are you using?