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 |
|
fritzy
Starting Member
5 Posts |
 Posted - 2007-08-28 : 18:21:53
|
| I've been working on implementing my backups via scripts and not using maintenance plans. I'm test restoring the backups to ensure things are going well and in the process, working with the sprocs out there that manage the deletion of older files. I created three transaction log backups and ended up deleting them after some time, but when I went into EM to do a restore, they still appeared as valid backup sets even though the files no longer existed.I'm curious: why is this so? Is this just one of the reasons most DBAs recommend NOT using EM to restore files? Why don't the sprocs that manage physical file deletion also manage deletion of media sets?Thanks,fritzy |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 21:47:04
|
| You have to clean up backup history with msdb..sp_delete_backuphistory. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 08:31:23
|
| Beware that will run like a dog if you have lots of backup history.See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=MSDB+Massive+msdbdata.mdf |
|
|
|
fritzy
Starting Member
5 Posts |
Posted - 2007-08-29 : 12:42:20
|
| Thanks for the replies. I figured I would've seen this sproc (sp_delete_backuphistory) in one of the many scripts out there to manage backup files so I was curious why I hadn't.Kristen - I actually bookmarked that url the other day ;). Fantastic resource. I've been over everything relating to backup and didn't find much about backup history other than managing it in a custom table.I've never had a problem using maintenance plans or EM to manage restores. I've been researching this to understand more about SQL Server backup/restore am just working to understand the true differences and reasons to use either solution (EM vs. scripts). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 14:28:02
|
"I figured I would've seen this sproc ..."me too! It only came to light here when I started wondering why my MSDB database was so huge. The tables were SYSTEM so didn't show up immediately, so it looked like a huge database which was nearly-EMPTY ! Anyway it got me asking questions and I found out that there was no automatic housekeeping of the backup/restore logs. This is remedied (from memory) in the SQL 2005 Maintenance Wizard Thingie."understand the true differences and reasons to use either solution (EM vs. scripts)"Tricky question.Making scripts is a BIG job. Lots of time & effort to get working well, and then are you sure you knew enough to set up all the possible failure reporting etc.?Then again ... once the Maintenance Plan system lets you down, tells you there was an error, but gives you no useful means of finding out what the problem was you would probably wish you had created your own scripts  A mix of recovery models (Full and Simple) and trying to have an automated "If I create a new database add it to the maintenance plan automatically" is quite a challenge (for us, building for this scenario, the Maintenance Wizard fails to delete the TLog backups, so we have to do that manually, and then someone forgets and the whole system stops with a Disk Full).Of course if you want to add a rule to your automatically-add-new-databases-to-maintenance-plan to provide stuff like: "Do NOT add to automatic backup IF the name starts with "RESTORE_" (which we do actually have, so we can restore a database for mucking-about and NOT have it automatically backed up - kinda important when you mount a copy-database that is main GB large!) you are fresh-out-of-luck with the maintenance wizard  The SQL 2005 Wizard has all the obvious flaws in SQL 2000 fixed. It supports Differential backups, for example, and all the obvious housekeeping stuff is well/better supported.For someone new starting at SQL 2005, and with "modest requirement", the Maintenance Wizard might well be a good-enough solution.Kristen |
|
|
|
fritzy
Starting Member
5 Posts |
Posted - 2007-08-29 : 18:50:50
|
| Thanks Kristen. Essentially, complete control is what you want - I can dig that ;)Glad to know I wasn't being obtuse about the Backup History. I'm using SQL 2000 which I should've stated earlier, but everything still applies, and it's good to know the intracies about what was really fixed in SQL 2005 maintenance plans becuase for us, they've proven valuable. Well, SQL 2005 SP2 I should say.I'm feeling good about the solution I'm putting together - using scripts to backup and to manage the old files. I'll tweak them to use the system sproc for clearing backup history too.Thanks again,fritzy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-30 : 04:31:02
|
| "I'm feeling good about the solution I'm putting together"Give it a good test before you sleep easy, eh?!!"Glad to know I wasn't being obtuse about the Backup History"Script for a list of Backup History here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#273265(and one for Restore History slightly higher up that thread)Kristen |
|
|
|
|
|
|
|
|
Restoring - deleted files on disk show in EM