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!

One thought on “Creating a new database from a backup

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s