I am frequently asked to refresh development databases with production data. The usual way to do this is to back up the production database and then restore over the development database. SQL Server 2005 has a new backup option, “WITH COPY_ONLY”. This option allows you to perform a full backup without truncating the log and breaking the log chain. The COPY_ONLY option is not supported in the SQL Server Management Studio (SSMS) GUI, so you have to perform the backup via a script.
It’s important to note that a backup that is created with the COPY_ONLY option cannot be restored with the SSMS GUI. Instead you have to restore via a script.
SQL Server 2008 supports the COPY_ONLY option in the SSMS GUI.
