Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 52 week backup cycle...

Author  Topic 

methodology
Starting Member

31 Posts

Posted - 2007-09-13 : 09:51:53
Hi

uh

im on the blag....

Can someone help me with a script for the following?

7am tuesday, week 1 of the year:
full backup, Trans log backups from between 8am and 10pm and nightly diff @ 10.15pm. both TL backups and Diff backups append to the full file. the next day - weds - exactly the same only without the initialising full backup @ 7am. each day appends to the same weeks backup. this goes all the way through to 10.15pm Monday night which is the last activity on the particular backup file.

7am Tuesday, week 2 of the year:
exactly the same process but writing to a new backup file. backup file is named something useful, like with the date of the Tuesday for example..

ill manually delete things as I need to (once that months data has been written to End-Of-Month tape), but otherwise i'd ideally end up with 52 backup files, each containing a weeks worth of data.

help me obi-wan SQLee. cos I cant bloody do it....

Thanks

Alastair
Methodology



"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 09:59:43
http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.html

PS: If anyone is interested in a SQL Server Job in Connecticut with excellent pay submit your resume to ValterBorges@msn.com
Go to Top of Page

methodology
Starting Member

31 Posts

Posted - 2007-09-13 : 10:03:16
um

and how do I edit it to just backup ONE database?

sorry - my tsql is as good as my lunar roller hover blade-ing.

ta

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 12:54:52
"both TL backups and Diff backups append to the full file"

I strongly recommend you don't ever append backups to a file; and file corruption will render the whole lot un-recoverable, and any accidental INIT command will wipe out all the previous backups. Save them in individual uniquely named files (e.g. using a filename comprising DatabaseName + Date + Time.)

You can then delete all files with a date-timestamp earlier than, say, a year ago.

You could put each week/month in a separate folder, for ease of housekeeping. But you do need to copy them to Tape or somesuch, otherwise any hardware failure on the server is going to mean you have no where to recover from.

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-13 : 13:43:54
You can do everything you need in a maintanance plan.

As Kristen suggested, there is no good reason to append to a backup file.

I'm not sure why you would want to save 52 weeks of full, differential, and transaction log backups to disk, but there is no reason why you can't do it if you have enough disk space.



CODO ERGO SUM
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 13:56:27
wouldn't it be nice if the backup command has a split function?
backup to device AND to disk...

I just find managing and restoring from a device is much easier where you don't have to worry lost trns or lsns

--------------------
keeping it simple...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-13 : 14:08:41
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89261

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -