Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Unable to restore a large backup database

Author  Topic 

Richard_Tsai
Starting Member

4 Posts

Posted - 2007-05-10 : 02:08:32
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

22859 Posts

Posted - 2007-05-10 : 04:29:47
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 - 2007-05-10 : 05:10:31
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

22859 Posts

Posted - 2007-05-10 : 05:15:11
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 - 2007-05-10 : 05:50:19
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 - 2007-05-15 : 03:04:43
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
Master Smack Fu Yak Hacker

7266 Posts

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

Kristen
Test

22859 Posts

Posted - 2007-05-16 : 02:56:13
"I think HJ-Split didn't work for large file"

Sadly I'm not surprised
Go to Top of Page
   

- Advertisement -