I am trying to restore SQL 2000 DB into SQL 2005 Database.
I backup the database from SQL 2000.
From Management Suit, I try to restore database but I can not see network drive from there even though SQL serveris running under network account.
I could see network drive from SQL 2000 or at least I can type path to find backup file. However I can not do this..
My Q is:
1. How can I restore this SQL 2000 db to SQL 2005 using network path?
2. Since backup is SQL 2000 database, when I restore into SQL 2005, does restore upgrade system tables and other schema to SQL 2005 as well?
If not, what is the best practice to upgrade this database into SQL 2005?
Upgrading SQL 2000 current server to SQL 2005 is not an option at this point.
Eventually porduction server will be scrup and install SQL 2005 and then restore DBs into production machine...
Thx in advance
Jay
1. We support restoring from network paths. You need to ensure the sql service has access to the path. The GUI would also need access to the network path. You might try using an explicit UNC rather than drive letters.
2. We automatically upgrade the system tables as part of the restore (or attach).|||
Steve Schmidt
Thanks for the post here. But can you lay it down in plain english. What I mean is I need a step by step. What I have done is I have the backup for my SQL Server 2000 databases. I have created the tables in SQL Server 2005. Then I go to restore the files using the Restore Database in SQL Server 2005. I select the newly created database in the To Database on the general tab. Select From File on the general tab and browse for the backup file which I find. Check the restore check box then hit OK.
I get the following error.
// Start Error Message //
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'VM-WIN03SERVER'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'shoplist' database. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
// End Error Message //
If I do an import it works just fine but you loss all your key info and other important elements just like when you do a import from SQL Server 2000 to SQL Server 2000.
|||Your procedure is correct.
There is an "overwrite existing database" option on the "options" page of the restore dialog. You need to check it to avoid the error.
The error indicates that the backup set was not created by backing up the current database you plan to overwrite. So if you are sure that you have the correct backup set, check that box and the restore will procede.
Hope that helps.
|||Hi,
I was having a similar problem. I am trying to restore a .bak file from another sql server 2000. We are looking at moving all the DB's to the new server running sql 2005.
I have tried a restore using the managment studion via right click on the databases and also tried making a new data base that I may restore into like the example above. I both cases I get an error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'SQLSERVPRO2'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Robert_Taylor_Sys.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
What the problem was here was the paths where the origional data files are not same in sql 2005 x64. Under the options section there is a place to redirect the files to restore.
|||I am getting the same error msg. We did backup in SQL 2005 itself and wanted to try the restore operation but throws the same error. I did choose "force overwrite existing db" but still the same.
Any idea?
|||Anyone had solution to this? I'm experiencing the same error.|||This error:
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Robert_Taylor_Sys.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
indicates that restore can not find the path:
C:\Program Files\Microsoft SQL Server\MSSQL\Data
You either need to create the path yourself, or:
1. use "WITH MOVE" in the T-SQL
or
2. use the GUI to specify an alternate location for each database file
|||To restore a SQL 2000 database on SQL 2005
create a new database in 2005 with the same name and compatibility set for SQL 2000 or the version you want to restore from.
restore this database with options 'overwrite existing database'
i did this from one server to another, but it is also possible from a .bak file ofcourse.
gtx. YAK
|||I have done this with about 30+ DB. The fastest way and most reliable was to back up the DB from the SQL 2000 machine on to the SQL 2005 machine via EM. Then just login to the sql 2005 machine and use the restore database from SSMS. This way you can insure all of your tables will stay exactly the way that you had them ie identities. I have tried many different ways and this is by far the most straight forward. Of course you could script the backup and the restore if you are going to be doing this often or have more than one DB to convert/move.|||I am trying to restore a database from SQL7 to SQL2005 but get a message:
System.data.sqlclient.sqlerror: The backup set holds a backup of a database other than the existing database.
I have tried the restore and the inport option for the sql2005 database.
****Update
I used the overwrite option with a complete backup file. The procesws seamed to work but now I have a (6.5 Compatible) next to the dadabase name and there is nothing (tables, views, security, etc..) associated with the database. Now I can not delete or rename the database. Those options are not supported: 6.5 database compatibility level is not supported.
Any ideas?
|||Okay since this forum has failed to answer the question, here's the process in SSMS.
1) Go to restore database
2) Select the database that you want to back up to
3) Locate the backup file on disk. You may have to put it into the MSSQL Server -> MSSQL.1-> MSSQL -> Backup Folder. It must be a .bak file.
4) Select the back that want to restore from the available backups.
5) Go to the top left "options" property and when you do that select "overrite existing database".
6) Now make sure that the path to the files on database to be restored are correct in this same dialog view. Look at the paths to the database file and the log file and make damn sure that they are the correct ones for the database to be restored. The problem here is that those paths are going to be for the filesystem that the backup came from, not the one you are goning to put the restore onto. That's the big problem here.
|||it working with my good
just go options then chang the path for .mdf and ldf file
like : C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.mdf
C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.ldf
choose new name for the database
clear all check box overwrite options
|||Guys,
I have experienced challenges that only Microsoft can supply,
When all looks fine and it still doesn't work then it is time to copy and paste. I experienced similar problems and found when I copied the file path from windows explorer directly into the "options" -> "restore database files" ->"Restore As", it worked. I did not know that it was case sensitive, if not my DB was just tempremental.
If you follow the previous steps and copy and paste then it will hopefully work.
|||Hey "Reformed ", yes it worked the way you told us. :) Thanks it was a great help!
The only thing that was not correct was the path ... which you highlighted.
Thanks again!
No comments:
Post a Comment