?
Solved

Copy SQL database to a new database on the same server

Posted on 2014-08-27
4
Medium Priority
?
196 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!
0
Comment
Question by:bbdesign
  • 2
  • 2
4 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40288538
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
 

Author Comment

by:bbdesign
ID: 40288649
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40288676
Sorry:

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

Author Comment

by:bbdesign
ID: 40288758
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question