Backup databases stored procedure - new version

Mon Feb 26, 2007 by Tara Kizer in sql-server-database-administration

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&#39;, @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?