I am amidst of migrating an SQL Server 2005 database to an SQL Server 2012. Both environments are on different servers, and they both have different separated disks for the datafiles and logfiles.
My question is, what would be the pros and cons of detaching the databases, copying the datafiles and logfiles to the new server volumes and attaching them to the new MSSQL 2012 instance (keeping the old datafiles and logfiles in the 2005 instance in case of failure) versus doing a full backup and a restore to the new instance?
I can't seem to find the exact benefits of doing a backup-restore instead of a detach/attach in this two-server, two-disk scenario.
Thanks
Best Answer
The usual reason to go with backup/restore is that no downtime for the source database is needed.
In my opinion, if you can have a downtime, detach/attach will be perfectly fine.
If you talk about big database files (~ >= 100 GB) and time is an issue, backup/restore could be faster because only used pages of a database file are backed up, whereas detach/copy/attach will copy the whole database file, and even the complete logfile, whether their usage is 1 or 99%. In the case of SQL 2008 and later as the source system, backup compression would also play a role in shortening the total time needed.