Search

Showing posts with label RESTORE DATABASE fails while trying to restore password protected backup set. Show all posts
Showing posts with label RESTORE DATABASE fails while trying to restore password protected backup set. Show all posts

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