October 5, 2007 at 1:15 am
I'm wondering on what's the best way of moving an instance, with all databases and logins/users from one server to another?
They are both running Sql Server 2005, sp2.
Is the best way just to move the backups and restore them on the new server?
What about snapshots, is it possible to take snapshots and "restore" them on the new one?
Or is there another way that I haven't thought about?
Any input would be appreciated!
Svein Erik.
October 5, 2007 at 5:19 am
Hi
There are different ways of doing this. Some of them are
1) Backup - Restore
2) Attach
3) Script the db, run the script in the other server and port the data.
I would suggest that if your database size is not large you can copy the mdf and ldf files to the second server and do a attach.
"Keep Trying"
October 5, 2007 at 5:31 am
Thanks!
But what about the logins and users of the databases? How can i move them as well?
October 5, 2007 at 6:23 am
Database users are stored within the database itself. So when you move the database, you'll have the user. To transfer logins in SQL Server 2005, see the following KB article:
How to transfer the logins and the passwords between instances of SQL Server 2005[/url]
There's a couple of stored procedures you'll need to add that will allow you to extract the logins with SIDs and passwords (kept encrypted) and then you can use that information to recreate the logins on the new server.
K. Brian Kelley
@kbriankelley
October 5, 2007 at 6:35 am
A million thanks! 🙂
October 5, 2007 at 7:47 am
Greetings,
The best solution is to use the copy database wizard (right click on the database, task, copy database).
Do that for each database, so you'll have moved the data, jobs, and logins (logins = instance, users=database).
Detaching and re-attaching each database will work, but the users won't be moved and you'll have to script them out. Moreover, you can have issues with moving the MSDB. I have been there, done that and spent a few nights at work instead of at home...
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply