Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Backup Stored Proc Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2007 8:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:13 PM
Points: 2,665, Visits: 840
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
Post #424398
Posted Thursday, January 17, 2008 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:41 AM
Points: 7, Visits: 66
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.



Post #444181
Posted Friday, February 29, 2008 5:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 1, 2009 7:56 AM
Points: 1, Visits: 10
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
Post #462742
Posted Friday, March 28, 2008 5:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 22, 2010 7:45 AM
Points: 7, Visits: 37
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
Post #476012
Posted Tuesday, July 8, 2008 2:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 7, 2013 9:29 AM
Points: 409, Visits: 421
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
Post #529819
Posted Tuesday, August 12, 2008 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 10:52 AM
Points: 1, Visits: 36
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?
Post #551295
Posted Monday, August 18, 2008 2:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:13 PM
Points: 2,665, Visits: 840
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:

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



2005:

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




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
Post #554598
Posted Monday, August 18, 2008 2:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:13 PM
Points: 2,665, Visits: 840
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? 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
Post #554604
Posted Monday, August 18, 2008 2:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:13 PM
Points: 2,665, Visits: 840
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
Post #554605
Posted Monday, August 18, 2008 2:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:13 PM
Points: 2,665, Visits: 840
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:

SELECT @cmd = 'msdb..sp_purge_jobhistory @oldest_date=''' + CAST(@dt AS varchar(40)) + ''''
EXEC (@cmd)


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
Post #554609
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse