Search

Thursday, July 14, 2011

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


I am getting the below error while restoring the Database backup from SSMS-UI.
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)

Now, to resolve the error I go through the following procedure.
1st I ran the below command.
----RESTORE HEADERONLY----
RESTORE HEADERONLY FROM DISK = 'D:\tempdb\pubsdb.bak'
GO
Now it is clearly showing that the backup set is PASSWORD PROTECTED1. For more details, go through the following link >> Backup Set Password Protection
Now I tried to restore the database using below SQL Query.
----RESTORE DATABASE USING T-SQL----

RESTORE DATABASE [New_DB] FROM DISK = N'C:\temp\New_DB.bak'
WITH FILE = 1, MOVE
N'Pubs' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
New_DB.mdf',
MOVE
N'Pubs_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
New_DB.ldf',
PASSWORD =
'<Type Password here>'
GO

No comments:

Post a Comment