0

I have to run the backup of SQL Server 2000 in SQL Server 2008.

While restoring the database from the .bak file, I got the error

specified cast is invalid

After doing google I feel there is compatibility issue. Therefore I want to make the database compatibile to SQL Server 2000.

And run the below query

ALTER DATABASE  DBNAME 
SET COMPATIBILITY_LEVEL = 80

but nothing help. Any help will be appreciated. The complete error message is :

Specified cast is not valid. (SqlManagerUI)

------------------------------
Program Location:<br/>
  at <br/>Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()<br/>
   at <br/>Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()<br/>
   at <br/>Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)<br/>
   at System.Windows.Forms.Control.OnTextChanged(EventArgs e)<br/>
   at System.Windows.Forms.TextBoxBase.OnTextChanged(EventArgs e)<br/>
   at System.Windows.Forms.Control.set_Text(String value)<br/>
   at System.Windows.Forms.TextBoxBase.set_Text(String value)<br/>
   at System.Windows.Forms.TextBox.set_Text(String value)<br/>
   at <br/>Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.bu<br/>ttonSelectDevice_Click(Object sender, EventArgs e)<br/>
   at System.Windows.Forms.Control.OnClick(EventArgs e)<br/>
   at System.Windows.Forms.Button.OnClick(EventArgs e)<br/>
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)<br/>
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button,Int32 clicks)<br/>
   at System.Windows.Forms.Control.WndProc(Message& m)<br/>
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)<br/>
   at System.Windows.Forms.Button.WndProc(Message& m)<br/>
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)<br/>
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)<br/>
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, <br/><IntPtr wparam, IntPtr lparam)<br/>
  • The error message you posted does have much information. Can you show us the restore query(if you used) and complete error message. – Shanky Jul 17 '15 at 8:47
  • We have similar issue - we use DB in 2000 compatibility mode on MS SQL 2005 - we have issues in ORDER BY section. You have to find which is the query that gives the error and post its code so we can help. – Bogdan Bogdanov Jul 17 '15 at 9:40
  • how are we supposed to help on an error without the code? wich cast is invalid? beware: posting the single line with the cast is not helpful, a little context is needed. – Paolo Jul 17 '15 at 10:40
  • 1
    @poojasharma, are you saying you get the cast error during the restore? Try using a T-SQL RESTORE command instead of the GUI. – Dan Guzman Jul 17 '15 at 11:10
  • 1
    Install latest version of SSMS or your USE TSQL to restore backup. – Kin Shah Jul 17 '15 at 12:02
2

According to the following link there is a step missing:

http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

  • You must first update your SQL Server 2000 by upgrading to SP4 before making a backup to restore onto SQL Server 2008.

The TechNet link refers to moving from SQL Server 2000 to SQL Server 2012, but the first step is moving from SQL Server 2000 SP4 to SQL Server 2008.

If going all the way to a current version of SQL Server you will have to make the migration in two steps:

  • Step 1: Make a first migration from SQL Server 2000 to SQL Server 2008 for instance. You need to be on SQL Server 2000 SP4.

  • Step 2: Make a second migration from SQL Server 2008 to 2012.

|improve this answer|||||
  • the error itself is related to GUI Microsoft.SqlServer.Management.SqlManagerUI, I dont think your answer applies to this question. I would recon that if OP uses the latest version of SSMS or uses TSQL to restore then it would work. – Kin Shah Jul 17 '15 at 20:53
  • Perhaps you are right, but it seems unlikely that using the SQL Server 2000 tools to restore to a SQL Server 2008 would be smooth. – RLF Jul 17 '15 at 21:05
0

Thanks all for your comments and answers. After doing a little bit more GOOGLE, i get to know that sql 2000 can only be restored on sql 2005 database. the backup which are taken from sql 2005 and upwards can be restored on higher versoins after setting the compatibilty mode. but still i have an issue that it is taking so much time to restore even after i have used the option no recovery . can someone also help me to come out from this.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.