Link to home
Create AccountLog in
Avatar of IT_Service
IT_ServiceFlag for Canada

asked on

Move SQL Database - getting error

I'm getting an error when I try to restore a database to a new server.

Source Server:
Windows 2003 Standard - 32 bit
MS SQL 2000 Standard

Target Server
Windows 2008 R2 - 64 bit
MS SQL 2008 Web Edition - 64 bit

I created a backup of the database in Enterprise Manager, choosing the  "Database - Complete" option. I FTP'd this database over to the new server.

I created the File path on the new server: D:\SQL Data\MSSQL\Data\
as that was where the source .mdf and .ldf files were sitting.

When I attempt a restore, I choose the backup.bak file as the device, and when I click OK to accept the device I get this error:

Cannot find table 0. (System.Data)

The technical details are as follows:

===================================

Cannot find table 0. (System.Data)

------------------------------
Program Location:

   at System.Data.DataTableCollection.get_Item(Int32 index)
   at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv)
   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)

************************************

What could the problem be?

thanks,
IT_Service

Avatar of imran_fast
imran_fast



On the remote server where you are trying to restore database change the service account Log On as to Administrator rather than Local System account
Try to use commands:
for backup:
c:\> sqlcmd -i "backup.sql"
where backup.sql is in c:\ drive and the file contains

BACKUP DATABASE <DataBaseName> TO DISK = 'c:\<backupFile>.bak'
GO

and then similarly for restore
c:\> sqlcmd -i "restore.sql"
where restore.sql file is in c:\ and contains

RESTORE DATABASE <DataBaseName> FROM DISK = 'c:\<backupFile>.bak' WITH REPLACE
GO
Avatar of IT_Service

ASKER

@irman,

 The server was set up by a third party dedicated server hosting facility.

Looks like they created a local MSSQL_USER account for the service to use.

I tried adding this user to the Administrators group and restoring the DB again, but got the same error.

Do you think changing the service account to use Administrator would still yield different results?

thanks!
I think now that this might actually be due to some corruption with the bak file. I will have to test to make sure.

thanks for the help so far.
Is this user member of sysadmin in sql server itself.
ASKER CERTIFIED SOLUTION
Avatar of IT_Service
IT_Service
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I resolved the issue myself.

Thanks for the help.