restore sql 2000 .bak from different machine

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 2000 Forums

Import/Export (DTS) and Replication (2000)

restore sql 2000 .bak from different machine

Author

Topic

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-22 : 18:12:15

I am trying to restore a db from a winserver 2000 with sql 2000 onto a different machine with winserver 2000 and sql 2000. I keep getting errors, this is the lastest error:

Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\maxtest6a.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This is the command used:

RESTORE DATABASE chsprod
FROM DISK = 'C:\maxtest6a.BAK'
WITH REPLACE, MOVE 'chsprod' TO 'C:\MSSQL\DATA\chsprod6_Data.MDF',
MOVE 'chsprod_log' TO 'C:\MSSQL\DATA\chsprod6_Log.LDF'

the .bak was on a machine with two drives and was on D: (drive) anyway to edit the .bak to read C: or what. The original server was lrlsql03 and db name chsprod. The new server name lrlsql03 and new db named chsprod6.

Any help please?

mrt

Kristen
Test

22859 Posts

Posted - 2010-05-22 : 18:20:10

What does:

RESTORE FILELISTONLY FROM DISK = 'C:\maxtest6a.BAK'

tell you?

From there you may be able to work out the Restore command with reference to this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-22 : 18:24:55

I forgot, I ran this cmd:

RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxtest6a.BAK'
and got this:

LogicalName PhysicalName Type FileGroupName Size MaxSize

1.chsprod_Data D:\MSSQL7\data\maxtest6A.mdf D PRIMARY 3447259136 35184372080640

2.chsprod_Log D:\MSSQL7\data\maxtest6A_log.ldf L NULL 2951086080 35184372080640

3.chsprod_1_Log D:\MSSQL7\data\maxtest6A_log_1.ldf L NULL 22675456 35184372080640

4.chsprod_2_Log D:\MSSQL7\data\maxtest6A_log_2.ldf L NULL 22675456 35184372080640[/size=1]

mrt

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-22 : 18:34:20

the db was named maxtest6a.bak on the old machine

mrt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-22 : 18:38:31

You'll need to add two more WITH MOVEs for the two additional log files. All files need to be accounted for in the RESTORE WITH MOVE command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-22 : 18:56:25

Server: Msg 155, Level 15, State 1, Line 3
'MOVE' is not a recognized RESTORE option.

RESTORE DATABASE chsprod
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxtest6a.BAK'
WITH REPLACE, MOVE, 'chsprod' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\chsprod6_Data.MDF',
MOVE 'chsprod_log' TO 'C:\MSSQL\DATA\chsprod6_Log.LDF'
MOVE 'chsprod_1_log' TO 'C:\MSSQL\DATA\chsprod6_1_Log.LDF'
MOVE 'chsprod_2_log' TO 'C:\MSSQL\DATA\chsprod6_2_Log.LDF'

mrt

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-22 : 19:02:54

I have tried Toad for sql server, WLSQL and EFS database migration tolls and nothing has worked..?

mrt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-22 : 19:06:21

Your syntax is incorrect.

1. You've got a comma after the first MOVE which shouldn't be there.
2. You are missing two commas.
3. The logical name of the MDF file is incorrect, per the RESTORE FILELISTONLY output.

RESTORE DATABASE chsprod
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\maxtest6a.BAK'
WITH REPLACE,
MOVE 'chsprod_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\chsprod6_Data.MDF',
MOVE 'chsprod_log' TO 'C:\MSSQL\DATA\chsprod6_Log.LDF',
MOVE 'chsprod_1_log' TO 'C:\MSSQL\DATA\chsprod6_1_Log.LDF',
MOVE 'chsprod_2_log' TO 'C:\MSSQL\DATA\chsprod6_2_Log.LDF'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-22 : 19:22:51

Thanks for all the help but maybe sql 2000 can't handle it. I can restore the .bak to sql 2005 but when running in Maximo, it loses a lot of the tables. Might just have to start a new one and get what info I can out of the old one.

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\MSSQL\DATA\chsprod6_Log.LDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'chsprod_Log' cannot be restored to 'C:\MSSQL\DATA\chsprod6_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\MSSQL\DATA\chsprod6_1_Log.LDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'chsprod_1_Log' cannot be restored to 'C:\MSSQL\DATA\chsprod6_1_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\MSSQL\DATA\chsprod6_2_Log.LDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'chsprod_2_Log' cannot be restored to 'C:\MSSQL\DATA\chsprod6_2_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

mrt

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-05-22 : 23:18:54

You cannot restore a 2005 database to a 2000 system, which is what it sounds like you are trying to do. A restore does not 'lose' tables. Everything is restored, or nothing is...

Now, if you are not able to access tables it is probably because your login has been orphaned. After the restore on a 2005 system, execute sp_change_users_login with the 'Report' parameter. That will identify your orphaned users. Create those logins, run the same procedure with the 'Auto_Fix' parameter and it will link the user to the specified login.

Lookup the procedure in books online for each parameter and how to use it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-22 : 23:26:32

Make sure that those files don't exist in C:\MSSQL\DATA, unless you are restoring over them from an existing database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-23 : 09:13:17

jeffw8713, As stated in the first post, " am trying to restore a db from a winserver 2000 with sql 2000 onto a different machine with winserver 2000 and sql 2000 and the .bak is from a 2000 sql server, restoring to an 2000 sql server. I appreciate your help but it may be that the old DB "maxtest6a" was on a drive "D" and my server is on drive "C". When I go to do a restore and select the maxtest6a and view the file to restore it shows "Restore AS 'D:\MSSQL7\data\chsprod.mdf' " I have tried chaning the D to C but still gives the error Device Activation error C: MSQL.........may be incorrect.
I am going to reload my server and sql to a drive D after partitioning the hdd and try. Thanks all

mrt

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-23 : 12:09:34

Doesn't matter where the files were. That's what the WITH MOVE syntax is for, to say where the files must be moved to upon restore.

Does the C:\MSSQL\DATA\ directory exist? Does the account that SQL is running under have access to that folder?

--
Gail Shaw
SQL Server MVP

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-05-23 : 12:14:31

All of those errors are stating that the location you are trying to restore to does not exist. Does the directory C:\MSSQL\DATA exist? If it does exist, does the user account that is running SQL Server have access to that directory?

You say you can restore to 2005 - but you 'lose' tables. If you are restoring to a new system, you have to create the logins on the new server correctly or the users in the database will be orphaned. Google sp_help_revlogin and follow the directions to move the logins to the new server. Make sure you use the right one as the version to move to 2005 has changed.

The MOVE option allows you to move the database files, but it does not create the location for you. It has to exist before you perform the restore.

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-23 : 18:24:01

"C:\MSSQL\DATA exist? If it does exist, does the user account that is running SQL Server have access to that directory?"

Yes it does exist and yes the user account has access. Trying the instructions from sp_help_revlogin to move the logins. Thanks

mrt

mrtiggr
Starting Member

9 Posts

Posted - 2010-05-24 : 14:11:05

Thanks to all here who have gave me the ideas to get this db installed. Got it transferred to a new machine and rebuilt the fulltext cat. and can see all the tables. Thanks again..

mrt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources