Unable to RESTORE DATABASE to Another SQL Server 2

[JohnBates]

hi everyone,

I'm trying to restore a database (from tape) to a new db name on another SQL Server 2000 server. Let's call them Server1 and Server2. Both are on the same domain. The tape drive is attached to Server1 and I'm trying to restore to Server2.

I even tried using the server name \\Server2\C$\Webdata
in place of the mapped drive letter, but that failed with same error.

Maybe I mis-coded the syntax for the RESTORE DATABASE...here it is:

Thanks for any ideas. John

RESTORE DATABASE [gmRestored] FROM TAPE = N'\\.\Tape0' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , MOVE N'gm_Data' TO N'T:\WebData\gmRestored.mdf', MOVE N'gm_Log' TO N'T:\WebData\gmRestored.ldf'

failed:
Server: Msg 5110, Level 16, State 2, Line 1
File 'T:\WebData\gmRestored.mdf' is on a network device not supported for database files.
Server: Msg 3156, Level 16, State 1, Line 1
File 'gm_Data' cannot be restored to 'T:\WebData\gmRestored.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5110, Level 16, State 1, Line 1
File 'T:\WebData\gmRestored.ldf' is on a network device not supported for database files.
Server: Msg 3156, Level 16, State 1, Line 1
File 'gm_Log' cannot be restored to 'T:\WebData\gmRestored.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.

 

[mrdenny]

You can't restore a database to a drive on another server.

To restore from tape to server2 you basically have three options.
1. Install a tape drive on server2 and put the tape in that drive and do the restore directly on server2.
2. Restore the database to server1 then detach the database, move the files to server2's disk then attach the database.
3. Backup the existing database on server1 to disk, move the backup file to server2's disk and do the restore.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JohnBates]

wow that's very enlightening. MS really should allow this to be done.

See, my situation is that Server1 doen't have enough disk space to hold this db, but Server2 does.

Looks like I'll have to connect the tape drive to Server2 and perform the restore directly on Server2.... another weekend job I'm sorry to say.

Thanks for the info. John

 

[Catadmin]

MS has set up SQL to only allow backup and restores to directly connected backup devices. They do allow you to back up to a UNC network share (if you set it up correctly), but that's the ONLY time you can restore or backup to a drive that isn't directly on the machine.

The reason for this is security. How would you feel if someone got into your system and stole your data because they could back it up to their system. Or worse, overwrite your data from their own devices?

I would be in DBA hell if something like that could happen. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[JohnBates]

Cataadmin,

Yes I'm backing up Server2's databases to a snap server already.

I can see your point about security.
Thanks, John

 

[mrdenny]

The reason that you can't restore to a remote folder is also a performance issue.

If you restored and put the files on a network share, that network connection is now going to be flooded with data as SQL is constently reading and writting to it's files. So your network is now slowing down a lot. Your access to the SQL Server files will also be a lot slower over the network then it would be on a local disk, as you now have to deal with the other server having to instect every read and write request to ensure that the user account running your SQL Server has access to the network share, and the NTFS storage.

Now what happens when there is a momentary network hickup. Just a split second disconnection from the server running SQL Server and the server holding the mdf and ldf files. You've just been disconnected from the files in the middle of writting a transaction to the file. SQL would probably detect the write failure and try to roll back the transaction. But the drives aren't there. Your database is now marked as suspect.

So now your database comes back online and your transaction log and database files are out of sync with each other. You've lost your database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)