Copy database- will it create users and logins too?

[glgcag1991]

Newbie question:

I have a production server that has SQL Server 2008 Express on it and I want to copy the database to a dev server.

First, the two servers cannot see each other, how do I do it if they are on separate networks?

Second, if I do a database copy, will all the users and logins be copied as well?

 

[markros]

Backup/Restore or Attach/Detach does not copy Users and Logins. You'll need a separate script to re-create all users/logins.

PluralSight Learning Library

 

[mrdenny]

Backup/Restore or Detach/Attach will copy the users, it will not copy the logins. Google for sp_help_revlogin for a SQL Script which will create a script to create the logins on the other server with the same SID as the production server.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[glgcag1991]

Thanks Markros and MrDenny for the replies!

MrDenny, I looked at the sp_help_revlogin and it seems simple enough, but just so I know, is this what I should expect when developing? Do db admins that have to work on many remote sql servers have tools that better replicate the data, etc. between a dev and production server? (I guess it's not too much work, but somehow I thought it would be a little more automated.)

Also, what about service accounts? Is there a standard set of service accounts that I can rely on or do I need to ask about the service accounts in order to duplicate the service account settings on my dev box?

Thanks for your patience while I'm trying to get my head wrapped around this process!

 

[mrdenny]

Replicating logins is different than replicating normal data. There are processes in place for replicating data, called SQL Server replication that can replicate data from one server to another.

There is no standard set of service accounts. Those accounts would need to be created by someone and setup for use of the dev and production systems.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog