Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1083
  • Last Modified:

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

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
Starr Duskk
Asked:
Starr Duskk
  • 10
  • 5
5 Solutions
 
Tony303Commented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Anthony PerkinsCommented:
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now