SQL Server 2012 Enterprise: Copy Databases to New SQL Server, Same Domain, Users, Etc

Question

Am having trouble with what seemingly should be an easy process. I'm a SQL Server n00b, so please be gentle and very explanatory with your answers.

Currently, I have a SQL Server 2012 cluster (running on top of Windows Server 2008R2), and have created a new SQL Server 2012 Enterprise server in the same AD domain that I want to move all my databases to so I can start using this new instance (its running all SSD drives for better performance with databases, logs, temp logs, etc, all on different 512GB SSD drives). The new instance uses the same AD group as SQL administrators, so logins are the same.

I want to copy all the databases from the "old" SQLCluster to the new SQL instance (the new instance is standalone). I've tried the Copy Database Wizard, making sure firewalls are stopped on both instances (seems easiest to avoid conflicts), telling it to make a text log file at the root of C: drive in case anything goes wrong. However, the wizard gets to the last step (running the job) and bombs out, but doesn't create a log file ... it just says the job failed, giving me little to go on. Both instances have the SQL Agent running.

Can someone give me some guidance on the easiest way to do this while leaving the databases on the source cluster online? Note that I also have hourly backups that run via R1Soft that make copies of the database and log files; I thought perhaps I could just dump the MDF and LDF files from this backup in a directory and re-attach them, but the file structure is different on the new SQL Server 2012 machine with log files and DB files on different drives, and I get an error there with that, and don't know how to change those paths to reattach the databases from the backup (they are straight images of the MDF/LDF files, NOT SQL Server backups).

Thanks for any help you can provide.

Answer 1

Run this on your current instance:

select
    'CREATE LOGIN ['+p.name+'] FROM WINDOWS;'
from sys.server_principals as p
where type in ('U','G')

Take it's output and run it on the new instance. Those AD accounts will now have logins on both instances. (This is a good opportunity to audit your access and ensure all those users really are needed.)

Backup your databases (a proper SQL Server backup, not a copy of mdf and ldf files) and restore these to the new instance. You may have to get creative with the restores if the drive names have changed. If there are a lot of DBs some RESTORE FILELISTONLY and a script will save much time.

Finally, on each DB, script an ALTER USER to reconcile LOGINS to USERS. This can be meta-scripted as above.

Test connections to the new instance. Update the applications' connection strings. Take the old DBs offline and, after a few days, tidy away the old server.