[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

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

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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