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.

Trying to restore database, getting a dialog in SQL Server Management Studio

You cannot select backup sets from different databases"

Any idea why I am getting this error?

BTW backup is from SQL Server 2000 and trying to restore in SQL Server 2000 using SQL Server 2008 R2 Management Studio

share|improve this question

migrated from stackoverflow.com Feb 14 '12 at 19:56

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

add comment

3 Answers

The message usually means that you're trying to restore a backup of another - unrelated - database over your existing database. Double check that the backup you're restoring is really of the database you think it is.

Edit: If I remember correctly, you can also get this message if you're trying to restore a differential backup without first restoring the correct full backup.

share|improve this answer
add comment

To see what is going on, instead of using the GUI, use the following in a query window:

RESTORE HEADERONLY FROM DISK = 'drive:\path\file.bak';

You can read more about this here but at the very least this will return the DatabaseName column so you can sanity check that the database you're trying to restore is in fact the one you are intending to restore. Also learning the RESTORE DATABASE command in general will give you a much better way to show us what you're trying to do, since unless you record a video and post it, it is difficult for us to understand what you are pointing and clicking at before you get this error.

share|improve this answer
add comment

When using the GUI, you have to specify "" to enable over writing of a database with a backup from another database. See "Using the REPLACE option" on MSDN

How to?

With raw SQL, you need WITH REPLACE:

Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs. This prevents overwriting a different database by accident. The safety check ensures that the RESTORE DATABASE statement does not restore the database to the current server if the following conditions both exist:

  • The database named in the RESTORE statement already exists on the current server, and
  • The database name is different from the database name recorded in the backup set.

When using the GUI, you need to set "Overwrite the existing database":

Specifies that the restore operation will overwrite the files of any database that is currently using the database name that you are specifying in the To database field on the General page of the Restore Database dialog box. The files of the existing database will be overwritten even if you are restoring backups from a different database to the existing database name. Selecting this option is equivalent to using the REPLACE option in a RESTORE statement

share|improve this answer
add comment

Your Answer

 
discard

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