Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Weird Issue Restoring DB in SQL 2012 Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 4:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:20 AM
Points: 86, Visits: 515
Scenario
Test Server
Database A. Needs to be backed up and restored as Database B.
1 Backup Database A. Fine
2 Restore Backup of A as DATABASE B. Problem.

Although the destination db using the GUI has been changed to DATABASE B and the overwrite existing database is NOT ticked. This has been overwriting DATABASE A even through the overwrite option is not checked. When this is scripted out via the script button on the GUI it states restore database [DATABASE B}.
Workaround has been to rename DATABASE A to DATABASE x, do the restore it restores as DATABASE B correctly. Then return DATABSE X to DATABASE A.

If the original database that was used for the backup doesn’t exist it will create the database correctly with no overwrite. Has anyone seen this before its very strange.
SQL 2012 on WINDOWS 2012.
Post #1542032
Posted Monday, February 17, 2014 6:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 213, Visits: 829
When using the GUI, have it generate a CREATE script for Database A, a CREATE script for Database B, a RESTORE script for Database B, and a RESTORE FILELISTONLY of the backup file. Show us all of this and there will be an answer in there somewhere.

The only thing I can think of is if you detached Database A, and Database B had files pointing to the same location. You restore B without a WITH MOVE clause and it overwrites A's files.
Post #1542076
Posted Monday, February 17, 2014 7:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
Yeah, I'd make sure that you're using WITH MOVE. It could just be that it's overwriting the files... which would only be possible if the database is offline.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542123
Posted Monday, February 17, 2014 10:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:20 AM
Points: 86, Visits: 515
Thanks for the replies, but the dbs are not offline. I am using the GUI not T-SQL. The database nor db files should be able to be overwritten. As long as I change the destination db in the GUI and change the file names it should restore correctly.

If the database name is changed from what it was when the backup was taken it works fine. So it is not the files as they remain the same when the db is renamed. It's a very strange one.

Again my work around for now is to rename the db(Original db_a)the backed up db rename to(db_a1), restore it from the backup file as (db x).
Rename db_a1 back to db_a.

It seems if the origanl db used to create the backup file exists it overwrites it. Even when the option is not selected. This should not be possible. Honestly this is the case.
Post #1542213
Posted Monday, February 17, 2014 10:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 1,232, Visits: 9,678
This isn't what you're seeing is it?

http://sqlblog.com/blogs/merrill_aldrich/archive/2013/03/15/ssms-2012-restore-gui-gotcha.aspx
Post #1542217
Posted Monday, February 17, 2014 10:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 1,232, Visits: 9,678
Just played around with it myself (don't usually use the GUI very often) and it's pretty misleading.

By default, when selecting a backup file that has the same DB name as one that exists on that server, if you don't go onto the options tab, you'll put your source database in the RESTORING state as it does a tail log backup of it, even if your destination name is different.

It's not actually restoring over the top (you can still bring it back ONLINE), but can see why you'd think it was restoring over the top!
Post #1542223
Posted Tuesday, February 18, 2014 2:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:20 AM
Points: 86, Visits: 515
Yes the link seems to be the issue, this is pretty poor from MS as the overwrite is not ticked so you would expect failure.
I usually use T-SQL for restores, this caught me out as it was supposed to be a quick smash and grab restore for someone in the DEV environment who was struggling to get it done.

THANKS
Post #1542427
Posted Tuesday, February 18, 2014 3:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 213, Visits: 829
Holy cow that article (and more specifically the related Connect item) are crazy, and that they've been sitting there since February with no reply is ridiculous!

On the other hand I now have legitimate reasons to tell businesses that the SQL Server GUI can destroy databases just while trying to restore them, and so they really need my services because I would use T-SQL. Hehe.
Post #1542451
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse