Easiest way to make copy SQL Server DB to test DB?

What is the easiest way to take a SQL Server database and make a test copy of it?

I have looked through some existing topics but not sure if there is an easier way. I have database publisher. I want to be able to keep both database on the same server, potentially.

Update: I used the Microsoft SQL Server Publishing Wizard to script to file, create a new database, add "u开发者_StackOverflow社区se db", and execute the script. This seemed to work okay. The attach/detach is not recommended because it leaves links back to the original location if it is a full text search database or if the log is missing.

---

I always just back it up then restore to a different name/file set; How to: Restore a Database to a New Location and Name (Transact-SQL) or you can creater an empty db & use the restore "wizard" enabling OVERWRITE and changing the restore file paths.

---

I would just create the database, then use the "Import Data" task in SS Mgmt Studio to copy the data over. Or you could back up the production database and restore it into the test database.

Maybe not the absolute easiest ways, but pretty low-drama. You can also script the data to a file and play that back into a new database -- that takes awhile, but it's handy for things like version control, and it's human (well, "developer")-readable.

---

Detatch the database (means taking it offline), copy the mdf file, and then re-attach both the original and the copy.

---

I often have Test and Live databases with the exact same schema. They usually have stored procedures with changes depending on the state of my development changes. So I can't always just backup and restore. I wrote a query that loops through all tables in a database, deletes the data. Then loops again and inserts from a live database. In the below, my test database is called WorkflowTest and my live is called Workflow, but you can just replace the database names in the variables. Just make sure to connect to the TEST database.

But the table names and columns are completely arbitrary. I loop multiple times because I don't want to worry about foreign key constraints. Some deletes/inserts will fail because it expects data to exist in another table.

I find that all 45 or so of my tables get fully repopulated in about 2-3 loops.

During the Insert loop, it first checks if the table has an identity column by attempting to turn IDENTITY_INSERT on. If this doesn't fail, then it will build out an insert statement with a preceding IDENTITY_INSERT On and a following IDENTITY_INSERT off. It has to be done in the same EXEC statement because the commands in the EXEC fall out of scope after they are executed.

In hind-sight, I guess I could have scripted out all of my test stored procedures as alter statements, restored the test database from a backup of the live one, then executed my alter statements. But I find that user security settings don't restore properly, so sometimes that can be a hassle too.

            -- 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