Skip to main content

Copy a SQL Server Database to Another Environment.

-- -----------------------------------------------------------------------------
-- Copying a SQL Server Database to Another Environment
-- Article:
-- -----------------------------------------------------------------------------

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

-- 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',
    stats = 10;