June 22, 2009 at 12:21 am
Hi,
I want to take weekly backup of my database. Full database backup is required to be taken. i have created a job which takes backup on disk in a particular file.
I want the old file should b deleted and a new bakup file should replace the existing file.But for now the backup file on the disk is growing in size after every backup...
what should b done to keep only latest data..
Regards,
Abhay
June 22, 2009 at 12:35 am
You can use maintenance plan to delete old backups.
But since you are appending backups to a single file, this may not be possible. While taking backups, select option to create separate files for each backups.
June 22, 2009 at 1:06 am
Abhay (6/22/2009)
I want to take weekly backup of my database. Full database backup is required to be taken. i have created a job which takes backup on disk in a particular file.I want the old file should b deleted and a new bakup file should replace the existing file.
Use with INIT clause
Backup database mydb to disk='my path\myfile' WITH INIT.This will overwrite the last backup file (if it exists)
But for now the backup file on the disk is growing in size after every backup...
This happnes because backups are appended to the same file. You can check the contents of a backup file (how many backups are present in that file) by running this query.
restore headeronly from disk='c:\testfull.bak'
There will be multiple backups there, If you need to restore a particular one, u specify WITH FILE=n
where n is the Position Column from the result of the previous query.
June 22, 2009 at 1:59 am
Hi guys..thank u for quick response....i am now using maintenance plan for it..created maintenanace plans for full database backup as well as for transaction log...
Regards,
Abhay
June 22, 2009 at 9:01 am
Full backup just once a week ? If it's production, you could take DIFF backups during the week so that you don't have huge numbers of transaction logs to restore in case of emergency.
June 22, 2009 at 9:14 am
Abhay, as rightly pointed by Homebrew01 in last post, you need to plan you restoration strategy carefully, based on which you should create your backup jobs.
First question that's asked is how much data your company can afford to lose in case of disaster. based on this you make a robust backup/restoration strategy.
June 22, 2009 at 9:19 am
Note that your restore scenarios should look at how often data changes. If you change data often, and can't afford to recreate those changes, you need to back up often enough to prevent issues. I think many companies can afford to recreate an hours worth of changes, but some can't. Some could handle a day. Run your backups according to what you need.
Also, if this is production, learn about the transaction log and make sure you have log backups.
June 22, 2009 at 9:22 am
One more thing, don't delete an old backup (or overwrite it) without having a new backup. I've seen people do this:
- delete old backup
- run new backup
Which one day will end up as
- delete old backup
- server fail, or new backup fail
- update resume
Backups do fail at times. So be sure that if you use WITH INIT, you are using a new file name to prevent overwriting your previous backup.
June 22, 2009 at 11:48 pm
- server fail, or new backup fail
- update resume
lol 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply