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

I have updated my backup stored procedure, isp_Backup, as the old version had two issues. It did not exclude database snapshots, which can't be backed up. It also was not checking for the number of CPUs in the registry properly. The CPU check only affects backups when SQL Litespeed is used.

In the next version, I will exclude those databases that are being log shipped by SQL Litespeed as we don't want to interfere with the LSNs. I am already checking for this condition for native backups. I will also add code to perform a full backup when it is performing a differential or transaction log backup and a full backup does not exist. This will be done to avoid backup errors.

----------------------------------------------------------------------------------------------------
-- 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) <> '\'
    SET @path = @path + '\'

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
    
    IF @edition = 3
    BEGIN
        IF @version = '8'
        BEGIN
            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
            BEGIN
                SET @rc = 8
                GOTO EXIT_ROUTINE
            END
        END
        ELSE IF @version = '9' 
        BEGIN
            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
            BEGIN
                SET @rc = 8
                GOTO EXIT_ROUTINE
            END
        END
    END

    IF @version = '9'
    BEGIN
        IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)) AND source_database_id IS NOT NULL)
        BEGIN
            SET @rc = 11
            GOTO EXIT_ROUTINE
        END
    END

    INSERT INTO #WhichDatabase(dbName)
    VALUES(SUBSTRING(@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

-- Remove snapshots
IF @version = '9'
    DELETE t
    FROM #WhichDatabase t 
    INNER JOIN master.sys.databases d
    ON t.dbName = d.[name]
    WHERE d.source_database_id IS NOT NULL

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

    IF @bkpType = 'TLog' AND @dbType IN ('All', 'User') AND DATABASEPROPERTYEX(@dbName, 'RECOVERY') = 'SIMPLE'
        PRINT 'Skipping transaction log backup of ' + @dbName
    ELSE IF @bkpType = 'Diff' AND @dbName IN ('master', 'model', 'msdb')
        PRINT 'Skipping differential backup of ' + @dbName
    ELSE
    BEGIN
        -- Build the dir command that will check to see if the directory exists
        SET @cmd = 'dir ' + @path + @dbName
    
        -- Run the dir command, put output of xp_cmdshell into @result
        EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT
    
        -- If the directory does not exist, we must create it
        IF @result <> 0
        BEGIN
            -- Build the mkdir command        
            SET @cmd = 'mkdir ' + @path + @dbName
    
            -- Create the directory
            EXEC master..xp_cmdshell @cmd, NO_OUTPUT
    
            IF @@ERROR <> 0
            BEGIN
                SET @rc = 10
                GOTO EXIT_ROUTINE
            END
        END
        -- The directory exists, so let's delete files older than two days
        ELSE IF @retention <> -1
        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..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() - @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..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
        -- Get the current date using style 120, remove all dashes, spaces, and colons
        SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
    
        SET @extension =
            CASE
                WHEN @bkpType = 'Full' THEN '.BAK'
                WHEN @bkpType = 'TLog' THEN '.TRN'
                ELSE '.DIF'
            END
    
        -- Build the backup path and file name, backup the database
        IF @liteSpeed = 'N'
        BEGIN
            SET @fileName = @path + @dbName + '\' + @dbName + '_' + @now + @extension
            IF @bkpType = 'FULL'
                BACKUP DATABASE @dbName
                TO DISK = @filename
                WITH INIT
            ELSE IF @bkpType = 'DIFF'
                BACKUP DATABASE @dbName
                TO DISK = @filename
                WITH INIT, DIFFERENTIAL
            ELSE
                BACKUP LOG @dbName
                TO DISK = @filename
                WITH INIT    
        END
        ELSE
        BEGIN
            SET @fileName = @path + @dbName + '\' + @dbName + '_LS_' + @now + @extension
    
            DECLARE @regOutput varchar(20) -- stores the output from the registry
            DECLARE @numProcs INT -- stores the number of processors that the server has registered

            -- Get the number of processors that the server has
            EXEC master..xp_regread 
                  @rootkey = 'HKEY_LOCAL_MACHINE', 
                  @key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
                  @value_name = 'NUMBER_OF_PROCESSORS',
                  @value = @regOutput OUTPUT
            
            --  We want n - 1 threads, where n is the number of processors
            SET @numProcs = CONVERT(int, @regOutput) - 1
    
            IF @bkpType = 'FULL'
                EXEC master.dbo.xp_backup_database
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1
            ELSE IF @bkpType = 'DIFF'
                EXEC master.dbo.xp_backup_database
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1,
                    @with = 'DIFFERENTIAL'
            ELSE
                EXEC master.dbo.xp_backup_log
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1
        END
    END
        -- 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

SET @rc = 0

EXIT_ROUTINE:

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

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

    SELECT @rm = returnMessage 
    FROM @error 
    WHERE returnCode = @rc

    RAISERROR(@rm, 16, 1)
END

RETURN @rc

Print | posted on Wednesday, May 21, 2008 10:05 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Backup databases stored procedure - new version

Tara,

This is a nice script that I'm probably going to 'borrow'. One thing is if you can guarentee you are running on a Windows 2003 Server environment, you could shorten the delete files portion by simply sending a FORFILES command via xp_cmdshell and remove that entire looping business in the script.
5/28/2008 9:50 AM | Tony Fountain
Gravatar

# re: Backup databases stored procedure - new version

Hi Tara.

I came accross this site in search of a store proc to backup databases and found your work here (along with a vastness of other gems).

I'm looking to call your proc from VBScript and was wondering (if you have time to spell it out for me):

If I were to leverage your ISP_BACKUP proc....where would I deploy/create it...in Master, tempdb...other db on the server that I can call?

Knowing that I'm just assuming that I can simply call it....

Please advise when you have a moment (oh yah, congrats on becoming a MOM ;-)

Thanks a ton,
-Mark
6/6/2008 10:29 AM | Mark Haggarty
Gravatar

# 1 bug, one feature request

Tara, we've been using your code since a couple versions ago. Absolutely love it.

Mark - you can stick it in any database (except tempdb, since it'll vanish when the SQL Service restarts). We put ours in a "dba" database, along with other maintenance code (like Tara's defrag script).

One request and one bug on the new version:
Bug: On one of my 2000 servers, it doesn't like the log_shipping_databases bit. It chokes; says it can't find it.
Request: Check the results from the Litespeed call. If it fails, for whatever reason, isp_Backup still deletes the old backups. Could you set it to not delete if you get back an error from Litespeed? We've seen it in two different situations, one where Litespeed fails because there's not enough disk space, and one where Litespeed itself is not working correctly.
6/23/2008 7:20 AM | Michael B
Gravatar

# re: Backup databases stored procedure - new version

Michael B,

Please send me the error message. I've got the code running on quite a few servers that do not have LS installed and I do not have any issues on those. You could post the error here or send me an email through my contact page.

For the request part: Yes it is coded to delete the backups regardless of the outcome of the stored procedure. We can't attempt a backup until disk space has been cleaned, otherwise systems that are tight on disk space and only have enough space for the backups will error on the backup command.
6/23/2008 10:09 AM | Tara
Gravatar

# re: Backup databases stored procedure - new version

how we can to use "select" to show the name of tables in spesific database SQL Server 2000,
example like used " select name from master.dbo.sysdatabases" {to show databases}
thank's
7/1/2008 3:25 AM | charles Inter
Gravatar

# re: Backup databases stored procedure - new version

charles,

Use the INFORMATION_SCHEMA views for this.

USE SomeDb
GO

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME
7/1/2008 9:43 AM | Tara
Gravatar

# re: Backup databases stored procedure - new version

Hi tara Its Great, thanks for giving a nice script for backup
7/3/2008 10:42 PM | Rajinikanth
Gravatar

# re: Backup databases stored procedure - new version

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

can you advise?
7/8/2008 2:11 AM | esther
Gravatar

# re: Backup databases stored procedure - new version

esther, those are warnings, not errors. This is normal for systems that do not have SQL Litespeed installed. Please ignore the warnings, the stored procedure will work fine.
7/8/2008 9:04 AM | Tara
Gravatar

# re: Backup databases stored procedure - new version

Teach me not to followup. Three things
1) Feature request - blacklist
3) Error message for log_shipping on 2000 included below.


