current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I am trying to follow the instructions on the following post:

http://stackoverflow.com/questions/10299811/creating-new-database-from-a-backup-of-another-database-on-the-same-server

To summarize; I am building the logical file list then restoring the database:

RESTORE FILELISTONLY
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK';

RESTORE DATABASE KeltieCochrane_v2 
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK'
WITH 
    MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\...\KeltieCochrane_v2.mdf',
    MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\...\KeltieCochrane_v2_1.ldf';

I have no .MDF or .LDF in the target folder and understand that they are contained in the .BAK backup file.

When I restore the database into an empty, new database, it states:

The backup set holds a backup of a database other than the existing 'KCDB'.

When I try to restore using the actual database name in the script (KeltieCochrane_v2) I get There are conflicting file locations specified for the file.

If I try to simply restore the database I get a standard 15105 error and the operating system cannot get the text for this error.

What am I doing wrong?

share|improve this question

migrated from stackoverflow.com Jun 22 '12 at 18:09

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 5 down vote accepted

You're getting that first error because kdcb already exists. And the backed up database is not that same database. If you want to overwrite an existing, separate database (that isn't the same. With the same you don't need to specify replace), you need to include the REPLACE option:

RESTORE DATABASE KeltieCochrane_v2 
FROM DISK = 'C:\KeltieCochrane_v220120220.BAK'
WITH 
MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\KeltieCochrane_v2.mdf',
MOVE 'KeltieCochrane_v2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\KeltieCochrane_v2_1.ldf',
REPLACE

WARNING!!! You need to be very careful and make serious consideration by specifying REPLACE. Here is an exact excerpt from BOL regarding REPLACE:

Replace Option Impact

REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.

The REPLACE option overrides several important safety checks that restore normally performs. The overridden checks are as follows:

Restoring over an existing database with a backup taken of another database.

With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.

Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used.

With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

Overwriting existing files.

For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online. Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.

share|improve this answer
    
Thank you for your comment. I am using a local instance on my home computer. Its a fresh install. All I have is a .bak file. I just need to get the thing loaded for querying ASAP. I am not a DB admin I am a C#.net developer. The amended script gives me conflicting file relocations have been specified. Are you able to assist further with this info? –  dotnetnewb Jun 22 '12 at 18:28
1  
That's because the command you posted has duplicate logical file names (both 'KeltieCochrane_v2') in the WITH MOVE clause. Re-run the RESTORE FILELISTONLY and make sure those logical file names are what you use in the restore command. You'll probably see a 'KeltieCochrane_v2' logical name for the data file, and a 'KeltieCochrane_v2_log' logical name for the transaction log file. This is the default naming scheme used by SQL Server when creating databases without logical file names explicitly specified. –  kozloski Jun 22 '12 at 20:01
    
Ah, yes I see! Never recovered a .bak on a fresh local instance with no .ldf and no .mdf. You've been a great help much appreciated... –  dotnetnewb Jun 22 '12 at 21:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.