Wednesday, April 24, 2013

Sql Server : create new Database from the Backup of another Database

How to create new Database from the Backup of another Database?
I was using Adventureworks2008 database for practicing in Sql Server management system. During practice I wanted to use DDL, (create, alter, drop), and DML (delete, update) and many other operations as well. For that I wanted to keep my adventure works database safe for other practices as well.  Then I had a thought , if would have a copy of this same database , so that I could perform all kind of operations on one copy of my Adventureworks2008 database, and another remains safe for real projects.

Solution :

For the above scenario, we can create same database as Adventureworks2008 in two ways
A.      If Adventureworks2008 database exists on some other server or system, then we can take back up of Adventureworks2008, and then we can restore our empty database from the backup file.

B.      If Adventureworks2008 database exists on same server, then we can directly restore our empty database from Adventureworks2008 database.

Case A. When Adventureworks2008 database exists on some other server or system

Below are the steps that need to take place for creating backup file for Adventureworks2008
1: Right click on AdventureWorks2008 Database and go to Tasks->Backup

             2: Give a path as Destination, where you want to keep the backup., then click on ‘OK’

  Case B:  When Adventureworks2008 database exists on same server,

Then no need to create backup file, we can directly restore it from Adventureworks2008 Database
Below are the Steps, which will be followed to create and restore new database in both cases A and B
Create an empty new database say Copy_AdventureWorks
Here in the below snapshot, the path mentioned, is the path where the new database will be created
Right Click on Copy_AdventureWorks database then go to Tasks->Restore->Database

In the below snapshot of restoring database, we have to give the source and location for backup file to restore.
There can be two case, as mentioned above Case A and Case B.
Here we are assuming that Adventureworks2008 database exists on the same system that is Case B, So we select ‘from database’ and select ‘Adventureworks2008’ Database form the dropdown.
In Case B, when Adventure Works doesn’t exists on same system or server, then we will select ‘From Device’, and will give the path where the backup file exists.

When we click on OK, we get below error
‘Restore Failed and the backup set holds a backup of a database other than the existing Copy_AdventureWorks’
It’s because the backup taken is of Adventureworks2008 database. So we will need to overwrite the existing database. For this, go to option and check the checkbox ‘Overwrite the existing database’

Still we get some error as shown below in the snapshot:
The error is ‘…/.AdventureWorks2008_Data.mdf cannot be overwritten. It is being used by database ‘Adventureworks2008’.

The solution is to change the name for .mdf and .ldf file. That we can do by changing the name in Restore As column

 Change the name ‘AdventureWorks2008_Data’ to ‘Copy_AdventureWorks_Data’
And ‘AdventureWorks2008_Log’ to ‘Copy_AdventureWorks_Log’
And then click ‘ok’, it will work and you will get your new empty database Copy_AdventureWorks restored with the same Adventureworks2008 Database.

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

No comments:

Post a Comment