May 25, 2005 at 6:29 am
I have a database on server A that I would like to backup and restore on server B. Can anyone tell me how this is done and what the risks involved with this are please?
Thanks
May 25, 2005 at 6:42 am
You can either do this in enterprise manager by creating the backup on server A and restoring it on server B by choosing the restore from device option in the restore dialog.
The other option is to use the TSQL syntax for a restore and move the datafiles to a valid location on server B:
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',
MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH RECOVERY
the move statements go:
MOVE 'logical_datafilename' TO 'new_physical_datafilename',
MOVE 'logical_logfilename' TO 'new_physical_logfilename'
May 25, 2005 at 6:52 am
After you restore on Server B, you will have to deal with the orphaned users (users in your database that either don't have an asociated login on Server B or their SIDs don't match up with their login on Server B). Do a search on Orphan Users here for more information.
One article:
http://qa.sqlservercentral.com/columnists/rsharma/copydatabase.asp
May 26, 2005 at 12:12 am
Hi David,
You can take the backup of server A on Server B and restore it to the B.Or you can take the backup on Server A and restore it on B server.
The T-Sql given below.
use master
go
DBCC TRACEON(1807)
GO
-- Restore from server A
RESTORE DATABASE Northwnd
FROM Disk = '\\Server A\f$\Northwnd.bak'
WITH RECOVERY,
MOVE 'Northwnd' TO 'E:\Data\Northwnd.MDF',
MOVE 'Northwnd_LOG' TO 'E:\Data\Northwnd.ldf'
go
In the same way you can keep the backup file on server B and restore.
Regards
B John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply