Copy a SQL Server Database to Another Environment

-- -----------------------------------------------------------------------------
-- Copying a SQL Server Database to Another Environment
--
-- Article:
-- http://www.technologytoolbox.com/blog/jjameson/archive/2007/10/29/copying-a-sql-server-database-to-another-environment.aspx
-- -----------------------------------------------------------------------------

--
-- To create a backup with the COPY_ONLY option:
backup database WSS_Content
    to  disk = N'H:\WSS_Content.bak'
    with noformat,
        noinit,
        name = N'WSS_Content-Full Database Backup',
        skip,
        norewind,
        nounload,
        stats = 10,
        copy_only;

--
-- Returns a result set containing a list of the database and log files
-- contained in the backup set in SQL Server:
restore filelistonly
    from disk = N'E:\NotBackedUp\Temp\WSS_Content.bak';

-- To restore from a COPY_ONLY backup, use a command similar to the following:
--
-- NOTE that when copying a database from one environment to another, you often
-- need to use the MOVE option to specify the new location for the data and log
-- files (to account for different disk configurations and available disk space).
restore database WSS_Content_TEST
    from disk = N'E:\NotBackedUp\Temp\WSS_Content.bak'
    with file = 1,
    move N'WSS_Content'
        to N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST.MDF',
    move N'WSS_Content_Log'
        to N'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST_Log.LDF',
    nounload,
    stats = 10;