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.
| Author |
Topic |
|
theresamarieb
Starting Member
2 Posts |
 Posted - 2004-05-05 : 10:24:55
|
| Hi,I have recently taken over for our previous DBA. I'm a newbie DBA and only administer two servers - a development box and a production box. I took the MS Administering a SQL Server class so I am familiar with backups and restores and have done several restores.Our backup strategy (implemented by the previous DBA) is one nightly backup of each database that is appended to four backup devices (.bak files on a network drive). We are using Simple recovery mode b/c our policy is if a database goes down, we can get you back to the point of the last backup. We don't have a lot of data-intensive production databases so this has worked fine. The code for backing up one of the databases is:BACKUP DATABASE [WCB_EVALS] TO [BACKUP_FILE_FOUR_F], [BACKUP_FILE_ONE_F], [BACKUP_FILE_THREE_F], [BACKUP_FILE_TWO_F] WITH NOINIT, NOUNLOAD , NAME = N'MASTER BACKUP JOB', SKIP ,STATS = 10, NOFORMAT Here is my problem. He has been appending the data to each backup device. It is great when we are trying to restore dbs to a point in time - since the data we deal changes on a semester basis (University) it can be helpful to restore back a semester to see what data looked like at that time (for comparison sake).HOWEVER, each of the .bak files is over 75 GB!!! Meaning that with all four backup devices we have over 300 GB of backups! And they only contain data from the end of February. I remember from my class that you can specify a date for which to keep backups in the .bak file in the BACKUP DATABASE statement. But I would like to keep a running two weeks - always have at least two weeks worth of backups that I can restore from. Can anyone help me? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-05 : 12:14:33
|
| Don't backup to a device, backup to a disk file so that everything is separate.Include the date in the file name e.g. mydb_ful_yyyymmdd_hhmmss.bakYou can then include scrits to delete backups as necessary - often done when the backups are copied off the machine.seehttp://www.nigelrivett.net/BackupAllDatabases.htmlIt give s a method of backing up databases and a retention peeriod for the file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
HUGE Backup files