We help IT Professionals succeed at work.

Copy SQL database to a new database on the same server

Brad Bansner
Brad Bansner asked
on
217 Views
Last Modified: 2014-08-27
Running Microsoft SQL Server 2012. I am trying to copy a database called uktech to a new database called catech, on the same SQL server.

catech database does not exist. I did a "copy only" full backup of uktech, file is called uktech_full.bak

I am using a query that has worked before in this type of scenario, but today I am getting an error. Here is my query:

RESTORE DATABASE catech
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uktech\uktech_full.bak'
WITH FILE=1,
MOVE 'uktech' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\catech',
MOVE 'uktech_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\catech_log',
NOUNLOAD, STATS=10
;

Open in new window


Here is the error:

Msg 3234, Level 16, State 2, Line 1
Logical file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\uktech' is not part of database 'catech'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Open in new window


Since I didn't write that query originally, I'm not sure what is wrong with it today. Yes, the error is correct that that file is not part of the database catech. But I am trying to copy its entire database structure and records to a new database, and I know this has worked before.

Any ideas?
Thanks!
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Make sure the logical file names are complete; I suspect one or more may be missing.

You can use what the error suggests:

RESTORE FILELISTONLY FROM
DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uktech\uktech_full.bak'

or do this:

EXEC uktech.sys.sp_helpfiles
Brad BansnerWeb Developer

Author

Commented:
I tried changing everything to full paths, verified they are correct, also added the MDF and LDF file extensions, and ended up with this query (also changed RESTORE DATABASE to RESTORE FILELISTONLY):

RESTORE FILELISTONLY
FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uktech\uktech_full.bak'
WITH FILE=1,
MOVE 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\uktech.mdf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\catech.mdf',
MOVE 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\uktech_log.ldf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\catech_log.ldf',
NOUNLOAD, STATS=10
;

That gives me this error:
Msg 3032, Level 16, State 2, Line 1
One or more of the options (stats, stats=, move) are not supported for this statement. Review the documentation for supported options.

When I run:
EXEC uktech.sys.sp_helpfiles

I get:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'uktech.sys.sp_helpfiles'.

I also tried right-clicking the source database, tasks, copy database, select (local) for both source and destination server, "use the SQL Management Object method", select my source database to copy, tell it to create a new database catech (with corresponding MDF and LDF files). I run that package, it executes for a minute or so, then gives me an error:

SQL Server Scheduled Job 'CDW_IHPDB_IHPDB_4' (0xA21B43035C5CDB4897E2EBAC43BE77A3) - Status: Failed - Invoked on: 2014-08-27 14:01:04 - Message: The job failed.  The Job was invoked by User IHPDB\bbdesign.  The last step to run was step 1 (CDW_IHPDB_IHPDB_4_Step).

If you have a better suggestion for simply copying this database to a new database, please let me know. I am running into brick walls no matter what I try. I was able to easily do this kind of thing in MS SQL 2005, but 2012 seems to be a totally different animal.

Thank you.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Brad BansnerWeb Developer

Author

Commented:
Ah, that did it. Thank you! Once I could see the files involved, I realized uktech and uktech_log were not correct. Switched them, now the DB is copied.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.