Solved

SQL Server Management Studio 2012 Express : how to Restore from a backup

Posted on 2014-07-25
16
989 Views
Last Modified: 2014-07-30
I installed the 2012 SQL Server Management studio express, but when I want to use Tasks / Restore, it functions entirely differently than my 2008 version and I can't get it to do it.

 I select: Source / Device:
 then I select my backup MyData.Bak file.

 Then I set my destination to:
 MyData_Dev20

 Then I select a timeline:
 The last backup taken.

 Then I check the Restore box and it requires I Verify Backup Media, which I never had to do before.

 Then I can press OK and it fails with the error that:
 MyData.mdf can't be overwritten as it is being used by database MyData.

 However, I do NOT want it to overwrite MyData.mdf. I want it to create MyData_Dev20.

 I never had this problem with 2008.

 What can I do to use this thing? It's a mess!!!
0
Comment
Question by:Starr Duskk
  • 10
  • 5
16 Comments
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 100 total points
ID: 40221029
Hi Bob,

I think you need to look at the Options Tab in the GUI.
You need to look at the place where the filenames are listed, then look at the "Restore As" part.
Because you are restoring from a bak file, I think you need to change the name of the .mdf and .ldf.

Change these to your "MyData_Dev20" naming you want for the new DB.

I hope this helps

Tony
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40222640
I will look there and see what I can figure out. thanks!

But I'm still concerned.... Why would I have to change names in 2012, but I never did in 2008? It doesn't make sense. i'm restoring to "filename" and it should just do it like it did in 2008.

Have you done this with 2012? It is not intuitive and doesn't make sense they would require something additional without making it clear what/how to do it.

I'll respond how it goes.

thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40222679
But I'm still concerned.... Why would I have to change names in 2012, but I never did in 2008? It doesn't make sense. i'm restoring to "filename" and it should just do it like it did in 2008.
That has not changed:  You cannot overwrite an open database file, unless you request it.  In other words, in order to restore a database as a new database in the same folder you have to rename the data files.  You cannot have the same data files for two different databases in the same folder.

What you should do is script it out and post the results here.  Let me know if you don't remember how to do that.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40222888
Since they are on two different machines, I am going to post the screenshots from my 2008 and then from my 2012 and show you want I'm talking about:

2008
In the above I set the new database and it restores from my backup, which is the old one. It never complains that I am overwriting anything in use. And it doesn't wipe out or replace my active one either. It just restores to the new dbf name I give.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40222891
In the sql 2012, I do the exact same thing, but it errors. (BTW, I did this one first, so the dbf hadn't yet been created, because it failed. Then I did the 2008 which was successful. So they are in reverse order:

2012
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 40222946
Is there any chance you can script the restore in both cases as I suggested previously?

Also the result of the following query in both cases:
SELECT  physical_name
FROM    sys.master_files
WHERE   DB_NAME(database_id) = 'RTO2014'
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40222986
Here is 2008:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014.mdf
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014_1.ldf
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40222989
I don't know how to script a restore.

Here is the result of the query for 2012.
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014.mdf
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014_1.ldf
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:Starr Duskk
ID: 40222994
also, If I go to that directory, this is the main dbf:

Capture.PNG
Here is the copy I just made using 2008:
Capture2.PNG
Notice the time differences. Recovering the database did not update the original MDF or alter it. So I don't understand why it says it's going to alter it when I do the exact same things using 2012.

BTW, all these database MDFS are created in the same folder. Just using the new name I gave during recovery.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 40223336
I don't know how to script a restore.
Once you entered all the parameters in the Restore Database GUI, instead of clicking OK, click on the Script icon.  This will produce the T-SQL that would be executed.  Do the  same for both cases.

FYI In general, it is better to use T-SQL commands rather than the GUI, especially for critical functions like backup and restore.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40225302
Here is 2012:
USE [master]
RESTORE DATABASE [RTO2014_DEV74] FROM  DISK = N'C:\DBFBackups\RTO2014_Hourly.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40225309
Here is 2008:

RESTORE DATABASE [RTO2014_DEV74] FROM  DISK = N'C:\DBFBackups\RTO2014_Hourly.bak' WITH  FILE = 1,  MOVE N'RTO2012B' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014_DEV74.mdf',  MOVE N'RTO2012B_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014_DEV74_1.ldf',  NOUNLOAD,  STATS = 10
GO
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 40225394
The difference is pretty obvious to me:
Here they are highlighted:
RESTORE DATABASE [RTO2014_DEV74] FROM  DISK = N'C:\DBFBackups\RTO2014_Hourly.bak' WITH  FILE = 1,  MOVE N'RTO2012B' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014_DEV74.mdf',  MOVE N'RTO2012B_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\RTO2014_DEV74_1.ldf',  NOUNLOAD,  STATS = 10

You can see how the GUI in SQL Server 2008 is assigning new data and log transaction files.
0
 
LVL 1

Author Comment

by:Starr Duskk
ID: 40225459
well, yeah. I can see that. But why doesn't 2012 do the same thing? It's the same path. How do I fix 2012 to work?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 40225865
It looks like they changed the Restore GUI in SSMS 2012.  According to Aaron Bertrand it had to do with the fact that with SSMS 2008 the logical names remained the same (see the discussion here)

However, in my view the bigger problem with the new GUI in SSMS 2012 is the problem covered in the article SSMS 2012 Restore GUI Gotcha  Now that is some scary stuff there.

But I have to confess since I never use the GUI, I am not familiar with any of it.  I prefer to have the fullest control possible over what gets done.
0
 
LVL 1

Author Closing Comment

by:Starr Duskk
ID: 40230989
thanks!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
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 shrink a transaction log file down to a reasonable size.

867 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

22 Experts available now in Live!

Get 1:1 Help Now