1) One thing we're adding to ours is the idea of a blacklist. Not every database needs a backup. While you can add each new database to the job, I've found that things get overlooked. So we're adding a blacklist table (we use a "DBA" database to hold your code, other code, etc) that's referenced. Not sure what would be the best way to add it in your code, since it would probably require dynamic sql for certain steps.

2) Another error, fix included - retention affects everything in a folder

The problem is that if you store backups/tlogs/diffs in the same folder, you can accidentally set retention such that when it deletes one, it also deletes the others. So if you keep hourly transaction logs for 1 day in the same folder as the full backups, it'll delete any full backups older than 1 day. The fix is pretty simple, I believe.

You already set the @extension:
SET @extension =
CASE
WHEN @bkpType = 'Full' THEN '.BAK'
WHEN @bkpType = 'TLog' THEN '.TRN'
ELSE '.DIF'
END

Move that earlier in your code, and change the code:

-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @path + @dbName + ' /OD'
becomes
-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @path + @dbName + '\*' + @extension + ' /OD'

Alternatively, you could list it and just not delete it. This looks a little cleaner, and the performance hit would be minimal.
-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
becomes
-- Delete the file if the proper type and outside @retention
If right(@whichfile,4) = @extension
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

The error message:
Msg 208, Level 16, State 1, Procedure isp_Backup, Line 119
Invalid object name 'msdb.dbo.log_shipping_databases'.

