I need to copy my production database to QA for testing. What's the best way to do this and not have downtime in production.
asked
Copy Database to QA
Backup and restore is the quickest way to get production down to QA. A backup process on your production system will not take it offline. If you have regularly scheduled backups (and you should) you can even use one of those rather than running an ad hoc backup.
But you can't stop there. You'll need to make sure you change the security settings on the database so that the QA team can login and get at the data. You may also want to run some type of data clean-up on the database after the restore in order to hide any sensitive company information. Because production data is subject to change you may also want to create some known data for the testing team and load that as part of the restore process.
Restore from backup. It also makes sure your backup is healthy and that you have something you can switch over to in case if disaster.
You can use the above.. Or the Copy DB process.
I would go for the backup/restore so it test the state of the bkup (using the prev backup + any diffs/logs).
agree with Rob Farley in copying a backup.. you have also the option to use SSIS packages to move everything.. but I also want to suggest you to take a look into RedGate's tools: SQL Compare and SQL Data Compare, these 2 really save me a lot of time when copying databases from the several environments we have
