Run DB on another server 2

[password99]

Is it possible to take a DB from one server and run on other?

e.g.
Server A on machine X with DB 1
Server A on machine Y with DB 1

Now I want to take DB 1 from machine X and use it on machine Y? What are the steps to accomplish this?

 

[JayKusch]

You could use sp_detach to detach the DB from the Source server, and also on the Target server if there is a DB already there.

Then you would copy over the MDF and LDF of the DB to the Target server.

Then run sp_attach_db, to reattach the new DB to the Target server. May also run that sp to reattach the Source DB again.

Thanks

J. Kusch

 

[Corran007]

you can also do backup and restore if you are not able to take down your first database.

dont forget to resync any sql users when you switch databases.

 

[password99]

can i restore DB from another server?

e.g.
if I had
Server A on machine X with DB 1
Server A on machine Y with DB 1


Perform Backup of DB 1 on machine X
Perform Restore of machine X DB 1 into machine Y

can you please further explain "resync any sql users when you switch databases."?

 

[Corran007]

yes you can restore it.

the resync sql users is that the login name and name will not be alligned on the new server for sqlserver users only. if you only use windows authincation, you have nothing to worry about.
First you need to make sure that you create any users that database has in them that are not on the second server already.

Then run this in the selected database. i believe you need sysadmin to run sp_change_users_login. It will align the users correctly

DECLARE @USERNAME VARCHAR(40)

DECLARE User_Cursor CURSOR FOR
select name from sysusers WHERE issqluser = 1 and suser_sname(sid) is null and name not in ('guest','dbo','public')

OPEN User_Cursor

FETCH NEXT FROM User_Cursor
INTO @USERNAME

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC sp_change_users_login 'Auto_Fix',@USERNAME,NULL
Print @USERNAME + ' Changed'
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM User_Cursor
INTO @USERNAME
END

CLOSE User_Cursor
DEALLOCATE User_Cursor
GO

 

[password99]

Thanks for prompt reply.

In this case both the machines have identical servers running with the same users. however the data in one the DB is different and they would like to get the data from the other server to this server DB.

 

[password99]

you can also do backup and restore if you are not able to take down your first database.

I found that performing BACKUP on source server
and RESTORE on target server from source backup file was easier than detach/attach

 

[JayKusch]

It works just as well but not as fast. 1 copy operation as opposed to a Backup and then a Restore operation. If you are able to use Enterprise Manager for the detach and attach DB operations instead of using T-SQL commands, it is even faster.

Thanks

J. Kusch