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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 error importing sql server 2000 backup to slq 2005

Author  Topic 

HalfHuman
Starting Member

2 Posts

Posted - 2007-09-05 : 09:13:20
Hello

i have a backup of sql server 2000 pro sp4 database and i want to import it to a sql server 2005 developer edition sp2. i tried to import the database using RESTORE DATABASE IntEx
FROM DISK = 'C:\projects\backupBD\IntEx.bak'
and got the following error

quote:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\IntEX_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'IntEX_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\IntEX_Data.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\IntEX_Log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'IntEX_Log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\IntEX_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.



i tried then importing it with the restore function from sql manager and got the error:

quote:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'AMDINSIDE\TESTBED'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\IntEx.MDF'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476



as i said i'm using a sql server 2005 sp2. i tried the scripted method when i did not have sp2 in sql server and it worked without a problem. after installing sp2 it seems it does not want to work anymore. i also tied making a backup of a sql server 2005 database, deleting the database and restoring it and it worked again.

can somebody give me any advice on this problem?

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 09:21:53
By default SQL Server will try to restore the database to the path of the ORIGINAL (i.e. SQL 2000) database, which probably doesn't exist on the new SQL 2005 server

See the example of a "more complete" syntax for RESTORE here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen
Go to Top of Page

HalfHuman
Starting Member

2 Posts

Posted - 2007-09-05 : 10:04:17
thanks for answering. i managed to fix it by using this:

RESTORE DATABASE AgendaX
FROM DISK = 'C:\Projects\backupBD\AgendaTest.BAK'
WITH REPLACE, MOVE 'Agenda' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AgendaX_Data.MDF',
MOVE 'Agenda_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AgendaX_Log.LDF'

seems you were right about those paths. i did not think that the path is stored in the backup.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 10:46:03
I would suggest that you should also rename the Logical names, so that they are consistent (as per the link I posted above)

"i did not think that the path is stored in the backup"

I think the idea is that if you are restoring on the original machine (probably the most likely scenario) this makes the job easier because you don't have to know where the files are supposed to be kept - quite important for a system carefully tuned and thus spread over multiple spindles for performance reasons

But its a bit of a pain when you DO need to restore to a new location.

Kristen
Go to Top of Page
   

- Advertisement -