Copying Production Databases to Development Environment

Sometimes when you need to debug a production bug which can't be replicated on a development machine its very useful to get the production database(s) on the development server.

Our infrastructure was rather complex since we used more than one database, one for each section of the software with a common database for the common data.

First, I needed a way of deleting a database with minimal resistance, so taking the database offline before deleting it assured 99% of the time that it will go through the deletion.

ALTER DATABASE [DBName_Development]
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DBName_Development]
SET ONLINE
GO
DROP DATABASE [DBName_Development]
GO

Then I needed to backup the production database without affecting the backup set, hence the COPY_ONLY

BACKUP DATABASE DBName_Production
TO DISK = 'C:\TempBackup\DBName.bak'
WITH INIT,COPY_ONLY
GO

Then, we need to restore it on the development server, if the paths are a bit different the restore tsql needs some modifications, so this stored procedure was born:

-- =============================================
-- Author:        Dror Gluska
-- Create date: 2010-05-30
-- Description:    Restores databases with multiple files to specific destination
-- =============================================
CREATE PROCEDURE tuspTempRestoreDatabase
    @backupfilename nvarchar(255),
    @restorepath nvarchar(255),
    @dbname nvarchar(255)
AS
BEGIN

    Declare @LogicalName nvarchar(128)
    Declare @PhysicalName nvarchar(128)
    Declare @FType char(1)
    Declare @FileId int


    declare @dinfo table (LogicalName nvarchar(255),
        PhysicalName nvarchar(255),
        Type nvarchar(255),
        FileGroupName nvarchar(255),
        Size int,
        MaxSize float,
        FileId int,
        CreateLSN int,
        DropLSN int,
        UniqueId uniqueidentifier ,
        ReadOnlyLSN int,
        ReadWriteLSN int,
        BackupSizeInBytes int,
        SourceBlockSize int,
        FileGroupId int,
        LogGroupGUID uniqueidentifier ,
        DifferentialBaseLSN float,
        DifferentialBaseGUID uniqueidentifier ,
        IsReadOnly int,
        IsPresent int,
        TDEThumbprint int);

    declare @sql nvarchar(4000);
    set @sql = 'RESTORE FILELISTONLY from disk = ''' + @backupfilename + ''''
      
    insert into @dinfo 
    execute sp_executesql @sql

    set @sql = 'restore database ' + @dbname + ' from disk = ''' + @backupfilename + ''' with '

    Declare c1 cursor for
    select LogicalName,PhysicalName,Type,FileId
    from @dinfo
    order by Type

    open c1

    fetch next from c1
    into @LogicalName,@PhysicalName, @FType, @FileId

    while @@fetch_status = 0
    begin
        if(@FType = 'D')
        begin
            set @sql = @sql + char(9) + ' Move ''' + @LogicalName + ''' to ''' + @restorepath + '\' + @dbname + '_data_' + cast(@FileId as nvarchar(10)) + '.mdf''' + char(13)
        end
        else
        begin
            set @sql = @sql + char(9) + ' Move ''' + @LogicalName + ''' to ''' + @restorepath + '\' + @dbname + '_log_' + cast(@FileId as nvarchar(10)) + '.ldf''' + char(13)
        end

        fetch next from c1
        into @LogicalName,@PhysicalName, @FType, @FileId

        if (@@FETCH_STATUS = 0)
        begin
            set @sql = @sql + ',';
        end
    end
    close c1
    deallocate c1

    print @sql
    execute sp_executesql @sql
END
GO

Its asking for files list of the backup file, then writing a specific restore tsql with all these files to the selected directory name and the database name. here's an example:

exec tuspTempRestoreDatabase 'C:\TempBackup\DBName.bak', 'C:\Databases\DBName_Dev', 'DBName_Development'

As I stated before, this configuration had multiple databases, each held its own section but the tricky part was that it used cross databases stored procedures, function and views, so just restoring the databases with different names and location wouldn't do it as all cross referencing scripts would stop working. 

The simplest solution I could find for it is go over all the scripts and modify the database names.

The first component of this stage is getting all the stored scripts and their bodies:

-- =============================================
-- Author:      Dror Gluska
-- Create date: 2010-05-30
-- Description: Gets all Views/StoredProcedures and references outside the current database
-- =============================================
CREATE PROCEDURE tuspGetAllExecutables
AS
BEGIN
    SET NOCOUNT ON;

    declare @retval table (name nvarchar(max), text nvarchar(max), refs int);

    declare @tmpval nvarchar(max);

    declare @tmpname nvarchar(max);

    declare @ref table(
        ReferencingDBName nvarchar(255),
        ReferencingEntity nvarchar(255),
        ReferencedDBName nvarchar(255),
        ReferencedSchema nvarchar(255),
        ReferencedEntity nvarchar(255)
     );
    declare @refdata table (DBName nvarchar(255), Entity nvarchar(255), NoOfReferences int);
     
    insert into @ref
    select DB_NAME() AS ReferencingDBName, OBJECT_NAME(referencing_id) as ReferencingEntity, referenced_database_name, referenced_schema_name, referenced_entity_name
    FROM sys.sql_expression_dependencies




    insert into @refdata
    select     ReferencingDBNAme,
             ReferencingEntity,
             SUM(NoOfReferences) as NoOfReferences
    from
    (
        select *
        , (
            select COUNT(*)
            from @ref r2
            where r2.ReferencedEntity = [@ref].ReferencingEntity
            and r2.ReferencedDBName = [@ref].ReferencingDBName
          )
          as NoOfReferences

        from @ref
    ) as refs
    group by ReferencingDBNAme,   ReferencingEntity
    order by NoOfReferences



    declare xpcursor CURSOR for
    SELECT name
    FROM syscomments B, sysobjects A
    WHERE    A.[id]=B.[id]
        and xtype in ('TF','IF','P','V')
    group by name
    order by name

    open xpcursor
    fetch next from xpcursor into @tmpname

    while @@FETCH_STATUS = 0
    begin
        set @tmpval = '';

        select @tmpval = @tmpval + text
        FROM syscomments B, sysobjects A
        WHERE   A.[id]=B.[id]
            and A.name = @tmpname
        order by colid

        insert into @retval
        select @tmpname, @tmpval, (select top 1 NoOfReferences from @refdata where [@refdata].Entity = @tmpname)

        fetch next from xpcursor into @tmpname
    end

    close xpcursor
    deallocate xpcursor

    select * from @retval
    order by refs
 
END
GO

Then, I needed a stored procedure that will give me all the indexes created on schemabound views (but it works on tables too):

-- =============================================
-- Author:        thorv-918308
-- Create date: 2009-06-05
-- Description:    Script all indexes as CREATE INDEX statements
-- Copied from http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx#bm879833
-- =============================================
CREATE PROCEDURE tuspGetIndexOnTable
 @indexOnTblName nvarchar(255)
AS
BEGIN
    --declare @indexOnTblName nvarchar(255) = 'test'

    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    --1. get all indexes from current db, place in temp table
    select
        tablename = object_name(i.id),
        tableid = i.id,
        indexid = i.indid,
        indexname = i.name,
        i.status,
        isunique = indexproperty (i.id,i.name,'isunique'),
        isclustered = indexproperty (i.id,i.name,'isclustered'),
        indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')
        into #tmp_indexes
    from sysindexes i
    where i.indid > 0 and i.indid < 255                                             --not certain about this
    and (i.status & 64) = 0                                                                 --existing indexes

    --add additional columns to store include and key column lists
    alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)

    --2. loop through tables, put include and index columns into variables
    declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

    declare index_cursor cursor for
    select tableid, indexid from #tmp_indexes

    open index_cursor
    fetch next from index_cursor into @tableid, @indexid

    while @@fetch_status <> -1
    begin

    select @isql_key = '', @isql_incl = ''

    select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
     --key column
     @isql_key = case ic.is_included_column
       when 0 then
         case ic.is_descending_key
           when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
           else            @isql_key + coalesce(sc.name,'') + ' ASC, '
         end
       else @isql_key end,
                       
     --include column
     @isql_incl = case ic.is_included_column
       when 1 then
         case ic.is_descending_key
           when 1 then @isql_incl + coalesce(sc.name,'') + ', '
           else @isql_incl + coalesce(sc.name,'') + ', '
         end
       else @isql_incl end
    from sysindexes i
    INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
    INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id
       
    where i.indid > 0 and i.indid < 255
    and (i.status & 64) = 0
    and i.id = @tableid and i.indid = @indexid
    order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end

       
    if len(@isql_key) > 1   set @isql_key   = left(@isql_key,  len(@isql_key) -1)
    if len(@isql_incl) > 1  set @isql_incl  = left(@isql_incl, len(@isql_incl) -1)

    update #tmp_indexes
    set keycolumns = @isql_key,
     includes = @isql_incl
    where tableid = @tableid and indexid = @indexid

    fetch next from index_cursor into @tableid,@indexid
    end

    close index_cursor
    deallocate index_cursor

    --remove invalid indexes,ie ones without key columns
    delete from #tmp_indexes where keycolumns = ''

    --3. output the index creation scripts
    set nocount on

    --create index scripts (for backup)
    SELECT
        'CREATE '
        + CASE WHEN ISUNIQUE    = 1 THEN 'UNIQUE ' ELSE '' END
        + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
        + 'INDEX [' + INDEXNAME + ']'
        +' ON [' + TABLENAME + '] '
        + '(' + keycolumns + ')'
        + CASE
             WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN ''
             WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)'
             WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
             WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'
             ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'
          END collate database_default as 'DDLSQL'
    FROM #tmp_indexes
    where left(tablename,3) not in ('sys', 'dt_')   --exclude system tables
    and tablename = @indexOnTblName
    order by tablename, indexid, indexname


    set nocount off

    drop table #tmp_indexes
END

And finally I needed something to connect those two components into a solution:

declare @codetext nvarchar(max);
declare @newcodetext nvarchar(max);
declare @idxcode nvarchar(max)
declare @tablename nvarchar(255);
declare @indextable table (DDLSQL nvarchar(max));

--get all executables
declare @executables table(name nvarchar(max), text nvarchar(max),refcount int);
insert into @executables
exec tuspGetAllExecutables

declare spcursor CURSOR for
select text,name
from @executables
order by refcount

open spcursor
fetch next from spcursor into @codetext, @tablename

--for each executable 
while @@FETCH_STATUS = 0
begin
    set @newcodetext = @codetext;

    --modify the database name
    set @newcodetext = REPLACE( @newcodetext,'DBName_Production.','DBName_Development.')
    --etc' etc' etc' 

    --check if we actually modified the contents of the script, otherwise, no need to alter it.
    if (@newcodetext != @codetext)
    begin
        --modify CREATE to ALTER
        set @newcodetext = REPLACE( @newcodetext,'CREATE FUNCTION','ALTER FUNCTION')
        set @newcodetext = REPLACE( @newcodetext,'CREATE PROCEDURE','ALTER PROCEDURE')
        set @newcodetext = REPLACE( @newcodetext,'CREATE VIEW','ALTER VIEW')

        --get all indexes on script, if its a schemabound view, it will get all the indexes on that view, otherwise empty
        insert into @indextable
        exec tuspGetIndexOnTable @tablename

        print @tablename
        print @newcodetext
        EXECUTE sp_executesql @newcodetext

        --recreate lost index for schemabinded views
        if (select COUNT(*) from @indextable) > 0
        begin

            declare vidxcursor cursor for select DDLSQL from @indextable
            open vidxcursor
            fetch next from vidxcursor into @idxcode

            while @@FETCH_STATUS = 0
            begin
                print @idxcode
                EXECUTE sp_executesql @idxcode
                fetch next from vidxcursor into @idxcode
            end
            close vidxcursor
            deallocate vidxcursor

        end
    end
    fetch next from spcursor into @codetext, @tablename
end

close spcursor
deallocate spcursor

In the end, I needed to delete user emails from the databases so test won't cause the application to start emailing users, but you might have your own development practices.

And finally:

print 'Done!'

Tags: