June 15, 2009 at 7:57 am
Hello.
I have a question regarding the following.
Using SQL Server 2008 Web edition. Dealing with 15GB client DB, so for backups I wanted to save disk space, so I've setup a maintenance plan to make full backups every sunday, differential backups every night (user DBs, except sunday) and hourly transaction log backups in between the differentials.
Now my question is: if I restore the DB at (for example) wednesday 3pm, do I need to make a new manual full backup after the restore or would the thursdays differential be considered as full?
Similarly - if I add new user DB on monday, will tuesday's differential backup be considered as full or do I have to do the first full backup manually?
One more question - I made single maint. plan for all the tasks (with custom schedules for each task - subplan) - is it possible to manually execute only selecteds subplan? Or what will happen if I execute the maint. plan manually - does it check the schedule and if it finds no scheduled action for that moment, will it skip them all or perform all the tasks?
June 15, 2009 at 8:14 am
markos (6/15/2009)
Hello.Now my question is: if I restore the DB at (for example) wednesday 3pm, do I need to make a new manual full backup after the restore or would the thursdays differential be considered as full?
no. you need to take a fresh full backup after u restore, or else differential backup will fail. Thursday's diff cannot be considered full. It's a diff backup.
[/quote]Similarly - if I add new user DB on monday, will tuesday's differential backup be considered as full or do I have to do the first full backup manually?[/quote]
If you're adding a database, it must be backed up fully once or else u wont be able to take differential or tran log backups. You have to do full backup on monday after database creation and change ur maintainance plan to include this db.
Moreover, tuesday's diff backup cannot be considered full. diff backup is diff backup and must be used in conjuction with a full backup.
One more question - I made single maint. plan for all the tasks (with custom schedules for each task - subplan) - is it possible to manually execute only selecteds subplan? Or what will happen if I execute the maint. plan manually - does it check the schedule and if it finds no scheduled action for that moment, will it skip them all or perform all the tasks?
You can execute sub steps manually, but make sure ur log chain remains intact. For example if log backup happens at 1 am, 2 am.... and so and u manually fire log backup at 1.15 am, this log backup file must be present during restore in the same sequence as other log backups.
For differential backups, it doesnt matter if you take seperate backup, only the latest one will be needed for restoration.
June 15, 2009 at 9:41 am
Thanks alot for the prompt reply. One more (reassuring) question.
ps (6/15/2009)You have to do full backup on monday after database creation and change ur maintainance plan to include this db.
If the maintenance plan is set to backup all user DBs, the newly added/created DB should be included automatically, wouldn't it?
You can execute sub steps manually, but make sure ur log chain remains intact. For example if log backup happens at 1 am, 2 am.... and so and u manually fire log backup at 1.15 am, this log backup file must be present during restore in the same sequence as other log backups.
Thanks for clearing that up for me.
June 15, 2009 at 9:55 am
markos (6/15/2009)
If the maintenance plan is set to backup all user DBs, the newly added/created DB should be included automatically, wouldn't it?
I just tried that, it didnt include the new db. I opened up my maint plan and clicked on databases and once again selected ALL User Databases and saved. This did the trick as i noticed that before selecting it showed me older databases and after selecting it included new database. Perhaps it refreshed itself. No clue on this. You can check the T-SQL before and after this 'refresh' to see the statements that the plan is going to fire.
June 15, 2009 at 10:53 am
The maintenance plan resolves some things at save time, but I thought that in 2005 it ran a cursor over sys.databases at run time. Is this an SP2 patched or SP3 server instance? There were some flaky things in the RTM version.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 15, 2009 at 11:02 am
Steve - I'm using RTM Developer edition.
I tried saving also but it didn't resolve things. only when i re-selected databases drop down, it resolved.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply