Creating a new database from a backup

Here’s a quick note on how to take a backup of an existing database, and restore it to a brand new database where you don’t have a copy of the schema.

A few preliminaries:

If you try to do this without using REPLACE, then you might get the following error message:

Error 3154: The backup set holds a backup of a database other than the existing database

You may have to rename the backup to give it a file extension of *.bak

There are a number of ways to create a new database from a backup.

Script

Here is a quick script that takes a backup called star.bak, and uses it to make a database called Star:

RESTORE DATABASE Star

FROM DISK = 'C:\Star\Star.bak'

WITH REPLACE

Interface

Alternatively, it is possible to do this using the interface. Here is the ‘WITH REPLACE’ option that you need to use:

You may also need to give the file an 'owner'. Here is the location where the valid file owner is set:

Enjoy!

Add to Technorati Favorites