RESTORE DATABASE fails while trying to restore password protected backup set from SSMS
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!
**1**The 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