RESTORE DATABASE fails while trying to restore password protected backup set from SSMS

RESTORE DATABASE fails while trying to restore password protected backup set from SSMS

  • Comments 1

ISSUE:

I’m trying to restore a database backup from SSSMS-UI, however it fails in an initial stage with below error

TITLE: Microsoft SQL Server Management Studio
------------------------------
Specified cast is not valid. (SqlManagerUI)
------------------------------

Here’s the detailed error:
Program Location: 
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnTextChanged(EventArgs e)
at System.Windows.Forms.TextBoxBase.OnTextChanged(EventArgs e)
at System.Windows.Forms.Control.set_Text(String value)
at System.Windows.Forms.TextBoxBase.set_Text(String value)
at System.Windows.Forms.TextBox.set_Text(String value)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

CAUSE:
This seems to simple restore, so not sure what’s happening. With little idea, ran below command against the backup set

----RESTORE HEADERONLY----
RESTORE HEADERONLY FROM DISK = 'D:\tempdb\pubsdb.bak'
GO

 

This clearly means that the backup set is PASSWORD PROTECTED1. More details, can be read here >> Backup Set Password Protection
However strange thing is that, SSMS-UI never prompted me to enter password. At this time, this appears to be a SSMS limitation (
We have already logged the necessary feedback with our product development team so that they are aware of this issue) and can be easily overcome this using below resolution. 

RESOLUTION:

Try restoring the database using T-SQL command and specify the PASSWORD = ‘****’, as below 

----RESTORE DATABASE USING T-SQL----
RESTORE DATABASE [pubs_new] FROM  DISK = N'D:\tempdb\pubsdb.bak'
WITH  FILE = 1,  MOVE
N'Pubs' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\pubs_11.mdf'
MOVE
N'Pubs_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\pubs_11.ldf'
PASSWORD =
'**password_is_this**'
GO

And this is done!

1The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password. The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created. This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. MSDN Source >> Backup Set Password Protection

 

 

Regards,

Varun Dhawan

SE, Microsoft SQL support

Reviewed by

Saket Suman

TL, Microsoft SQL support

Amit Banerjee

SE, Microsoft SQL support

 

 

 

 

 

Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
  • I have a better resolution:

    MS finally put in the ability to enter a damn password when doing a restore!!!! It's only been 7 years since I first begged you to put this in and you said you would at that time!

    Silliness that Management Studio can't handle this.

    And then there are all of the cases of attaches and restores not working with file streams and different directories than what it was backed up with.....

Page 1 of 1 (1 items)