SQL backup 6.5 Ability to choose "existing database file bug

Andrew Hodge

Was interested in the new 6.5 feature

Ability to choose "existing database file location" as well as the SQL Backup suggested defaults.

This doesnt seem to work correctly for me though.

See the screenshots showing what happens when I select Original Database data directory. The primary file directory seems to be incorrect with the DATA directory getting transposed into the filename? if I switch back it gets even worse with the log file name getting overwritten to.



Original Database file location
[/img]
Log file name overwrittern
[/img]

Matthew Flatt

Hi Andrew,

Thank you for pointing this out, we will take a look into it. Switching to Create new database and back to Overwrite existing should get you into a workable state when this does happen.

Out of interest, when you select a database to be overwritten by the restore what do you think should appear for the file locations by default?

SQL Server Data Directory.
Database file locations from the backup file.
Database file locations from the database being overwritten.

Thanks,

Andrew Hodge

It looks as if it nearly works.

When selecting Original Database Data Directory it is correctly changing the Transaction Logs path to F:\Program Files\Microsoft SQL Server\....

The problem is the Primary database file path and name (screenshot 2), it changes it to

Path
E:\Program Files\Microsoft SQL Server\MSSQL10.INSTC\MSSQL
File Name
DATA (this is totally wrong and is the end of the folder path)

It should read

Path
E:\Program Files\Microsoft SQL Server\MSSQL10.INSTC\MSSQL\DATA
File name
BGInfo.MDF

Andrew Hodge

Tried this on another server (with a default instance) and get exactly the same problem. Ie the filename of the primary file being overwritten with DATA and the file path losing the final folder ie the DATA folder

Andrew Hodge

Tried this on a SQL Server 2008 and SQL Server 2005 instance and both have the same issue

David A

I had sent an email to Support before finding this thread. The "SQL Server Data Directory", while backward consistent, is not intuitive. I would expect to find the values SQL Server uses internally, as found in the registry keys HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\DefaultData and DefaultLog. This has caused considerable pain as new developers come on and start using the tool.

To answer Matthew's question, I would expect the existing file pathnames to be used when overwritting a database, which again has been a point of pain for me.

Andrew Hodge

I agree with David

When selecting SQL Server Data Directory it should default to the SQL Server settings (specifically it should pick up the log location).



[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLDataRoot]
=E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
This appears to be missing the final folder \DATA.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog]
F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

When chossing original database directory it should be
Database file locations from the database being overwritten.

Andrew Hodge

Just did some more testing.

When I select the create new database radio button the drop down (orginal database data directory) works correctly ie the paths and filenames are correct.

The bug only appears to be when having the overwrite database radio button selected.

Matthew Flatt

Hi Andrew/David,

If you would like to download this it should fix the issue with the Combo box when Overwrite existing database is selected.

Let me know how you get on.

Thanks,

Andrew Hodge

Hi Matt,

Tested this and working as expected now. Tried it on a few different configurations (2005 & 2008, default instance and named instance) and all ok.

Just curious that the fix appears to be in the GUI but the download also updates the agent? ie with the 6.5.0.75 GUI the funcationality works as expected even on 6.5.0.73 agents? Any reason for this.

Many thanks

Andy

Matthew Flatt

Hi Andy,

It's just that the version number of the Server Components has now been incremented to match the GUI. The GUI however notices that the Server Components build number is older than the GUI and prompts for an upgrade.

Thanks,

David A

I just installed the update through the normal update service, but it still does not read the registry keys from our server. (See the early post for the keys used on our 64-bit box.)

Matthew Flatt

Hi David,

The fix in this version was just to correct the behaviour when the File locations drop down box was used when Overwrite existing database was selected.

The issue with SQL Backup not reading the correct registry keys to obtain the SQL Server default data and log locations is a bigger one which we will hopefully fix in the next major release.

Thanks,

jasoncrider

The hotfix for this was 6.5.0.75.

I'm currently running 6.5.1.9 and I can't use that exe to fix this bug.

What are my options?

Thanks

Matthew Flatt

Hi Jason.

The fix to correct the behaviour when the File locations drop down box was used with Overwrite existing database being selected is also in 6.5.1.9.

Could you email support@red-gate.com with details of the issue you are currently seeing and screenshots if possible?

Thanks,

jasoncrider

I sent the email as you requested with screenshots.