Dynamic Database Backup

Question

Dynamic Database Backup

mitch 14804

SSC Veteran

Points: 298
More actions
November 20, 2012 at 9:09 pm

#265138

Comments posted to this topic are about the item Dynamic Database Backup

Viewing 7 posts - 1 through 6 (of 6 total)

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 1

Great script. Thanks for posting this. Often backups don't get done because it's too hard. This will help a lot. Folks should put this in place and get it running. You almost can't have enough backups

There are a couple of things that I spotted though.

SET @RowCount = (select COUNT(*) from @DBTable)

This is before the code that actually inserts rows into the table so always comes up zero. Move this down and it works.

You are doing FULL backups. This is fine as long as it is understoood and LOG backups follow. For this kind of script I would add the COPY ONLY.

Thanks again. Good work.

ATBCharles Kincaid

mitch 14804 SSC Veteran Points: 298 More actions · Answer 2

Thanks Charles for your comments. I'm not sure how that line of code got out of place. Thanks for posting that correction..

Yes i know this is a full backup as this is what I wanted. There is also another posting in SSC for Dynamic Logfile Backups which is just like this one except for log files.

I run nightly backups with this procedure and hourly log file backups with the other procedure.

mitch

monpara.sanjay SSC Enthusiast Points: 110 More actions · Answer 3

monpara.sanjay

SSC Enthusiast

Points: 110

November 21, 2012 at 11:42 pm

#1561494

Its very helpful script, Thanks

rjoseph Ten Centuries Points: 1087 More actions · Answer 4

Hello Mitch,

Pardon my lack of knowledge. I would like to use this script but I don't know how to put it to work for me. I am no DBA but trying to get around. Would you kind enough to let know how to use or which are the parameters that I need to exec it.

Thanks very much

mitch 14804 SSC Veteran Points: 298 More actions · Answer 5

rjoseph

To use this procedure one must set a few things up first.

1) Set the correct settings for:

----------------------------------------------------------------------------------------------------------------------------------------------------

----- Must be set based on individual needs

----------------------------------------------------------------------------------------------------------------------------------------------------

SET @BackupRoot = N'Q:' -- everything is below this point. (this is the target drive) (Change the Q: to your Target Drive

SET @Recipients = 'sqldba@somecompany.com' (email address to send notifications)

SET @Profile_name = 'SQL Backup Mail' (db mail profile) (can comment out all ref to mail if it's not needed)

2) On bug was discovered and needs to be fixed.

Located this line

SET @RowCount = (select COUNT(*) from @DBTable)

and move to a location right after this statement:

insert into @DBTable(DatabaseName)

SELECT name FROM sys.databases

WHERE state = 0 -- online

AND name NOT IN (N'model', N'tempdb') -- exclude these databases

ORDER BY name -- by putting them in order we can track the backup progress on the file system.

3) you can call this procedure without any parameters. You can also set up a SQL Agent Job to start this procedure each night.

Hope this helps and good luck.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

Thanks for the additional info, I missed first go around.