Backup only newest database.

Question

Backup only newest database.

Jeff Sims-413169

Say Hey Kid

Points: 711
More actions
March 7, 2012 at 9:12 am

#252054

Have an app that creates databases about once every other day a new DB will be created. Once a new Db is created the old DB's are kept for 60 days and are not changed. Is there some way to program a backup to only backup the newest DB so I dont have to backup all the DB's every night? The name of the newest will change and count up. EX: WSlogdb64, wslogdb65. No the numbers should count up in increments of one but that is also not a guarantee as currently I have a mismatch of #'s. Any help or point in right direction would be appreciated,
Thanks,
Jeff

Viewing 5 posts - 1 through 4 (of 4 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I would record database names in a table and then backup only the databases that are not in that table.

Another possible solution: since you don't want database backups for existing databases, I suppose they don't get modified, so you could take a backup of read/write databases and then set all databases read only.

-- Gianluca Sartori

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

You can check if your database ever was backed-up:

SELECT Database_Name,

CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup

FROM MSDB.dbo.BackupSet

WHERE Type = 'd'

GROUP BY Database_Name

ORDER BY Database_Name

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Gianluca Sartori (3/7/2012)
Another possible solution: since you don't want database backups for existing databases, I suppose they don't get modified, so you could take a backup of read/write databases and then set all databases read only.

I believe that would be the perfect solution for two reasons.

1) It would be easy to write the script to do this.

2) It will prevent updates to databases that aren't being backed up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Sims-413169 Say Hey Kid Points: 711 More actions · Answer 4

Thanks, I like the read, read/write solution as the old DB are for reporting only so no writes. I will work on getting this up and working.. Thanks.