Sql-server – How to create a fresh copy of a large database on SQL Server

Taking into account a 400GB database, its upto you to choose any of the below routes :

Method 1 : Backup and Restore

Depending on your hardware and the amount of activity going on, it will be slower than Method 2 - BCP OUT / BCP IN

Below is the script that will help you :

/************************************************************************************************************************************************
Author      :   KIN SHAH    
Purpose     :   Restore database on the same server with different Name
DATE        :   06-03-2013

Note        :   1. Change as per your environment by replacing the bits marked by "--- CHANGE HERE !!"
                2. If you are using SQL 2008 and up depending on Enterprise or Standard Edition, you can add "COMPRESSION" to the backup command as well.
                3. As a cleanup task, this script requires xp_cmdshell to delete the backup file on the server. I have commented that out for now.
************************************************************************************************************************************************/
declare @dbname NVARCHAR(MAX)

set @dbname = 'test1' --- CHANGE HERE !!

if object_id('tempdb..#temp1') is not null
            begin
            drop table #temp1
            END
if object_id('tempdb..#temp2') is not null
            begin
            drop table #temp2
            END
if object_id('tempdb..#temp3') is not null
            begin
            drop table #temp3
            END

 --backup path goes here
DECLARE @path NVARCHAR (MAX)
-- Here the path is hard-coded  as all the server has 'D:\2restore' folder. This can be made as input parameter also !
SET @path = 'C:\test' --- CHANGE HERE !!
 -- generates Archive database name 
DECLARE @archivedbname NVARCHAR(MAX)
SET @archivedbname = @dbname+'_Archive'+'_'+DATENAME(MONTH, GETDATE())+'_'+ CAST(DATEPART(YEAR, GETDATE()) as VARCHAR(MAX))
--PRINT @archivedbname

-- check that the database to be archived is  there and is not a system database ...

IF  @dbname in (SELECT NAME FROM MASTER..sysdatabases WHERE DB_ID(NAME)>4)
    BEGIN
        select 'The database is correct. starting Archiving Process .....'
            BEGIN TRY
                        select * INTO #temp1
                            FROM MASTER.sys.master_files
                            WHERE database_id = cast(DB_ID(@dbname) AS NVARCHAR(MAX))

                    -- now get the logical and physical names of the database to be archived
                    -- type 0 = data
                    CREATE TABLE #temp2 (ldata NVARCHAR(MAX), pdata NVARCHAR(max))
                    DECLARE @ldata NVARCHAR(MAX)
                    DECLARE @pdata NVARCHAR(MAX)
                    SELECT  @ldata = 'select [name],[physical_name] from #temp1 where  type = 0 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                    INSERT INTO #temp2
                    EXEC (@ldata)

                    SELECT @ldata = ldata FROM #temp2
                    --PRINT @ldata
                        SELECT @pdata = pdata FROM #temp2
                        SELECT @pdata = left(@pdata, len(left(@pdata,LEN(@pdata)-4))-len(@ldata))+@archivedbname+'.mdf'
                        --PRINT @pdata
                        -- type 1 = log
                        CREATE TABLE #temp3 (llog NVARCHAR(MAX), plog NVARCHAR(max))
                        DECLARE @llog NVARCHAR(MAX)
                        DECLARE @plog NVARCHAR(MAX)
                        SELECT  @llog = 'select [name],[physical_name] from #temp1 where  type = 1 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                        INSERT INTO #temp3
                        EXEC (@llog)
                        SELECT @llog = llog FROM #temp3
                        --PRINT @llog
                        SELECT @plog = plog FROM #temp3
                        SELECT @plog = left(@plog, LEN(left(@plog,LEN(@plog)-4))-LEN(@llog))+@archivedbname+'_log.ldf'
                        --PRINT @plog

                        -- now we will take backup of the database that is specified ....
                        select 'Taking backup of database ' + @dbname

                        DECLARE @sql NVARCHAR (MAX)
                        -- use compression using Redgate backup 
                        select @sql = 'backup database '+@dbname+' to disk ='''+@path+ '\'+ @dbname+'_FULL_'+convert(varchar(10),getdate(),112)+'.bak'+''' with init, stats = 10'

                        --print @sql
                        exec(@sql)

                        select 'The backup is done for ' + @dbname
            END TRY
            BEGIN CATCH

                    SELECT
                        ERROR_NUMBER() AS ErrorNumber,
                        ERROR_SEVERITY() AS ErrorSeverity,
                        ERROR_STATE() AS ErrorState,
                        ERROR_PROCEDURE() AS ErrorProcedure,
                        ERROR_LINE() AS ErrorLine,
                        ERROR_MESSAGE() AS ErrorMessage

            END CATCH

                -- now restore the database as archive database          
        BEGIN TRY
         select 'Starting restore part for ' + @archivedbname

             SELECT @sql = 'restore database '+@archivedbname + ' from disk = ''' + @path +'\'+ @dbname+'_FULL_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'+''' with recovery, stats = 10 '+','+ ' move '''+@ldata+''' '+'to ' +''''+ @pdata +''''+','+ ' move '''+@llog+''' '+ ' to '+ ''''+ @plog +''''
                --print (@sql)
                exec (@sql)

                select 'Restore is done sucessfully ! And the new database name is '+@archivedbname + '!!'

        END TRY
        BEGIN CATCH

                SELECT
                    ERROR_NUMBER() AS ErrorNumber,
                    ERROR_SEVERITY() AS ErrorSeverity,
                    ERROR_STATE() AS ErrorState,
                    ERROR_PROCEDURE() AS ErrorProcedure,
                    ERROR_LINE() AS ErrorLine,
                    ERROR_MESSAGE() AS ErrorMessage
        END CATCH

select 'Database is restored as Archive, so now lets do some clean up task ...'
-- delete the backup file
select @sql= 'exec master.dbo.xp_cmdshell ''Del '+@path+'\'+ @dbname+'_full_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'''

print (@sql)
--EXEC (@sql) --- CHANGE HERE !! (if you want to delete the backup file after the restore is done !!


    END

ELSE
    BEGIN
            select ' The database is INCORRECT ! Check if the database exists or is not a system database'
    END       
GO

Method 2 - BCP OUT / BCP IN

Script out the database SCHEMA_ONLY and recreate an empty database on the same server with a different Name.

Use BCP OUT and BULK INSERT to insert data. Below script will help you with that :

    /************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another or same server but different database ***************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe  --- CHANGE HERE !!
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format --- CHANGE HERE !!
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         



--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in --- CHANGE HERE !!
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
        +  QUOTENAME(name) 
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','') 
        +'.dat'' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',  
        TABLOCK
        )'  + char(10) 
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id)