none
more backup/restore woes RRS feed

  • Question

  • hello all,
    I was wondering if someone can help me with a pesky backup/restore problem:  basically, a number of databases that I have, after backing them up, they are not restoring properly, ie, they restored w/ the large fact table files corrupted.

    So, I have machine where I build the project, all MDX statements run fine, I take a backup of the database and restore it on two other machines, the databases are corrupted.  So, I have no way of knowing if the backup process is corrupted or the two restore jobs goof off.  One of the 'restore machines' is almost identical in every way with the process machine, the other one is an AMD machine  -- the backup/restore fails on both of these machines.

    Any thoughts on what might be causing this? 
    thx a big bunch,
    Cos
    Monday, August 4, 2008 4:50 PM

Answers

  • First - is to make sure you upgraded to the latest service pack release for Analysis Services.

    Second - it is possible something wrong with your original database. You might be only seeing this problem as you are trying to restore. So before you put your fresh backup file onto permanent storage try and restore it with different database name.

    This way you can verify your restore works.

     

    As for trying to restore on different platforms- Analysis Services backup is platform agnostic. I am not surprized you are seeing the same failure on both machines.

     

    Edward Melomed.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, August 6, 2008 6:26 AM
  • Hi Cos
    Seems like you have done alot of investigation. We are having the exact problem as described by Edward. From my findings I have found out that the SSAS 2005 current design only allows around 2G file for the partition file so that the backup/restore will work correctly.

    To solve the issue you can try the following:

    1. Partition the files less than 2G.
    2. Try using synchronization services http://msdn.microsoft.com/en-us/library/ms174488.aspx.
    Monday, March 2, 2009 10:19 PM

All replies

  • First - is to make sure you upgraded to the latest service pack release for Analysis Services.

    Second - it is possible something wrong with your original database. You might be only seeing this problem as you are trying to restore. So before you put your fresh backup file onto permanent storage try and restore it with different database name.

    This way you can verify your restore works.

     

    As for trying to restore on different platforms- Analysis Services backup is platform agnostic. I am not surprized you are seeing the same failure on both machines.

     

    Edward Melomed.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Wednesday, August 6, 2008 6:26 AM
  • hi Edward,
    I've done a bit more research into the problem (I hope I reduced it to the simplest test):

    1.  We have MS SQL Server Developer Edition 2005 SP2 CU 4 (a development laptop) --32 bit.
    2.  I have a cube of over 6 gb in size, created on this laptop, with 3 gb ram.  I built the cube from scratch -- and it works fine.
    3.  I successfully run a query like: 

    with MEMBER [Measures].[BLANK] as ""
    select [Measures].[BLANK] on 0,
    NonEmpty([promo].promo.Members) on 1
    from [trcube]

    4.  I backup the database, on the same laptop.
    5.  I successfully restore the same file onto a different, brand new database name, on the same machine.
    6.  I run the query above, I get:

    Executing the query ...
    File system error: The following file is corrupted: Physical file: \\?\c:\ASData\DBDemoRestorable.0.db\FIN 1.0.cub\Tran Summ.0.det\Fact Tran Summ.0.prt\1.fact.data. Logical file .
    Execution complete

    (I obfuscated some names here).    is  \\?\c:\   something valid?  E:\ is the correct drive letter and ASData is the correct folder.

    1.fact.data is the largest fact file, of over 4gb, which apparently gets corrupted, either when it gets backed up or when it gets restored..

    I have the exact same type of issue on 2 other machines, running Standard Edition.
    Whether I try a backup/restore within SSMS or a backup/restore within SSIS, creating a project doing the backup and restore with components available there, the databases, as restored and queried, come up as corrupted, with a typical message as above.

    Another issue I've discovered is, the same project, if I build it using only a sample of the data for the fact table, let's say 100,000 records, it builds, backs up and restores fine.  If i use ~200,000,000 records for the fact table (no partitions) --the entire recordset and generate the 6gb cube, I have that problem.  I have not investigated whether there is a barrier on around the 2gb cube size.... there should not be any.... We're also using NTFS (no FAT 32) so that should not be a concern. 
     
    Latest Findings: 
    a)  saw in one of the newsgroup posts that there might be a 'limitation on the fact file size' of aroung 4gb so I proceeded to partition the build to create smaller files, on a 32 bit system.  Still no go, after build/restore, same error type as above.
    b)  Did a binary check using fc /b  on the two large fact files, the pre backup and the post restore and confirmed that they ARE different.
     
    Interestingly, latest test, I run the same project on a 64 bit system (AS v  9.00.3042.00) w/ 8gb RAM and voila, the build/backup/restore worked without a problem.  So, what could be the explanation?  (one thing I've noticed is that the compression size of the backup on the 64 bit is about 40% larger than the same backup on the 32 bit system ... which might indicate that I'm not working w/ the same SQL Server release source code? --my system is a x64 system based off of the new Intel Q6600 quad CPU which has the EMT64 extensions) 

    Any insight into this apparent corruption (on 32 bit systems?)    ?

    thx much,
    Cos
    Wednesday, August 6, 2008 6:12 PM
  • Hi Cos
    Seems like you have done alot of investigation. We are having the exact problem as described by Edward. From my findings I have found out that the SSAS 2005 current design only allows around 2G file for the partition file so that the backup/restore will work correctly.

    To solve the issue you can try the following:

    1. Partition the files less than 2G.
    2. Try using synchronization services http://msdn.microsoft.com/en-us/library/ms174488.aspx.
    Monday, March 2, 2009 10:19 PM