Restore did not restore the local packages
-
Hello everyone,
I restored our databases on a server. Everything looks fine except the local packages were not restored. Doesn't SQL Server restore the packages? Actually I used Veritas Backup Exec 8.6. Any help would be greatly appreciated. Thank you.
-
To add to this, I did not recover the system databases, is that something that I should have had to do in order to get the local packages? If yes, I have this question: My backup is from a SQL 7, I am recovering to a SQL 2000, are there any issues or things I should be doing/looking for? Thanks a lot.
-
Yes, you would need to recover the system databases in order to get the DTS packages back. As to the 7.0 restore to 2000 on the system databases the answer would be no.
http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=93
I tried this, hence the FAQ and was rather frustrated with the results. My suggestion would be to restore to a 7.0 box and migrate from there either via an upgrade or saving the packages to the new server.
Hope this helps and sorry for the bad news.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Your packages are in MSDB, restore this, not master.
Steve Jones
-
Thanks Steve! Should have specified msdb in my last post.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Thank you everyone!
-
So its not possible to recover a SQL 7 MSDB to SQL 2000? The reason I am asking is I am having problems installing SQL 7. The installation is not clean, I get an error message and the services are not listed, even though if I try to install SQL 7 again, it tells me that it is already installed. Anyways, here is part of the error that I am getting:
"File replacement was attempted on a protected file c:\winnt\system32\chsbrkr.dll. This file was restored to the original version to maintain system stability..."
Thanks for your help.
-
I found the following on BOL. Does this mean that the MSDN cannot be restored on a SQL 7 database or does it mean that Microsoft Support does not support it? David, you have restored the MSDB to a SQL 7 before, right? Thanks.
"The model and msdb databases can only be restored from backups created on a Microsoft® SQL Server™ 2000 server. Restore of backups of these databases made on SQL Server version 7.0 or earlier is not supported."
-
I try to restore the MSDB database to SQL 7 and I get the following message:
Database msdb
An error occurred on a query to database msdb.
^ ^ ^ ^ ^
SQL Server returned the following error message:
Database in use. The system administrator must have exclusive use of the database to run the restore operation.
^ ^ ^ ^ ^
Please help, I am really stuck. Thank you.
-
BTW, I took care of the file replacement problem. It seems that installing from Terminal Services created the problem.
-
I found the problem. The service for SQL Server Agent shoud be stopped before the restore. If you search the Veritas documentation, it will have two articles that contradict each other, one says to put the database on Singer User mode, the other says to take the Singer User mode off. Anyhow, the problem I had was that the SQL Server Agent was running and accessing the msdb database while the msdb database was on a single user mode. Thanks for all of your help everyone.
-
OK, here is another problem.
My MSDB database was recovered fully (or at least thats what Backup Exec shows in the logs). However, not all of the users' local packages were restored. Could the packages be saved in multiple databases?
-
Look at "Copying DTS Packages To a Different Server" by Andy Warren in this site. I recently moved some DTS packages from a SQL 7 box to a SQL 2000 box. Though I didn't end up using the exact code given in the article, it got me started in the right direction.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Local DTS Packages are stored at the server level, not in individual databases, so that shouldn't be a problem. Is it possible that some packages were stored as files or in Meta Data?
There's a great tool for moving dts packages around at SQLDTS.com (DTS Backup 2000).
quote:
OK, here is another problem.My MSDB database was recovered fully (or at least thats what Backup Exec shows in the logs). However, not all of the users' local packages were restored. Could the packages be saved in multiple databases?
-
SQL server DTS Packages can be saved in 3 different places:
1) In the SQL Server (Not Necessarily in the same server, could be in a different server too; of course not at the database level, but at the server level)
If it is stored in SQL server it should be on the MSDB database and in the SYSDTSPACKAGES table. You have to find out the right server where the package was saved to restore the package. What I mean by that is by default it picks the local server. You can choose a remote server if you wish and restore (or copy) it from there.
2) It can be restored in an O/S file as a .com object (the file name should be some_name.dts), which can be restored by going to the DTS package and selecting open.
3) It can also be stored in the respository and restored from there.
You have to find out where the package was saved in the first place.
Hope that helps.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply