Solved

How to create a new database from an existing database's backup

Posted on 2014-01-15
5
170 Views
Last Modified: 2014-01-21
I have a SQL Server 2005 database instance and a backup of this instance. My user accidently deleted some content and needs a copy of the database restored so that they can retrieve that content and then add it back to the originally production instance.

I'm not at all familiar with using SQL queries or commands so I am looking for a way to create a temporary database from the backup of an existing/production Sql 2005 database instance.

Thanks.
0
Comment
Question by:dowhatyoudo22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 29

Expert Comment

by:chilternPC
ID: 39783788
create a new database
and use the backup to restore to that new database
then access the new database with the old data
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39783794
ok, if you are using SQL Server Management Studio

Then please follow the below steps

1) right click on the database node and say restore
2) select from disk (or from path) option and give the backup location
    --- wait for a few seconds as SQL server will try to create a restore plan
3) Now, the destination database will be filled up with the original database name, change it to some thing else (add temp to it)
4) go to files section
5) change the destination directory for all the files or change the file names.
6) click restore
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39783803
If you want to see what's going on behind the scenes:

You can do:

restore filelistonly
from disk=N'E:\backups\your_backup_file_name.bak'

That will return you logical file names as the first column of the output.

From there you can do this, with one MOVE clause for each logical file name

RESTORE DATABASE [TESTCOPY]
from disk=N'E:\backups\your_backup_file_name.bak'
WITH  FILE = 1,  
MOVE N'first_logical_file'     TO N'E:\MSSQL\Data\testcopy.mdf',  
MOVE N'second_logical_file' TO N'E:\MSSQL\Data\testcopy2.ndf',  
MOVE N'lofile_logical_Log'     TO N'E:\MSSQL\Data\testcopy.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

When finished, you have a copy of the database.
0
 

Author Comment

by:dowhatyoudo22
ID: 39783883
Surendra Ganti,

for step 5) do I need to change the entries listed under the 'Original File Name' heading? Or can I just add 'temp' to the end of the filenames listed under the 'Restore As' heading? Want to make sure I am not overwriting the production files.

Also I noticed your directions are for SQL Server Management Studio. I do not have SSMS installed on the server in question. I have SQL Server Enterprise Manager. However I do have SMSS installed on another server and can connect back to the SQL server where the production database instance and where I want to restore this copy to. Are there any issues with doing this remotely?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39783989
do the later, change the file names under the restore as heading..
No, issues even if you do this remotely...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question