Copy SQL database to a new database on the same server

Posted on 2014-08-27
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:

FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uktech\uktech_full.bak'
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',

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?
Question by:bbdesign
    LVL 68

    Expert Comment

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

    You can use what the error suggests:

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

    or do this:

    EXEC uktech.sys.sp_helpfiles

    Author Comment

    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):

    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',

    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.
    LVL 68

    Accepted Solution


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

    Author Comment

    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.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now