Post reply

Trying to create new database from restore.

  • February 3, 2011 at 5:21 am

    #233722

    I am trying to create a copy of my scouts database. Scouts3 does not exist. I want to create if from a backup of Scouts. I run the following command. Below the command is the error I recieve. If this command is not the correct way to create a new database from a restore please give me direction on how I should go about doing something like this. Thanks.

    RESTORE DATABASE Scouts3

    FROM DISK =

    'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\scoutsnow.bak'

    with move 'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Scouts.mdf'

    to

    'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Scouts3Data.mdf',

    move 'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Scouts_log.ldf'

    to

    'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Scouts3Log.ldf';

    ------- The error -----

    Msg 3234, Level 16, State 2, Line 6

    Logical file 'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Scouts.mdf' is not part of database 'Scouts3'. Use RESTORE FILELISTONLY to list the logical file names.

    Msg 3013, Level 16, State 1, Line 6

    RESTORE DATABASE is terminating abnormally.

  • February 3, 2011 at 6:45 am

    #1281879

    That's the right syntax, almost.

    The WITH MOVE must reference the logical name of the file, the name that it's referred to in SQL, not the name of the file in the file system. If you don't know what the logical names are, use RESTORE FILELISTONLY and it will list the files of the database in the backup. Then your restore would look like this

    RESTORE DATABASE Scouts3

    FROM DISK = 'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\scoutsnow.bak'

    with move <logical name of data file> to 'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Scouts3Data.mdf',

    move <logical name of log file> to 'C:\Program Files\Microsoft Sql Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Scouts3Log.ldf';

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply