SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Unable to restore a large backup database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Richard_Tsai
Starting Member

4 Posts

Posted - 05/10/2007 :  02:08:32  Show Profile  Reply with Quote
Hi!
I have a large database for sharepoint service 2.0 with SQL Server 2000sp3 and the size of the database is around 200GB. Recently, I tried to restore the database from its full backup file to another SQL Server, but I always get error as follows finally:
quote:

Server: Msg 3270, Level 16, State 1, Line 1
An internal consistency error occurred. Contact Technical Support for assistance.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.



My backup script is as follows:

BACKUP DATABASE [wssdb] TO  DISK = N'E:\SQL\MSSQL\BACKUP\WSSDBBackup.BAK' WITH  INIT ,  NOUNLOAD ,  NAME = N'wssdb backup',  NOSKIP ,  STATS = 10,  NOFORMAT


Restore script:

RESTORE DATABASE wssdb FROM DISK='F:\WSSDbBackup.BAK'
WITH RECOVERY, REPLACE, NOUNLOAD,
MOVE 'wssdb' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\Data\wssdb.mdf',
MOVE 'wssdb_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\Data\wssdb_log.LDF'


I have tried to do database check
dbcc checkdb
and
dbcc checkcatalog
The check result said there is no error on database.

I tried to use some different option in restore process, but the result s were the same. If you have suggestion, your suggestions are appreciated. Thanks so much!


Richard

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/10/2007 :  04:29:47  Show Profile  Reply with Quote
Is F:, on the target server, a share?

If so it might be worth copying the file local and then restoring - assuming you have spare disk space! (Using NTFS you can mark the Backup file as Compressed before you start restoring it.

Its possible that the backup file is corrupted, so worth trying to make a new backup file, and restoring that.

NOUNLOAD applies only to tape backups, but I expect that it is benign if you use it on a backup to disk. As you are using INIT then NOSKIP is not going to do anything useful ... I'm not sure that NOFORMAT serves any purpose either, and its just possible that it is causing the headers for any pre-existing file to be left alone, despite the INIT command. maybe pre-delete and existing BAK file before you make the next backup to ensure that it is indeed a fresh file?

Kristen
Go to Top of Page

Richard_Tsai
Starting Member

4 Posts

Posted - 05/10/2007 :  05:10:31  Show Profile  Reply with Quote
Hi! Kristen,
Thank you for your suggestion.
Drive F is a local drive on the target server, not a share.
Actually, I did split the large file into small pieces and join them in target server, or 200GB is unable to transfer by network share or FTP. BTW, I use HJ-Split(www.freebyte.com) to do split and join. I tried the whole process to a small database(about 10MB) and that database could be restored, successfully.
I did three times to delete old backup file, make new backup file and restore it, but I always get the same error. I also tried to use DTS to copy database to target sql server, but there were many errors while copy.
Anyway, your suggestions are appreciated.

Richard
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/10/2007 :  05:15:11  Show Profile  Reply with Quote
Might be worth finding a command line utility that will give a CRC for a file - and then use that on both source and destination to check that your Split and Join utility has worked OK. 200GB might be outside their test plan!

Dunno how long WinZip would take to make a ZIP of a 200GB file, but it has inbuilt CRC so you would know if the file got broken in transit. If can also create "chunks" to split large files. I would expect WinZip to compress by 80%-90% - so you would only have a 10-20GB file to transfer.

Kristen
Go to Top of Page

Richard_Tsai
Starting Member

4 Posts

Posted - 05/10/2007 :  05:50:19  Show Profile  Reply with Quote
Fortunately, HJ-Split has CRC checksum function.
I'm doing CRC check now, but it takes long time. Well, I didn't zip the backup file, because it may take 40 hours to compress file. Right now, I use WinRAR to zip file with fast zip option. It looks like to take about 10 hours to complete. Anyway, I'll make sure the file is not corrupted during transferring. Thanks.
Go to Top of Page

Richard_Tsai
Starting Member

4 Posts

Posted - 05/15/2007 :  03:04:43  Show Profile  Reply with Quote
Hi! Kristen,
Thanks for your great suggestion. I've figured this out.
I think HJ-Split didn't work for large file. Anyway, I used WinRAR to split and join backup file. I've successfully restored the backup file eventually and my WSS2.0 works now. Thanks WinRAR! BTW, WinZip cannot compress file over 4GB.

Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/15/2007 :  13:13:43  Show Profile  Reply with Quote
Yes you can if you registered winzip (that means paied).
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/16/2007 :  02:56:13  Show Profile  Reply with Quote
"I think HJ-Split didn't work for large file"

Sadly I'm not surprised
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000