Restore an MSSQL backup to a different database name

I had a database backup and was trying to restore it but to a different database name than that from where it was backed up.

Let’s say you backed up MyDB and you want to restore it to MyNewDB. You can run the following TSQL:

Of course, the paths and the names of the database should be changed according to your needs.

2 thoughts on “Restore an MSSQL backup to a different database name

  1. Why is the replace required here? What is replace doing? I’ve been having issues getting a particular DB to restore as a different name, and with a test db I was able to do something like…

    restore database testdb from disk = C:\test.bak with recovery,
    move ‘test’ to ‘C:\newlo\test.mdf’,
    move ‘test_Log’ to ‘c:\newlo\test.ldf’
    and that worked, but then on this production DB, when I tried the same I kept getting failures until I used replace/go as you did above.

  2. @Derek

    Thanks for commenting. I don’t know why it didn’t work for you without the REPLACE, but I have found the following excerpt:

    —–%<——————
    When the REPLACE option is not specified, a safety check occurs (which
    prevents overwriting a different database by accident). The safety check
    ensures that the RESTORE DATABASE statement will not restore the database to
    the current server if:

    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.

    REPLACE also allows RESTORE to overwrite an existing file which cannot be
    verified as belonging to the database being restored. Normally, RESTORE will
    refuse to overwrite pre-existing files.
    —–%<——————

    Maybe something mentioned above was your case.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s