Backup Stored Proc

  • Comments posted to this topic are about the item Backup Stored Proc



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Very nice backup script ,

    it will be nicer if it will be wrapped in a cursor or some kind of a loop that will backup all the Database in the instance not just specified database.

    best regard Amit Hollenberg.

  • Hey Scott,

    I receive Server: Msg 8145, Level 16, State 2, Procedure sp_purge_jobhistory, Line 0 @oldest_date is not a parameter for procedure sp_purge_jobhistory when trying to run the sproc. Being a noob, I've no idea whats wrong:)

    Any pointer?

    Thanks

  • Scott, this is a great backup sp. Would you happen to have a matching restore sp that will pick up the latest backup file?

    Thanks

    Tom

  • Hi,

    Its a great script. We use this stored proc to loop through all the databases in another stored procedure.

    I have used like this

    SP_MSFOREACHDB 'EXECUTE DBO.USP_DBBackup ''full'',''?'',''E:\sqlbackups'',''1'',1'

    My suggestion are.

    1. The stored proc needs to check the avaibility of the databases(online, offline, recovering,etc..)

    2. Check if the database is involved in logshipping. Otherwise we may break the lsn continuity by taking log backups.

    3. We can make it general script for 2000 and 2005 by adding IF ELSE Blocks for 2000 and 2005

    I would modify the stored proc IF @@VERSION LIKE 'Microsoft SQL Server 2000%'

    But overall its a great script.

    Thanks and regards,

    Vee

  • Great SP Scott

    But i do have one question, every so often my msdb databases is skipped with this backup, have you (or anyone) ran into this issue before?

  • tdepalo (3/28/2008)


    Scott, this is a great backup sp. Would you happen to have a matching restore sp that will pick up the latest backup file?

    Thanks

    Tom

    No, but that's just because I haven't had to do it. I do have some code that determines when the latest backup was and what the path+filename is (used in a documentation script I have). It wouldn't be too hard to wrap a restore command around it.

    The question would be what exactly you would want to see in such a proc. It would be easy enough to write a proc that does a forced restore over the top of the existing database, but is that what you are after?

    Here's the code for determining last backup date & location (note - only tested with disk backups, not tape). There's SQL 2000 & SQL 2005 code (I don't think there's a lot of difference, other than I've probably refined the 2005 version a little more). Bear in mind it was pulled out of a script that outputs HTML-compatible text, so some of the formatting will be redundant (using LEN, RTRIM et al).

    2000:

    [font="Courier New"]DECLARE @sql nvarchar(4000),

    @maxlen1 smallint,

    @maxlen2 smallint,

    @maxlen3 smallint

    IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmp_backups%')

    DROP TABLE #tmp_backups

    CREATE TABLE #tmp_backups

    (

    name sysname,

    backupstartdate char(11),

    comment varchar(50),

    location nvarchar(260)

    )

    -- Last full backups

    INSERT INTO #tmp_backups

    SELECT s.name,

    CAST(b.backup_finish_date AS char(11)),

    CASE WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

    THEN 'Last full backup was within the last 24 hours'

    WHEN b.backup_finish_date > DATEADD(dd,-7,getdate())

    THEN 'Last full backup was within the last week'

    ELSE '***** CHECK BACKUP!!! *****'

    END,

    bmf.physical_device_name

    FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

    INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

    WHERE s.name <> 'tempdb'

    AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

    FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type = 'D') -- full database backups only, not log backups

    ORDER BY s.name

    SELECT @maxlen1 = COALESCE((MAX(LEN(RTRIM(name))) + 2), 1)

    FROM #tmp_backups

    SELECT @maxlen2 = COALESCE((MAX(LEN(RTRIM(comment))) + 2), 1)

    FROM #tmp_backups

    SELECT @maxlen3 = COALESCE((MAX(LEN(RTRIM(location))) + 2), 1)

    FROM #tmp_backups

    SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

    SET @sql = @sql + 'backupstartdate AS ''Backup Date'', '

    SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

    SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

    SET @sql = @sql + 'FROM #tmp_backups '

    SET @sql = @sql + 'ORDER BY name'

    EXEC (@sql)

    TRUNCATE TABLE #tmp_backups

    -- Last log backups

    INSERT INTO #tmp_backups

    SELECT s.name,

    CAST(b.backup_finish_date AS char(11)),

    CASE WHEN b.backup_finish_date > DATEADD(hh,-12,getdate())

    THEN 'Last log backup was within the last 12 hours'

    WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

    THEN 'Last log backup was within the last day'

    ELSE '***** CHECK BACKUP!!! *****'

    END,

    bmf.physical_device_name

    FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

    INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

    WHERE s.name <> 'tempdb'

    AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

    FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type = 'L') -- log backups only, not full backups

    ORDER BY s.name

    SELECT @maxlen1 = COALESCE((MAX(LEN(RTRIM(name))) + 2), 1)

    FROM #tmp_backups

    SELECT @maxlen2 = COALESCE((MAX(LEN(RTRIM(comment))) + 2), 1)

    FROM #tmp_backups

    SELECT @maxlen3 = COALESCE((MAX(LEN(RTRIM(location))) + 2), 1)

    FROM #tmp_backups

    SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

    SET @sql = @sql + 'backupstartdate AS ''Backup Date'', '

    SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

    SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

    SET @sql = @sql + 'FROM #tmp_backups '

    SET @sql = @sql + 'ORDER BY name'

    EXEC (@sql)

    DROP TABLE #tmp_backups[/font]

    2005:

    [font="Courier New"]DECLARE @sql nvarchar(4000),

    @maxlen1 smallint,

    @maxlen2 smallint,

    @maxlen3 smallint

    IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmp_backups%')

    DROP TABLE #tmp_backups

    CREATE TABLE #tmp_backups

    (

    name sysname,

    backupfinishdate datetime,

    comment varchar(50),

    location nvarchar(260)

    )

    INSERT INTO #tmp_backups

    SELECT s.name,

    b.backup_finish_date,

    CASE WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

    THEN 'Last full backup was within the last 24 hours'

    WHEN b.backup_finish_date > DATEADD(dd,-7,getdate())

    THEN 'Last full backup was within the last week'

    ELSE '***** CHECK BACKUP!!! *****'

    END,

    bmf.physical_device_name

    FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

    INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

    WHERE s.name <> 'tempdb'

    AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

    FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type = 'D') -- full database backups only, not log backups

    ORDER BY s.name

    SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)

    FROM #tmp_backups

    SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

    SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '

    SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

    SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

    SET @sql = @sql + 'FROM #tmp_backups '

    SET @sql = @sql + 'ORDER BY name'

    EXEC (@sql)

    TRUNCATE TABLE #tmp_backups

    -- Last log backups

    INSERT INTO #tmp_backups

    SELECT s.name,

    b.backup_finish_date,

    CASE WHEN b.backup_finish_date > DATEADD(hh,-12,getdate())

    THEN 'Last log backup was within the last 12 hours'

    WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

    THEN 'Last log backup was within the last day'

    ELSE '***** CHECK BACKUP!!! *****'

    END,

    bmf.physical_device_name

    FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

    INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

    WHERE s.name <> 'tempdb'

    AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

    FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type = 'L') -- log database backups only, not full backups

    ORDER BY s.name

    SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)

    FROM #tmp_backups

    SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

    SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '

    SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

    SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

    SET @sql = @sql + 'FROM #tmp_backups '

    SET @sql = @sql + 'ORDER BY name'

    EXEC (@sql)

    DROP TABLE #tmp_backups

    [/font]



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Vee (7/8/2008)


    Hi,

    Its a great script. We use this stored proc to loop through all the databases in another stored procedure.

    I have used like this

    SP_MSFOREACHDB 'EXECUTE DBO.USP_DBBackup ''full'',''?'',''E:\sqlbackups'',''1'',1'

    My suggestion are.

    1. The stored proc needs to check the avaibility of the databases(online, offline, recovering,etc..)

    2. Check if the database is involved in logshipping. Otherwise we may break the lsn continuity by taking log backups.

    3. We can make it general script for 2000 and 2005 by adding IF ELSE Blocks for 2000 and 2005

    I would modify the stored proc IF @@VERSION LIKE 'Microsoft SQL Server 2000%'

    But overall its a great script.

    Thanks and regards,

    Vee

    I hadn't thought about using sp_MSforeachdb with it. Glad it works!

    Thanks for the suggestions.

    For point 1, it checks that the database is online in the section where it checks for a database name, then whether the database exists then whether it is online. Any other status returned by DATABASEPROPERTYEX means the database is not in a state to be backed up.

    Good point with number 2, but the same point could be made about any log backup performed on the database. I'm not sure what you would want to see in the script in this regard - don't do a log backup if it is in log shipping? What if you want to use this proc to do the log backups? It would be just as easy to just not use the proc in the first place if that was the case.

    As for number 3, why didn't I think of that? :pinch: I will look into it, will probably use one of the SERVERPROPERTY properties.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • amit hollenberg (1/17/2008)


    Very nice backup script ,

    it will be nicer if it will be wrapped in a cursor or some kind of a loop that will backup all the Database in the instance not just specified database.

    best regard Amit Hollenberg.

    See Vee's post - you can use sp_MSforeachdb. Note - that is an undocumented system stored procedure, so may not be around in future versions of SQL (so sayeth Microsoft).

    The reason I did it this way was so you could have granularity - if you only wanted to backup a few of the databases, you could, rather than all or nothing. It wouldn't be too hard to add that functionality though, so I'll look into it.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • JCasale (2/29/2008)


    Hey Scott,

    I receive Server: Msg 8145, Level 16, State 2, Procedure sp_purge_jobhistory, Line 0 @oldest_date is not a parameter for procedure sp_purge_jobhistory when trying to run the sproc. Being a noob, I've no idea whats wrong:)

    Any pointer?

    Thanks

    If you're running it on SQL 2000, there's an error I didn't pick up on. Remove these lines from the 'tidy up msdb tables' section:

    [font="Courier New"]SELECT @cmd = 'msdb..sp_purge_jobhistory @oldest_date=''' + CAST(@dt AS varchar(40)) + ''''

    EXEC (@cmd)

    [/font]

    That parameter is only valid for SQL 2005.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Jonathan.Sims (8/12/2008)


    Great SP Scott

    But i do have one question, every so often my msdb databases is skipped with this backup, have you (or anyone) ran into this issue before?

    I've replied to this privately, but for other forum users: no, I haven't come across this behaviour before.

    Edit: But Jonathan & I are trying to find the cause of it.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Here's a revised version of the script. Now handles long folder/file names, checks server version is 2000 or 2005.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Another revision. SQL version checking (will only run on SQL 2000, 2005), better logging, additional options.

    Set Database name to:

    ALLSYS = backs up master, model, msdb

    ALLUSER = backs up all (online) user databases (excludes master, model, msdb, tempdb)

    ALLDATABASES = backs up all (online) databases (excludes tempdb)

    or just specify an individual database name.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • I tried to use this script and i found you that if the msdb..backupfile is large this scripts just hanges up.

    it does the backup but never finishes deleting the old backuphistory

    :w00t:

    best regards Ingvi Jón

  • This is a known issue with MSDB - there are some indexes missing that would improve performance. There is an article in the forums somewhere (search for "large msdb" and you should come across it) which boils down to this:

    To improve performance of sp_delete_backuphistory (i.e., there are tens of thousands of rows in each of the backupset, restorefile & restorefilegroup tables), create the following indexes:

    CREATE NONCLUSTERED INDEX ix_mediasetid ON backupset(media_set_id)

    GO

    CREATE NONCLUSTERED INDEX ix_restorehistoryid ON restorefile(restore_history_id)

    GO

    CREATE NONCLUSTERED INDEX ix_restorehistoryid2 ON restorefilegroup(restore_history_id)

    GO

    Creating the indexes above sped up the deletion process considerably - from ~60-100 rows per minute (speed improved as the tables got smaller) to 9600 rows per minute.

    Run DBCC UPDATEUSAGE(0) WITH COUNT_ROWS to update table usage by counting every row.

    Run sp_updatestats 'resample' when done.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply