Restoring a full backup to a database on another server

[bessebo]

Is there anyone out there who has restored a local database to a database on a different server using the Restore Database interface? The books say you can but I have never tried it. I thought I would have to copy over the full backup (or transaction logs)to the other server and then restore locally. I assume that to do the restore to a different server we would have to have a drive letter mapped to that server and database location and then the restore database will work. I know that you can't backup to a network drive and I also thought the converse was true (that you couldn't restore across the network) but maybe I'm wrong. Please advise...

Bessebo

 

[Andel]

You can backup and restore into/from a network drive. And you can also restore a database backup from another server, as long as your destination database has the same or more space than the sourse database.

Depending on what version of SQL server you're running, I always have problem restoring database using enterprise manager in sql6.5. I always use the query window.


Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[bessebo]

Andel,
I assume that you just have to map a drive letter to where your physical filename is located on the other server if you are restoring to a database on another server? Is this correct?

Bessebo

 

[Andel]

Yes, that's correct.
Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[bessebo]

Andel,
We cannot restore to another server because the mapped drive is not recognized. Please elaborate on how you are doing this. Maybe you can post the code that you are using via the query analyzer to do the restore (or a backup for that matter) to another server location.

Thanks,
Bessebo

 

[Andel]

I don't understand why you can't see your mappings. Anyway, here's what you can try in the query query window.

LOAD DATABASE yourdatabasename
FROM DISK = yourbackupfilename
GO

or this

RESTORE DATABASE yourdatabase
FROM yourbackupdevice


Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[cheezebeetle]

Did you try to backup from the local system. Zip it up and FTP it up to the remote one and then restore. This may not work depending on how big the database is, but it has been working for me on the database I am using. (several hundred MBs in size)

 

[bessebo]

We understand that you can back up from the local system. What I don't want to have to do is just what you're suggesting. I don't want to have to copy it to the remote system. I want to do the restore to a remote location so that I don't have to perform the copy first.

Bessebo

 

[tmp14]

Using the Restore from file option you can grab the backup from any location. It doesn't have to be copied locally. However there are some occasional pitfalls....In SQL 7.0 the users are not always created properly on the new database. the logins get created but the sysusers table doesn't get created properly - there is a stored procedure to fix orphaned logins. Also if you are trying to create a entirely new database on another server DTS works really well.

 

[sunglasses]

I met the same problem now in SQL Server 2000. I want to backup data to a network drive. Of course, also should restore data from this network drive. Bessebo, have you got your problem solved? Would you share your solution please?

I think it is the user authority in SQL Server that makes the backup or restore is not permitted in the network drive. Is this correct? I've tried to add a SQL Server permitted user 'sa' in that network drive. But it doesn't work.

Help me please!

Thank you!

 

[tlbroadbent]

SQL Server runs under its own set of NT credentials. Therefore, it cannot see the drives mapped in the Windows login. Check out the follwing FAQ for more insights about this topic.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14025

We backup and restore across our network all the time. You can easily backup and restore using UNC addressing with T-SQL commands or from Enterprise Manager.

--Backup Employees database from server1 to server2

--Use a T-SQL command in Query analyzer:

BACKUP DATABASE Employees To
DISK='\\server2\backup\sql\Employees020328.bak'
WITH INIT

Use Enterprise Manager:

Select backup, click on the Add button and type location and name of the backup file in the File Name of the Select Backup Destinnation dialog.

\\server2\backup\sql\Employees020328.bak

You should be able to create the restore SQL statements using SQL BOL and the examples I've given. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.