Solved

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

Posted on 2014-01-15
5
166 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now