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