I have to move/copy all my databases(that are only 5) in one server to a different. I understand how to create backup and restore however i should also move information of my databases including customers, schema, etc.
- Right click the DB
- Click tasks
- Click generate scripts
- Feel the wizard and choose your tables
- Around the options page click on the Advanced button (It's there, just does not stick out perfectly)
- Alter the "Kinds of data to script" option
You will find choices to script data and logins (a lot of other things). It is extremely handy.
Here's the script which will produce a script to include all roles on all databases
declare @selectStatement varchar(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;
Generally you will find two options copying database:
Within this situation you'll have some probles primary which is
After you move databases to a new server, users may not be able to log in to the new server. An answer of the issue is provided here:
2 DETACH-COPY-ATTACH it is simply copying (or moving) of database files to a different server.
About benefits and drawbacks of every approach read here SQL Server backup/restore v.s. detach/attach
I recommend using Red-colored Gate's SQL Compare with this task. I cost a bit but it's price of every cent. Observe that SQL Compare doesn't migrate physical databases, just the content from the databases.