Transfer SQL Database
Posted on 2003-11-26
I need to setup a test environment for my live database running on SQL Server 2000.
I know I can use BackUp and Restore to copy the database to my test Server but how do I successfully transfer all the Logins and Users ?
I know the Logins are stored in the master database and the users are stored in the User database (lets call it MyDb).
If I restore MyDb to the test server the Users and Roles are created but the logins are not and so the SUID does not match so my users are 'Orphaned'.
So If I restore the Master database from the Live server to the Test server and then restore Mydb to the Test server will the SUID's match up ?
The Logins will be using integrated security. My Admin guy will setup a Test environment to match the Live environment exactly - same domain, server name, Windows Users etc..
The restore will happen on a regular basis and will completely overwrite the Test database.
Is there a better way to achieve this ?
What effect\implications are there when restoring the Master database from one sever to another server ?
Any Help and/or Advice is gratefully acceopted.