Restore database from SQL Server 2000 to SQl Server 2005

I have a file backup database SQL Server 2000
I want to restore it to SQL Server 2005.
But it failed.
I try :
Database ( right-click) - Restore Database - From Device - Specify Backup Add
Locate Backup File. I must choose File of Type is All File so that i can find file backup from SQL 2000, if i choose File of Type is *.bak,*.trn so i cannot find that file.
And then i choose OK all. But it failed. The error is below :

Restore failed for Server 'DHBK-734D0C3663'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\qlsach_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()

===================================

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\qlsach_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)IS sqlservice running on service account? If yes, check whether you have modify privilege for service account for the path "C:\Program Files\Microsoft SQL Server\MSSQL\data"|||

That error is actually not related to SQL Server versions but the drive letter of your original backup because I just solved it for SQL Server 2005 about six weeks ago. If the backup was created in my case E drive instead of C drive where SQL Server is located it just give you this error.

Now to solution you need four sets of small code create a backup devices in the Master for the MDF file, LDF file, then put the database in restore, which makes SQL Server to put it in restoring mode and the last code to restore. I found some code in the link below which I converted and it worked for me. Hope this helps.

http://technet.microsoft.com/en-us/library/ms156421.aspx

|||

Here is a blog posting by another developer if you cannot use the code in the technet link.

http://wvmitchell.blogspot.com/search?q=backup+restore

|||I want some solution guide in visual, please. I am not a developer. I have just begun to learn SQL and SQL Server.
In my school, they are using SQL Server 2000.
In home, i installed SQL Server 2005.|||

This is not something you can do if you are new to SQL Server because you need to know if each step is correct, you can download AdventureWorks Samples from the link below install and attach it is very easy to do. When it is installed just go to Management Studio right click in the databases space and select attach and you are ready to use the database. Download the x86 case insensitive and you could also download the samples, these are Reporting and other services samples that you can look at and play with. Post again if you still need help.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||May be i reinstall SQL Server 2000.Hic hic|||

No you don't have to do that and I am glad you asked because there is an easy alternative which is to attach the database to SQL Server 2005. It is not normal attach with a wizard but it is just one set of code to write and it is easy.

So here are the steps you run sp_dettach_db at your 2000 and copy the MDF and LDF which are in the Data subfolder next to the Backup folder and put those in same location in 2005. The code below change 'Yourdbname' to your database name, then copy your MDF and LDF in your 2000 location below and put in the 2005 location below in your home box. Remember to change 'Yourdbname' to your actual database name or SQL Server will not create the database. I got the code from the link below post again if you still need help, try to read the link so you understand the steps needed to move the database. Hope this helps.

USE master;
GO
EXEC sp_detach_db @.dbname = N'Yourdbname';
GO

C:\Program Files\Microsoft SQL Server\MSSQL\Data\ (copy files from 2000 location)

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ (put in 2005 location)

USE master;
GO
CREATE DATABASE pubs ON PRIMARY
(FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Yourdbname.mdf')
LOG ON (FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Yourdbname_log.ldf')
FOR ATTACH;
GO

http://msdn2.microsoft.com/en-us/library/ms189625.aspx

|||

I just came across this thread having the same issue, but the circumstances may be unique. This may work for your case too, using Sql Management Studio's GUI instead of a script. The steps and the order was the key.

In my case we have a database backup file *.BAK and I needed to create a separate test instance of a web site and because we implement a custom membership provider, I needed a raw backup for testing new membership data.

So this is the scenario: 1 *.BAK file which I need to restore as 2 different databases. The second database needs a unique name. I do not have time to explain what not to do, but try this.

1. Right click on the 'Databases' node and select 'Restore database'. DO NOT CREATE A BLANK DATABASE FIRST.

2. Select Restore database: The dialog that appears includes at the top a dropdownlist where an existing database could be selected. Do not select a database (since you did not create a blank database first ) and enter the database name you wish by typing it in to the listbox.

3. Beneath select "From device" and navigate to your *.BAK file.

4. Select the check mark to restore this database

5. Select options.

6. Select overwrite existing database

At this point you will see that the correct mdf and log file information was created for you if you examine the database file paths while in the options tab of the restore database dialog.

Click okay.