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)
Here is a run down to accomplish what you are trying to do. I added screen shots to help you along with backing up and restoring your database. The file paths won't exactly be the same.
BACKUP DATABASE
Backup the database Registered
Click on OK
The database has been backed up
RESTORE DATABASE
To restore the database registered, right click on the Registered Database in SSMS and choose Restore Database
In the window that appears, select From Device and click on the button with three ... to search for your backup
From here, click on Add and locate your backup file
Now click on OK
Click on OK
You are now back in the Restore Database Window, select Options
Make sure to select Overwrite the existing database (WITH REPLACE) and Leave the database ready to use option in the Recovery State options.
Then Click on General in the Select a page zone
Click on the checkbox "Restore" and then click on Script. I prefer sending the restore command to a new windows as a Script instead of directly clicking on OK. This way, I can drop any active connections to the database before restore which could result in a database in use error.
A new query window will be opened with the code necessary to restore the Registered database.
Add the following code just above the restore database script
-- drop all connections and put database in restricted user mode
ALTER DATABASE [Registered] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
-- restore database
RESTORE DATABASE [Registered]
FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Registered.bak'
WITH FILE = 1, NOUNLOAD, STATS = 1
GO
You will notice that I modified the STATS option from STATS=10 STATS=1. This can be useful when restoring large databases. In the Messages tab, you can view the progression of the restore by %. With STATS=1, the restore can be monitored 1% at a time instead of every 10%
Now hit F5, to run the script.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
13 percent processed.
23 percent processed.
32 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
73 percent processed.
83 percent processed.
92 percent processed.
100 percent processed.
Processed 168 pages for database 'Registered', file 'Registered' on file 1.
Processed 5 pages for database 'Registered', file 'Registered_log' on file 1.
RESTORE DATABASE successfully processed 173 pages in 0.076 seconds (17.783 MB/sec).
Best Answer
I know this is an old question but will leave this here for anyone else who comes looking. Ran into this problem today and was able to get it going.
This is difficult to believe but disabling parallelism got it online in my case.
I never would have thought to even try that, only I was watching the sysprocesses for that DB and was seeing the parallelism split / cxpacket wait behavior… the wait types would change like it was working on something. Thinking maybe maxdop=1 would help whatever this was by not splitting up, changed it and the DB immediately came online.
Other info:
Mine was also SQL 2008 database is 176GB
Also saw (at some point) a system spid (under 51) showing "Create Index" Runs change data capture in production (not sure that had anything to do with it)
Was restoring copy to a dev environment as part of a regular data refresh from production.
Disk space was very tight on the destination server it had just enough room for the data and log files to restore, but was under 10 meg free at that point. I think the lack of space may have helped cause the problem? I was able to free up some space before the maxdop change, but the database was still not usable until the maxdop setting was changed. sys.databases showed the state as 'online' the database looked usable in every way but any attempt to use it would time out