T-SQL Script to Clone a Read-Only Production Database to Editable Database with Filegroups

When working with SQL Server environments, a common requirement is creating an editable copy of a production database while maintaining the original as read-only. Here's a comprehensive T-SQL solution that automates the entire process.

The script performs three key operations:

Restores PROD backup to temporary name
Creates EDIT copy with proper filegroup handling
Sets original database to read-only

Here's the complete script with error handling for SQL Server 2008:

USE [master]
GO

DECLARE @BackupPath NVARCHAR(255) = 'C:\Backups\PROD_Full.bak'
DECLARE @ProdDBName NVARCHAR(128) = 'MyPROD_Database'
DECLARE @EditDBName NVARCHAR(128) = 'MyEDIT_Database'
DECLARE @TempDBName NVARCHAR(128) = 'TempRestore_' + CONVERT(NVARCHAR, GETDATE(), 112)

BEGIN TRY
    -- Step 1: Restore PROD backup to temporary name
    RESTORE DATABASE @TempDBName 
    FROM DISK = @BackupPath
    WITH 
        MOVE 'PROD_Data' TO 'C:\Data\' + @TempDBName + '_Data.mdf',
        MOVE 'PROD_Log' TO 'C:\Logs\' + @TempDBName + '_Log.ldf',
        REPLACE,
        STATS = 5
    
    -- Step 2: Generate dynamic SQL for file movement
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = '
    USE [master]
    RESTORE DATABASE [' + @EditDBName + '] 
    FROM DISK = ''' + @BackupPath + '''
    WITH '
    
    -- Add file movement clauses for all filegroups
    SELECT @SQL = @SQL + 
        'MOVE ''' + name + ''' TO ''C:\Data\' + @EditDBName + '_' + name + '.' + 
        CASE WHEN type = 0 THEN 'mdf' WHEN type = 1 THEN 'ldf' ELSE 'ndf' END + ''', '
    FROM sys.master_files
    WHERE database_id = DB_ID(@TempDBName)
    
    -- Complete the restore command
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ', REPLACE'
    
    -- Execute the EDIT database creation
    EXEC sp_executesql @SQL
    
    -- Step 3: Set original PROD to read-only
    SET @SQL = 'ALTER DATABASE [' + @ProdDBName + '] SET READ_ONLY WITH NO_WAIT'
    EXEC sp_executesql @SQL
    
    -- Cleanup temporary database
    SET @SQL = 'DROP DATABASE [' + @TempDBName + ']'
    EXEC sp_executesql @SQL
    
    PRINT 'Database copy operation completed successfully'
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
    DECLARE @ErrorState INT = ERROR_STATE()
    
    -- Attempt to clean up temp database if it exists
    IF DB_ID(@TempDBName) IS NOT NULL
    BEGIN
        EXEC('DROP DATABASE [' + @TempDBName + ']')
    END
    
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
GO

Filegroup Handling: The script dynamically handles all filegroups from the source database
Error Recovery: Proper cleanup is performed even if the operation fails
Performance: Uses direct restore operations instead of SMO for better performance

To schedule this as a regular job:

-- Create SQL Agent Job
USE [msdb]
GO

EXEC msdb.dbo.sp_add_job
    @job_name = N'Daily_PROD_to_EDIT_Copy',
    @enabled = 1
GO

-- Add job step with our script
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Daily_PROD_to_EDIT_Copy',
    @step_name = N'Copy Database',
    @subsystem = N'TSQL',
    @command = N'-- Our script here',
    @database_name = N'master'
GO

-- Schedule for daily execution
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'Daily_PROD_to_EDIT_Copy',
    @name = N'NightlySchedule',
    @freq_type = 4, -- Daily
    @freq_interval = 1,
    @active_start_time = 010000 -- 1AM
GO

When working with production databases, a common requirement is creating a writable copy of a read-only production database for development or editing purposes. The scenario becomes more complex when dealing with multiple filegroups, which introduces additional considerations during the copying process.

Here's a comprehensive script that automates the entire process from backup restoration to creating the editable copy:


-- Step 1: Restore the production database
USE [master]
GO

-- Declare variables for file paths
DECLARE @BackupFile NVARCHAR(255) = 'C:\Backups\PROD_Full.bak'
DECLARE @ProdDBName NVARCHAR(50) = 'MyPROD_Database'
DECLARE @EditDBName NVARCHAR(50) = 'MyEDIT_Database'
DECLARE @DataPath NVARCHAR(255) = 'C:\SQLData\'
DECLARE @LogPath NVARCHAR(255) = 'C:\SQLLogs\'

