November 20, 2007 at 8:00 pm
Comments posted to this topic are about the item Backup Stored Proc
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
January 17, 2008 at 1:43 am
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.
February 29, 2008 at 5:56 pm
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
March 28, 2008 at 5:47 am
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
July 8, 2008 at 2:17 am
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
August 12, 2008 at 11:59 am
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?
August 18, 2008 at 2:30 pm
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]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 2:40 pm
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.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 2:43 pm
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.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 2:49 pm
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.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 2:51 pm
Jonathan.Sims (8/12/2008)
Great SP ScottBut 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.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 9:39 pm
Here's a revised version of the script. Now handles long folder/file names, checks server version is 2000 or 2005.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 20, 2008 at 5:03 pm
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.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
September 2, 2008 at 7:02 am
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
September 3, 2008 at 5:44 pm
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.
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