How To Move/copy All Databases With Users, Schema And Roles From One Server To Another

I need to move/copy all of my databases (only 5) from one server to another. I know how to make backup and restore but I also need to move all information of my databases including

Solution 1:

1. Right click on the DB
2. Click on tasks
3. Click on generate scripts
4. Go through the wizard and select your tables
5. On the options page click the Advanced button (It's there, just doesn't stand out very well)
6. Change the "Types of data to script" option

There are options to script data and logins (a ton of other stuff). It's very handy.

Here's the script that will generate a script to add all roles on all databases

declare@selectStatementvarchar(1000)

set@selectStatement='Use ?;SELECT ''USE ?''; SELECT '+'''EXECUTE sp_AddRoleMember '''''+''' + roles.name + '''''''+','''''+''' +  Replace(users.name,''DOMAINA'',''DOMAINB'') + '''+'''''''
     from sys.database_principals users
    inner join sys.database_role_members link
    on link.member_principal_id = users.principal_id
   inner join sys.database_principals roles
     on roles.principal_id = link.role_principal_id'--where users.name like ''%%'''EXEC sp_MSForEachDB @selectStatement;

Solution 2:

In general there are two possibilities to copy database:

1 BACKUP-RESTORE In this case you will have some probles main of which is After you move databases to a new server, users may not be able to log in to the new server. A solution of this problem is provided here:

How to transfer logins and passwords between instances of SQL Server

2 DETACH-COPY-ATTACH it's just copying (or moving) of database files to another server.

About pros and cons of each approach you can read here SQL Server backup/restore v.s. detach/attach

Solution 3:

I would suggest using Red Gate's SQL Compare for this task. I cost a little bit but it is worth of every penny. Note that SQL Compare does not migrate physical databases, only the content of the databases.