Unable to restore my backup due to missing tabledata

pdj

My customer and me are running NAV4SP3 and SQL2000.
I have been making some development in my DB that hasn't been given to the customer yet, but for testing purposes I wish to import recent data from the customer in my dev db. I'm just using the NAV backup/restore functionality, and it usually works fine.

However; today it gives me the error "TableData 1000000003 is missing" (directly translated)

First I thought is was due to SIFT or even tables I had deleted in my dev db, but now I have imported all tables from the customer db, and I still get the error. Any ideas why I get this error? (It shows up right after all the tables are created, so it only takes half a minute to re-produce)

kine

It seems that some restore of backup was not finished correctly. ID 1000000003 and more are used during backup restore to keep restored data before the objects are imported.

pdj

I have used my db for dev the last weeks without problems. Today I just deleted the Company and wanted to import the recent backup.
I tried enabling the Query Profiler and these are the last commands:

CREATE TABLE "My-DEV"."dbo"."0000000000B61C2E$Payment Terms1000000003" ("timestamp" ROWVERSION NOT NULL,"Code" VARCHAR(10) NOT NULL,"Due Date Calculation" VARCHAR(32) NOT NULL,"Discount Date Calculation" VARCHAR(32) NOT NULL,"Discount %" DECIMAL(38,20) NOT NULL,"Description" VARCHAR(50) NOT NULL,"Calc_ Pmt_ Disc_ on Cr_ Memos" TINYINT NOT NULL,"OIOXML Code" INTEGER NOT NULL CONSTRAINT "0000000000B61C2E$Payment Terms1000000003$0" PRIMARY KEY CLUSTERED ("Code"))

exec sp_executesql N'SELECT O.[id], O.[type], CASE WHEN C.[name] IS NULL THEN 0 ELSE 1 END AS [primkey] FROM "My-DEV".[dbo].[sysobjects] AS O LEFT OUTER JOIN "My-DEV".[dbo].[sysobjects] AS C ON (O.id = C.parent_obj AND C.xtype = ''PK'') WHERE O.[name] = @P1', N'@P1 nvarchar(40)', N'0000000000B61C2E$Payment Terms1000000003'

GRANT ALL ON "My-DEV"."dbo"."0000000000B61C2E$Payment Terms1000000003" TO [$ndo$shadow]

exec sp_cursorfetch 180150029, 2, 0, 80

exec sp_cursorclose 180150029

exec sp_execute 29, 1, '', 1000000003

exec sp_execute 27, 1, '', 1000000003

exec sp_executesql N'SELECT O.[id], O.[type], CASE WHEN C.[name] IS NULL THEN 0 ELSE 1 END AS [primkey] FROM "My-DEV".[dbo].[sysobjects] AS O LEFT OUTER JOIN "My-DEV".[dbo].[sysobjects] AS C ON (O.id = C.parent_obj AND C.xtype = ''PK'') WHERE O.[name] = @P1', N'@P1 nvarchar(31)', N'MyCompany$Payment Terms1000000003'

@TRANCOUNT > 0 ROLLBACK TRAN

And that's it. Any idea?

kine

It is creating temporary tables for the company.

Can you post the full error text (if it is in another language, change language for your account on MS SQL or language in NAV client)?

pdj

I just tried restoring the previous backup I had and it gave the same error. I know for sure this was the last backup I had restored, so the problem is not in the backup but somewhere in my dev db.

Since I have to get some work done I had to create a new database and there I first imported a backup of my dev db (objects and other shared tables). Then there were no problem in restoring the customer backup to get recent data. (Well, at least it got further - it is still restoring)

So I'm running again - just don't like not knowing what happened.

pdj

kine wrote:

It is creating temporary tables for the company.

Really? I thought it was creating SIFT bucket tables. Do you know what it is going to use these temporary tables for?

kine wrote:

Can you post the full error text (if it is in another language, change language for your account on MS SQL or language in NAV client)?

The only errormsg I get in NAV is "TableData 1000000003 does not exist" (got my language changed) Anywhere else I can look for a more detailed error? (Have already tried in the Event Log and SQL Server Log)

kine

If you look detaily to restore process, first step is restore of TableData (data in tables), but because in this time NAV have no objects restored, the data are stored in temporary tables with ID shifted by 1000000000. The structure is same as in original DB, but just with definition of fields. After the data are restored, backup continues with restore of objects. In this step objects are imported. After that, the data are moved to definite tables (are renamed or moved, I do not know).

The error can be problem of permission. Check if you are restoring the DB as DBOwner or sysadmin.

Did you restore the backup into new DB when you got the error?

pdj

Thanks for the info about the restore process. In my case are the objects already in the database, so I don't quite understand why it doesn't restore directly to the right tables.

Well, I unfortunately managed to reproduce the problem in my new database. I had to leave from the office yesterday before it was finished restoring, so I had to cancel the restore process. When I started it again this morning the error showed up again. Then I made a new db again, and it is restoring right now.

The problem seems to be related to the state it leaves the database when canceling a restore when the db already has objects but no companies.

Edit: Based on the above I don't consider permissions to be an issue.

kine

The restore is processed on temporary tables because the structure of the data can be different from actual objects in your DB. After the data are restored, the actual table format is applied to these restored tables and the tables are moved into target IDs. During that process the "compatibility" of the structure is tested (e.g. deleted fields, deleted table objects etc.).

DenSter

pdj wrote:

... so I had to cancel the restore process. When I started it again this morning the error showed up again...

That's exactly what causes the error.

If you cancel the restore, you should be able to continue it, but depending on where yu cancel it, you might break it altogether. In a long restore that sucks, but it's not strange that the database is in an inconsistent state when you cancel a restore process.