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)