Line 119 corresponds with:
IF @version = '8'
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 8
GOTO EXIT_ROUTINE
END
END

7/21/2008 8:48 AM | Michael B
Gravatar

# re: Backup databases stored procedure - new version

While I hate seeing them this way...database names can have spaces. When they do, the script breaks when checking/creating the folders. The fix is to enclose the name in double quotes when issuing the dir and mkdir commands.

For example,

SET @cmd = 'dir ' + @path + @dbName

becomes

SET @cmd = 'dir "' + @path + @dbName + '"'
7/22/2008 7:23 AM | Jason C
Gravatar

# re: Backup databases stored procedure - new version

Jason,

I'm aware of the issue and should have a new version with this fix released in a couple of weeks. I'm fixing a bunch of things and adding new features in this next release.
7/22/2008 9:23 AM | Tara
Gravatar

# re: Backup databases stored procedure - new version

I keep getting this error:
Msg 2812, Level 16, State 62, Procedure isp_Backup, Line 386
Could not find stored procedure 'master.dbo.xp_backup_database'.

I thought that it would work without LightSpeed installed. Any help would be greatly appreciated!
8/5/2008 7:01 PM | Trey
Gravatar

# re: Backup databases stored procedure - new version

Trey, yes it works without Litespeed. But you are the second person to report this. It is only supposed to show a warning when LS is not present, not an error. Could you show me the output of SELECT @@VERSION?

Also, what is the compatibility level of the database where you tried to create the object?
8/5/2008 8:18 PM | Tara
Gravatar

# re: Backup databases stored procedure - new version

I figured it out...stupid on my part, I had LiteSpeed= Y

Do you know of any posts where you can specify specific DBs to backup instead of all user? We have too much data and not enough time...Thanks for your help!
8/6/2008 12:46 PM | Trey
Gravatar

# re: Backup databases stored procedure - new version

Trey,

I'm glad it turned out just to be a parameter value issue.

My script works for specific databases. Just pass "-dbName" to @dbType input parameter. So that's dashDatabaseName to be more clear.
8/6/2008 1:07 PM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET