将SQL Server数据库复制到测试数据库的最简单方法是什么?

我总是备份它,然后恢复到另一个名称/文件集; How to: Restore a Database to a New Location and Name (Transact-SQL) 或者您可以创建一个空数据库,并使用恢复“向导”启用覆盖和更改恢复文件路径。

我只需要创建数据库,然后使用ss-mgmt-studio中的“导入数据”任务来复制数据。或者您可以备份生产数据库并将其还原到测试数据库中。

也许不是最简单的方法,但相当低调。您还可以将数据脚本化为一个文件,并将其回放到一个新的数据库中——这需要一段时间,但它对于版本控制之类的事情很方便,而且它是人类(好吧,“开发人员”)—可读的。

删除数据库(意味着将其脱机),复制MDF文件,然后重新附加原始文件和副本。

我经常使用具有完全相同模式的测试和实时数据库。它们通常具有存储过程,并根据开发更改的状态进行更改。所以我不能总是备份和恢复。我编写了一个查询,该查询循环访问数据库中的所有表,删除数据。然后再次循环并从活动数据库中插入。 在下面,我的测试数据库被称为WorkflowTest,而我的Live则被称为Workflow,但是您可以只替换变量中的数据库名称。只需确保连接到测试数据库。

但是表名和列是完全任意的。 我循环多次,因为我不想担心外键约束。某些删除/插入将失败,因为它期望数据存在于另一个表中。

我发现我所有的45张左右的桌子都在大约2-3个循环中被完全重新填充。

在插入循环期间,它首先通过尝试打开标识插入来检查表是否具有标识列。如果这没有失败,那么它将构建一个insert语句,其中前面的标识为“insert on”,后面的标识为“insert off”。必须在同一个exec语句中执行,因为exec中的命令在执行后超出范围。

事后看来,我想我可以将所有测试存储过程脚本化为alter语句,从实时数据库的备份中还原测试数据库,然后执行alter语句。但我发现用户安全设置无法正确还原,因此有时这也会带来麻烦。

            -- Gets a list of all tables in the Test database
            -- first loops through them and deletes all records.
            --   if it encounters an error, it does not remove that table from #tablesNeedingCopy so it will try again.
            --   this is because we don't know the order to delete and may encounter foreign key constraints.
            --   It usually deletes all records from all tables after 2 or so loops.

            -- the 2nd step is nearly identical, but instead it inserts the data
            Declare @CopyToDatabase varchar(100)
            declare @CopyFromDatabase varchar(100)

            set @CopyToDatabase = 'WorkflowTest'
            set @CopyFromDatabase = 'Workflow'

            use WorkflowTest -- [Connect to Database that you want to copy to]


            DECLARE @sqlCommand varchar(max)
            declare @columnNames varchar(max)
            DECLARE @tableName as NVARCHAR(100);
            DECLARE @tableNameCursor as CURSOR;

            create table #tablesNeedingCopy
            (
                Table_Name varchar(100)
            )


            insert into #tablesNeedingCopy
            (Table_Name)
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_TYPE = 'BASE TABLE' 
                and Table_Name not like 'sys%'

            declare @hasTableError as char(1)
            declare @remainingTableCount int
            declare @loopControl int
            set @loopControl = 0

            select @remainingTableCount = count(*)
            from #tablesNeedingCopy

            while (@remainingTableCount > 0 And @loopControl < 10)
            begin


                set @loopControl = @loopControl + 1

                SET @tableNameCursor = CURSOR FOR
                SELECT TABLE_NAME
                FROM #tablesNeedingCopy




                OPEN @tableNameCursor;
                FETCH NEXT FROM @tableNameCursor INTO @tableName;

                WHILE @@FETCH_STATUS = 0
                BEGIN

                set @hasTableError = 'N'


                SET @sqlCommand = 'Delete from ' + @tableName
                print @sqlCommand
                begin try
                    exec (@sqlCommand)
                end try
                begin catch 
                    set @hasTableError = 'Y'
                    print ERROR_MESSAGE()
                end catch



                if (@hasTableError = 'N')
                begin
                    -- otherwise leave the table in
                    delete from #tablesNeedingCopy
                    where Table_Name = @tableName
                end

                FETCH NEXT FROM @tableNameCursor INTO @tableName;
                END

                CLOSE @tableNameCursor;
                DEALLOCATE @tableNameCursor;

                select @remainingTableCount = count(*)
                from #tablesNeedingCopy

            end -- end while


            select @remainingTableCount = count(*)
                from #tablesNeedingCopy


            if (@remainingTableCount > 0)
            begin
            select Table_Name as DeleteTableNames 
            from #tablesNeedingCopy
            end

            delete from  #tablesNeedingCopy

            -------


            insert into #tablesNeedingCopy
            (Table_Name)
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_TYPE = 'BASE TABLE' 
                and Table_Name not like 'sys%'

            declare @hasIdentityColumn as char(1)
            set @loopControl = 0


            select @remainingTableCount = count(*)
            from #tablesNeedingCopy

            while (@remainingTableCount > 0 And @loopControl < 10)
            begin

                set @loopControl = @loopControl + 1

                SET @tableNameCursor = CURSOR FOR
                SELECT TABLE_NAME
                from #tablesNeedingCopy




                OPEN @tableNameCursor;
                FETCH NEXT FROM @tableNameCursor INTO @tableName;

                WHILE @@FETCH_STATUS = 0
                BEGIN

                set @hasTableError = 'N'
                set @hasIdentityColumn = 'Y'

                SET @sqlCommand = 'SET IDENTITY_INSERT ' + @CopyToDatabase + '.dbo.' + @tableName + ' ON;' -- Database to copy to
                begin try
                    print @sqlCommand
                    exec (@sqlCommand)
                end try
                begin catch 
                --print  ERROR_MESSAGE() 
                set @hasIdentityColumn = 'N'
                end catch


                if (@hasTableError = 'N')
                begin
                    SELECT  top 1 @columnNames =
                        STUFF((SELECT N', ' + Column_Name 
                                FROM INFORMATION_SCHEMA.COLUMNS AS t2 
                                WHERE t2.TABLE_NAME=t.TABLE_NAME 
                                FOR XML PATH,TYPE).value(N'.','nvarchar(max)'),1,2,'')
                    FROM INFORMATION_SCHEMA.COLUMNS t
                    WHERE TABLE_NAME = @tableName
                    order by ORDINAL_POSITION


                    set @sqlCommand = 'Insert into ' + @CopyToDatabase + '.dbo.' + @tableName + ' (' + @columnNames + ') select ' + @columnNames + ' from ' + @CopyFromDatabase + '.dbo.' + @tableName 

                    if (@hasIdentityColumn = 'Y')
                    begin
                    set @sqlCommand = 'SET IDENTITY_INSERT ' + @CopyToDatabase + '.dbo.' + @tableName + ' ON; ' + @sqlCommand + ' SET IDENTITY_INSERT ' + @CopyToDatabase + '.dbo.' + @tableName + ' OFF;'
                    end
                    print @sqlCommand
                    begin try
                        exec (@sqlCommand)
                    end try
                    begin catch
                        set @hasTableError = 'Y'
                        print ERROR_MESSAGE()
                    end catch
                end


                if (@hasTableError = 'N')
                begin
                    -- otherwise leave the table in
                    delete from #tablesNeedingCopy
                    where Table_Name = @tableName
                end

                FETCH NEXT FROM @tableNameCursor INTO @tableName;
                END

                CLOSE @tableNameCursor;
                DEALLOCATE @tableNameCursor;

                select @remainingTableCount = count(*)
                from #tablesNeedingCopy

            end -- end while

            select @remainingTableCount = count(*)
                from #tablesNeedingCopy


            if (@remainingTableCount > 0)
            begin
            select Table_Name as InsertTableNames 
            from #tablesNeedingCopy
            end

            drop table #tablesNeedingCopy