?
Solved

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

Posted on 2014-01-15
5
Medium Priority
?
172 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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

764 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