-- Generate dynamic SQL for restore with multiple filegroups
DECLARE @RestoreSQL NVARCHAR(MAX) = 
'RESTORE DATABASE [' + @ProdDBName + '] FROM DISK = ''' + @BackupFile + ''' WITH '

-- Get file list from backup to handle multiple filegroups
CREATE TABLE #FileList (
    LogicalName NVARCHAR(128),
    PhysicalName NVARCHAR(260),
    Type CHAR(1),
    FileGroupName NVARCHAR(128),
    Size NUMERIC(20,0),
    MaxSize NUMERIC(20,0),
    FileId INT,
    CreateLSN NUMERIC(25,0),
    DropLSN NUMERIC(25,0),
    UniqueId UNIQUEIDENTIFIER,
    ReadOnlyLSN NUMERIC(25,0),
    ReadWriteLSN NUMERIC(25,0),
    BackupSizeInBytes BIGINT,
    SourceBlockSize INT,
    FileGroupId INT,
    LogGroupGUID UNIQUEIDENTIFIER,
    DifferentialBaseLSN NUMERIC(25,0),
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    IsReadOnly BIT,
    IsPresent BIT
)

INSERT INTO #FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + '''')

-- Build MOVE statements for each file
DECLARE @MoveSQL NVARCHAR(MAX) = ''
SELECT @MoveSQL = @MoveSQL + 
    ', MOVE ''' + LogicalName + ''' TO ''' + 
    CASE 
        WHEN Type = 'L' THEN @LogPath + @ProdDBName + '_' + LogicalName + '.ldf'
        ELSE @DataPath + @ProdDBName + '_' + LogicalName + '.mdf'
    END + ''''
FROM #FileList

SET @RestoreSQL = @RestoreSQL + ' FILE = 1' + @MoveSQL + ', REPLACE, STATS = 10'

-- Execute the restore
EXEC sp_executesql @RestoreSQL
PRINT 'Successfully restored production database'

-- Step 2: Create the editable copy
DECLARE @CopySQL NVARCHAR(MAX) = 
'CREATE DATABASE [' + @EditDBName + '] AS COPY OF [' + @ProdDBName + ']'
EXEC sp_executesql @CopySQL
PRINT 'Successfully created editable copy'

-- Step 3: Set original database to read-only
DECLARE @ReadOnlySQL NVARCHAR(MAX) = 
'ALTER DATABASE [' + @ProdDBName + '] SET READ_ONLY WITH NO_WAIT'
EXEC sp_executesql @ReadOnlySQL
PRINT 'Set production database to read-only'

-- Clean up
DROP TABLE #FileList

For SQL Server 2005 and 2008 environments, we need to pay special attention to filegroups. The script above automatically handles multiple filegroups by extracting file information from the backup and applying appropriate MOVE statements.

To fully automate this process:

Schedule the script using SQL Server Agent
Add error handling with TRY/CATCH blocks
Include validation checks for existing databases
Consider adding transaction logging

For very large databases where storage is a concern, you might consider this optimized version:


-- Directly restore to the EDIT database with different file locations
USE [master]
GO

DECLARE @BackupFile NVARCHAR(255) = 'C:\Backups\PROD_Full.bak'
DECLARE @EditDBName NVARCHAR(50) = 'MyEDIT_Database'
DECLARE @DataPath NVARCHAR(255) = 'C:\SQLData\EDIT_'
DECLARE @LogPath NVARCHAR(255) = 'C:\SQLLogs\EDIT_'

-- Restore directly to EDIT database with new file paths
RESTORE DATABASE [@EditDBName] 
FROM DISK = @BackupFile
WITH MOVE 'PROD_Data' TO @DataPath + 'EDIT_Data.mdf',
MOVE 'PROD_Log' TO @LogPath + 'EDIT_Log.ldf',
MOVE 'FG1_Data' TO @DataPath + 'EDIT_FG1.ndf',
REPLACE, RECOVERY

PRINT 'Directly restored editable copy with new file paths'

ServerDevWorker

T-SQL Script to Clone a Read-Only Production Database to Editable Database with Filegroups

Related Articles