I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.
If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.
I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.
Thanks.
|||If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
and modify the BackupDirectory value, you'll have this as the new default.
Note that this permanently modifies the default location for ALL TSQL backups!
|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||
Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.
Thanks again for the info though.
Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.
I guess we'll just have to wait and see if it is ever acknowledged.
|||
Actually, this is a server-side setting.
If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!
|||Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.
For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.
Anyway, thanks again for the big tip! I'm sure it will help others as well.
P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.
|||It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.
Thank you for the great assistance!
|||The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.
It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.
No comments:
Post a Comment