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
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.