Solved

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

Posted on 2014-07-25
16
979 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:BobCSD
  • 10
  • 5
16 Comments
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 100 total points
Comment Utility
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:BobCSD
Comment Utility
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
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:BobCSD
Comment Utility
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
Comment Utility
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:BobCSD
Comment Utility
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:BobCSD
Comment Utility
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
Comment Utility
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:BobCSD
Comment Utility
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
Comment Utility
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:BobCSD
Comment Utility
thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

9 Experts available now in Live!

Get 1:1 Help Now