July 5, 2011 at 5:16 am
Hi folks,
I need to create a backup plan for my database, it should be in weekly basis of full backup.
So there would be 4 version in the bak file.
I want to then remove the oldest version when making newer backup, so the bak is always 4 version size and always keeps a month history.
How do I do this?
THank you very much.
July 5, 2011 at 5:34 am
halifaxdal (7/5/2011)
I want to then remove the oldest version when making newer backup, so the bak is always 4 version size and always keeps a month history.
this statement is confusing.Do you want to keep recent 4 backup files and delete other backup files ?.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 5, 2011 at 5:35 am
I think he wants to keep all 4 backups in the same .bak file and then remove the oldest.
What I've always seen is 1 file per backup where you delete the oldest.
July 5, 2011 at 1:42 pm
Ninja is right
So can I create one file including 4 weekly backups? or can I create 4 files with different name?
July 5, 2011 at 3:37 pm
I preffer 4 files. Like DBName - PROD - 2011-07-05 07H00.bak.
That way you know exactly what's in the file.
If you have 4 backups in the same .bak file it makes it a lot more obscure and you can even restore the wrong version of the backup from the file.
July 6, 2011 at 5:22 am
Ninja's_RGR'us (7/5/2011)
I preffer 4 files. Like DBName - PROD - 2011-07-05 07H00.bak.That way you know exactly what's in the file.
Exactly! But how do I do this in SSMS? and then make it a schedulable maintenance job?
Thank you
July 6, 2011 at 5:36 am
halifaxdal (7/6/2011)
Ninja's_RGR'us (7/5/2011)
I preffer 4 files. Like DBName - PROD - 2011-07-05 07H00.bak.That way you know exactly what's in the file.
Exactly! But how do I do this in SSMS? and then make it a schedulable maintenance job?
Thank you
I can't share my solution. This is has gotten rave reviews by trusted friends: http://ola.hallengren.com/
July 6, 2011 at 5:46 am
Thank you my friend, I will check later.
July 6, 2011 at 6:01 am
the maintainence plan wizard will also setup these date stamped backups for you.
July 6, 2011 at 6:08 am
Create a maintenance plan for your Weekly backup, and set clear backup file more than 30 days .
July 6, 2011 at 6:14 am
steveb. (7/6/2011)
the maintainence plan wizard will also setup these date stamped backups for you.
MP have always been buggy over the years... I've pretty much lost all confidence in them!
July 6, 2011 at 2:54 pm
Ninja's_RGR'us (7/6/2011)
steveb. (7/6/2011)
the maintainence plan wizard will also setup these date stamped backups for you.MP have always been buggy over the years... I've pretty much lost all confidence in them!
I can't agree more on this
July 6, 2011 at 3:43 pm
halifaxdal (7/6/2011)
Ninja's_RGR'us (7/6/2011)
steveb. (7/6/2011)
the maintainence plan wizard will also setup these date stamped backups for you.MP have always been buggy over the years... I've pretty much lost all confidence in them!
I can't agree more on this
what sort of issues have you guys had with the backup plan?
I have used it for years and never really had a problem, also you can see what t-sql the plan is producing and it seems very close to t-sql that one would write.
July 6, 2011 at 4:41 pm
Maintenance plans have worked for me in the past, but I like Ola's script as a more flexible solution.
July 7, 2011 at 1:05 am
Steve Jones - SSC Editor (7/6/2011)
Maintenance plans have worked for me in the past, but I like Ola's script as a more flexible solution.
Ola's script is definitely a lot better than the maintenance plan, but i find it can be a bit overwhelming to show this to people when they are just starting out in SQL compared to a couple of clicks on a plan.
Like anything its about finding the right tool for the situation.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply