SQL Server 2012 copy database failed

Question

I want to make a copy of database in the same SqlServer . so, When i using the Copy Database Wizard, it throw error: ( I did this steps with a test DB and it works fine !!!!)

config:

sa user

Method:"Use the SQL Management Object method"

Chose new name for destination database.

error:

TITLE: Copy Database Wizard

The job failed. Check the event log on the destination server for details.

------------------------------ BUTTONS:

OK

in event log :

• System

  • Provider

  [ Name] SQLSERVERAGENT

  • EventID 208

  [ Qualifiers] 16384 Level 3 Task 3 Keywords 0x80000000000000

  • TimeCreated

  [ SystemTime] 2014年05月07日T06:23:11.000000000Z EventRecordID 123672 Channel Application Computer Server1 Security

• EventData

  CDW_Server1_Server1_3 0x666DE807F406D7438C65B09171211D7B
  Failed 2014年05月07日 10:52:50 The job failed. The Job was invoked by User sa. The last step to run was step 1 (CDW_Server1_Server1_3_Step).

last lines of log file:

OnProgress,Server1,NT Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,Database transfer failed for 1 database(s). OnProgress,Server1,NT Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,100,0x,Transfer objects finished execution. OnTaskFailed,Server1,NT Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null) OnPostExecute,Server1,NT Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null) OnWarning,Server1,NT Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

OnPostExecute,Server1,NT Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null) DiagnosticEx,Server1,NT Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,104546304 PackageEnd,Server1,NT Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014 10:08:46 AM,5/7/2014 10:08:46 AM,1,0x,End of package execution.

• Vesper: You should provide more data on how do you configure the copy database process. Maybe you're copying DB over itself at file level.
• irmorteza: i use sa user in wizard. and Method:"Use the SQL Management Object method" . and i am sure named new DN name. I did this steps with a test DB and it works fine !!!!!.
• irmorteza: Restore with new name, not worked too.!!!!!!!!! it throw error of oldDB in use. i stoped application and restart SQLServer too, but not worked
• Steve Pettifer: You have to make sure that when you restore you change the physical file name as well as the logical name otherwise you're trying to restore over the top of the existing DB files which will be in use.
• irmorteza: In restore wizard after giving new name. Logical and physical name not changed automaticaly. and i just can change physical name not logical

Answer 1

It might be easier to simply backup the database to a .bak and then create a new database from a .bak restore.

BACKUP DATABASE [aaa] TO DISK = N'E:\aaa.bak'

Then check the logical names and locations for the current files in the .bak:

Restore filelistonly from disk ='E:\aaa.bak'

And finally restore the database, renaming the files to make sure you don't overwrite your existing database

restore database Newdatabasename
FROM disk = 'E:\aaa.bak'
WITH replace,
MOVE 'Logical data name' TO 'E:\Newdatabasename.MDF',
MOVE 'Logical log name' TO 'E:\Newdatabasename.LDF',
recovery --force

Answer 2

Below is the script that I wrote for myself to make copy of database. Its flexible and can be converted into a stored procedure.

The comments will explain what it does.

Test it on a Test server before running it in PROD !!

/*
Author : KIN SHAH
Purpose : Written for dba.stackexchange.com
 - This script will take the current database name and make a copy of it as
 databaseName_copy_Month_Year e.g. [AdventureWorks2008R2_copy_August_2014]
 - It will not replace the database and will fail if the copy database existed.
 - Assuming that copy database is not currently present on the instance.
 - It wont delete the backup made as a part of making a copy of the database. 
 It will print out the location and then you can delete it
Any questions .. Let me know .... :-)
*/
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
/************************************************* CHANGE HERE STARTS !! ******************************************************/
DECLARE @dbname NVARCHAR(MAX)
SET @dbname = 'AdventureWorks2008R2' -- ** change HERE ***
--backup path goes here
DECLARE @path NVARCHAR(MAX)
-- Here the path is hard-coded as all the server has 'D:2円restore' folder. This can be made as input parameter also !
SET @path = 'C:\crap_test' -- ** change HERE ***
 -- generates copy database name 
DECLARE @archivedbname NVARCHAR(MAX)
SET @archivedbname = @dbname + '_copy' + '_' + DATENAME(MONTH, GETDATE()) + '_' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(MAX))
--PRINT @archivedbname
/************************************************* CHANGE HERE ENDS !! ******************************************************/
-- 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, compression, 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 ' + ',' + ' 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 Copy, Now you can delete the backup taken at ...' + @path + '\' + @dbname + '_full_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'''
 -- 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)
END
ELSE
BEGIN
 SELECT ' The database is INCORRECT ! Check if the database exists or is not a system database'
END
GO

Answer 3

I had the same issue. I changed the SQL Server Agent service logon account to an administrator. After that, everything is okay. I think, the Agent service could not read/write.

• Lester Nubla: This is the easiest solution among the answers here. I will never suspect the logon account to cause the issue.

Answer 4

I had issues with this. Eventually I decided to do two things:

1. Create a file share called 'DTS Packages'. This was because I was not able to select a package destination in the wizard. Ensure the share and ACL permissions are full for the account your SQL Server Agent is running under (default is 'NT Service\SQLSERVERAGENT').

enter image description here

2. Add full permissions to the SQL Server Agent account to the destination folder where the new database is to be created too.

I also changed the logging to log to a file so it was easier to diagnose what went wrong.