Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup Stored Proc


Backup Stored Proc

Author
Message
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 932
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

amit hollenberg
amit hollenberg
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 70
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.



JCasale
JCasale
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 15
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 wrongSmile

Any pointer?
Thanks
tdepalo
tdepalo
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 38
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
Vee
Vee
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 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
Jonathan.Sims
Jonathan.Sims
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 932
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

thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 932
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

thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 932
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

thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 932
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 wrongSmile

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search