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
Solved

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

Posted on 2014-01-15
5
169 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
5 Comments
 
LVL 28

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 26
TSQL Challenge... 7 35
SSMS Opening Mode 9 18
Teradata SQL - Is it possible to take each line item and turn into column headers. 2 27
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

839 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