Copy SQL database to a new database on the same server

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!
Brad BansnerWeb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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
0
Brad BansnerWeb DeveloperAuthor 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.
0
Scott PletcherSenior DBACommented:
Sorry:

EXEC uktech.sys.sp_helpfile --no "s" on the end
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brad BansnerWeb DeveloperAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.