Maintenance Plan Wizard - backup on one or more files

Question

Maintenance Plan Wizard - backup on one or more files

Virtopeanu

SSC Enthusiast

Points: 129
More actions
January 26, 2007 at 7:51 am

#369081

I created some maintenance plans for 10 databases in each plan(MSSQL 2005) for full and trans backups using the option "create a backup file for every database' .
It was ok until I realized the old backups where not deleted since i created the plans and also is ataching a timestamp to the name of the db for bak and trn files.
Now the request is to create only one maintenance plan for all 30 db ( they are not too big ) and to have the name of bak and trn files not changes but stable and also use override not append so I will use the option 'Back up databases on one or more files' .
As i have selected 30 databases to be back up I added 30 files for each db a file ( used the db name .bak)
I never used this option before!
My question is : Is this new plan going to back up each db into its .bak file with the same name ? or is going to spread across all 30 backups into these 30 .bak files but the spread is not one to one by name?!!!
The same I included the next step in the maintenance plan for transactional backup - and I am planing to do the same for trn files!
Is this going to be a good back up ? I do not want to have the old back ups in that folder just for the last 3 days!
DO you know a way to add a job to be executed in another maintenance plan to delete old than 3 days backups when the name of old back up files is not fixed but aprended with the timestam?
Thanks in advance!
Doi

Viewing 8 posts - 1 through 7 (of 7 total)

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

Harish Shrivastava-401924 SSC Journeyman Points: 98 More actions · Answer 1

If you check the box for creating each database in subdirectory then the destination folder will have 30 subfolders of 30 database and if you chaek the delete after xx daya/weeks/monts then after that dureation backup will be deleted automatically.

Virtopeanu SSC Enthusiast Points: 129 More actions · Answer 2

First many thanks for answer .

In my last maintenance plans for these 30 databases I used to have the option to create for each db a subdirectory and to delete after 3 days BUT this is the problem the backups were created in each subdirectory with named with the name of the db and added a timestamp for the bak file name.

And even if I had the option to delete after 3 days that is only for the history not the files . My folder size increaded since it contains the backups for the last year and I need to manually clean it .

Is there a script to add it in my maintenance plan to delete files with the name starting with the name of my databases ..?

Harish Shrivastava-401924 SSC Journeyman Points: 98 More actions · Answer 3

If you are using MSDE or SQL express then you can't delete automatically.

Or reschedule the maintanace plan

philcart SSC-Forever Points: 47794 More actions · Answer 4

If you're using the new SQL Maintenance Plans utilising Integration Services tasks, then you should include the two clean-up tasks that are provided.

'Clean Up History' - removes rows from job history and maintenance history tables and deletes the text reports that are created.

'Clean Up Files' - deletes the backup files.

--------------------
Colt 45 - the original point and click interface

Virtopeanu SSC Enthusiast Points: 129 More actions · Answer 5

Thanks Phill ( i am an adopted ozzy myself)

Indeed I use the SQL Maintenance Plan but I use the Maint task wizard where I have only the clean up history to select. There is no option to clean up Files.

I have a execute agent job where now I am working on a sp to delete me files with the name of db with the datestamp appended less than current day with 3 days .

Still working on it ...as i have problem with the format - when the old bakups were created the names were like

'abc_backup_200701222253.bak' where abc is my db name.

In my sp when I want to create the timestamps less than 3 days

LEFT(

CONVERT(VARCHAR(19), getdate(), 120),8)+

SUBSTRING(CONVERT(VARCHAR(19), DateAdd(Day, -3, getdate()), 120),9,2)++'??????'+'.bak'

I get dates separated by dash like :

backup_2007-01-27??????.bak

Constance Grasshopper Points: 13 More actions · Answer 6

Hi. I had a similar problem on our new SQL 2005 server. My backups almost filled the disk, so I created a batch program with Robocopy which moves files older than 2 days from the backup folders to a pending folder, then deletes all files from the pending folder. It's very easy to do and then set it up as a scheduled task on the server. This way you can keep the individual folders for backups and allow SQL to put its timestamp on each .bak and each .trn file, so recovery is a breeze.

-Constance

VD Ten Centuries Points: 1149 More actions · Answer 7

The wizard doesn't have the step to delete backup files. I usually create a maint plan with the wizard and then open it and add Maintenance Cleanup Task. It has an option to include first level subfolders. Works like a charm.