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