Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shebert
Yak Posting Veteran
85 Posts |
 Posted - 2012-08-27 : 11:20:21
|
| HelloI am moving my databases to a brand new server is there any reason to do it one way or the other?way 1 i create a new database name it and restore from .bak file.Way 2 i detach the database on the old server and copy it to the new server and attach it.i know i need to create all the logins on the new server.is there an easy way set securities back up?Thanks in advance. |
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2012-08-27 : 11:39:23
|
| One advantage I've found in using your way #1 is that if I need to rollback I still have the old database in its previously existing state. You do need to be careful to inactivate the database after you take your last backup so no new data goes in that isn't transferred to your new server. But as far as actually bringing up the databases on the new system there shouldn't be a difference.Just a question, are you upgrading your version of SQL Server during this migration to a new server? If so, make sure to check out stuff migrating to a newer version of SQL Server, there are tons of good sites that detail the precautionary steps to take when upgrading. HTH.-NifflerX |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-27 : 11:42:39
|
| No need to crreate the new database - just do the restore.You need to do a backup beforethe detach anyway (just in case) so may as well copy that and restore - safer as the old database is still avaiklable.Only time I might not do this is if there wasn't room for the backup and the database on the destination.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
shebert
Yak Posting Veteran
85 Posts |
Posted - 2012-08-27 : 12:18:05
|
quote:
Originally posted by nigelrivett
No need to crreate the new database - just do the restore.You need to do a backup beforethe detach anyway (just in case) so may as well copy that and restore - safer as the old database is still avaiklable.Only time I might not do this is if there wasn't room for the backup and the database on the destination.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Thanks, any suggestions on setting up securty on new server to mimic the old server? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-08-27 : 14:40:15
|
| Search for sp_help_revlogin - you can use that to create the logins on the new server with the same SID's and passwords. If you create the logins on the new server with new SIDs the users in the databases will be orphaned and you will then need to run sp_change_users_login to link the users to the appropriate login. |
|
|
|
|
|
|
Moving DB to New server, Best Practices