Unable to restore my backup due to missing tabledata

pdj
Member Posts: 643
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)
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)
Regards
Peter
Peter
0
Comments
-
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.0
-
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?Regards
Peter0 -
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)?0 -
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.Regards
Peter0 -
kine wrote:It is creating temporary tables for the company.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)?Regards
Peter0 -
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?0 -
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.Regards
Peter0 -
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.).0
-
pdj wrote:... so I had to cancel the restore process. When I started it again this morning the error showed up again...
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions