Moving Databases from one physical Server to another

Question

Moving Databases from one physical Server to another

mngong

SSCertifiable

Points: 6167
More actions
April 29, 2004 at 12:15 pm

#107781

I have a couple of databases I plan to relocate to a new Server.
My options as of this time are ..
1)Backing up and restoring the master database and the other databases.
BACKUP DATABASE master TO DISK='c:\master.bak'(Copy to new server)
Restore DATABASE master from DISK='c:\master.bak'(with overwrite)
and do same thing for the other databases.
2)sp_detach sp_attach
3)import/export
Has anyone done this before?
Any method prefered over the others?
Any pitfalls to watch for?
Will appreciate your input on this
Mike

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

- SQL2000 ? (sql7 has more pitfalls)

- If you only want to move user-db, you might consider to use the copy-database-wizard. This will have unavailability of the db at the original server.

- you can also perform a workaround with creating a "workdb", granting all users that have permissions to you user-db public richts to your workdb.

Then copy-database-wizard your userdb with "copy only users from selected db", after it has compleded, restore a backup from your user-db-to-be-migrated. This way you don't have to struggle with master-db.

- msdb-recovery for jobs / DTS-packages ? Depends on the N° of jobs/packages.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

herbiechin Right there with Babe Points: 752 More actions · Answer 2

If it was a user-db, I would normally use the following syntax running from QA. This does not require the db to be offline which is a plus.

--Replace the folder locations to suit your environment

RESTORE DATABASE FROM DISK = 'D:\MSSQL7\BACKUP\LiveDatabase.DMP'

WITH REPLACE,

MOVE 'LiveDatabase_Data' TO 'D:\MSSQL7\Data\TestDatabase_Data.MDF',

MOVE 'LiveDatabase_Log' TO 'D:\MSSQL7\Data\TestDatabase_Log.LDF'

You have quite a few options. Good luck

Herb

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

correct, but what about sqluser-id's and their password ?

Windows-ids can be relinked with this script

SELECT 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomain**\' + su.name + ''')'

+ char(13) + ' begin '

+ char(13) + ' exec sp_grantlogin N''NtDomain**\' + su.name + ''''

+ char(13) + ' exec sp_defaultdb N''NtDomain**\' + + su.name + ''', N'''+ db_name() + ''''

+ char(13) + ' end'

FROM sysusers su

left join master.dbo.sysxlogins msu

on upper(su.name) = upper(msu.name)

WHERE su.sid > 0x00

ORDER BY su.name