Backup fails for big databases

Hello

I'm seeing that the backup using SMO fails with big databases (>15GB).
I have a 20GB database and I can see the command go thru. in Profiler
but after it's about 50-70% done, I get an exception saying backup
failed. There is no details on the SQLServer log.

Here's the code snippet:

bk.Initialize = m_backupInit;
bk.PercentCompleteNotification = 10;
bk.PercentComplete += new
PercentCompleteEventHandler(bk_PercentComplete);
bk.SqlBackup(m_Server);

Here's the command as captured by SQL-Profiler:
BACKUP DATABASE [Test_MODEL] TO DISK = N'D:\Temp
\Test_Model_Backup.dat' WITH NOFORMAT, INIT, NOSKIP, REWIND,
NOUNLOAD, STATS = 10

Has anyone else seen this. The wierd thing is it works using old SQL-
DMO.

thanks
Sunit

Hi,

the standard timeout for the command is as far as I can remember 600 seconds, you can extend this by setting the following command:

Server svr = new Server(".");
svr.ConnectionContext.StatementTimeout = 6000;

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||That seems to have fixed it. Thanks a lot.

Sunit

backup and recovery of VLDB

Could someone provide guidance or stats on the large
database backup and recovery? How long does it take to
backup 20GB database? 50GB? 500GB? How long does it take
to recover the db? Any suggestions on the hardware?
Thanks in advance, GregGreg
http://www.sql-server-performance.com/backup_restore_tuning.asp
We are running WIN2003 with SQL Server2000 (4 Proc 1.4)
Backup database takes 12 min and restore 17 min (database 20G)
"Greg" <ghodosevich@.printable.com> wrote in message
news:05d901c398fd$df765d50$a301280a@.phx.gbl...
> Could someone provide guidance or stats on the large
> database backup and recovery? How long does it take to
> backup 20GB database? 50GB? 500GB? How long does it take
> to recover the db? Any suggestions on the hardware?
> Thanks in advance, Greg|||There is no specific time we can say for BACKUP & RESTORE operation.
It depends on lot of factors like Disk I/O, Network,Processors.
Just to give a idea. SQL Server 2000 is very fast in backup.If you backup a database in a single device or file, approximately it takes less than 1 min for 1GB.
We have 700GB database. We use multiple backup devices on disk. Then we copy from disk to tape seperately.
Directly copying to tape via N/W is not a good practise since it occupies lof of Network traffic and it takes too much time.
Multiple backup devices can be used for backup and restore operations. This allows Microsoft=AE SQL ServerT to use parallel I/O to increase the speed of backup and restore operations because each backup device can be written to or read from at the same time as other backup devices. For enterprises with large databases, using many backup devices can greatly reduce the time taken for backup and restore operations. SQL Server supports a maximum of 64 backup devices for a single backup operation. For VLDB's use multiple devices on different disks.
The backup device must be same. (For example, all must be disk or all must be tape. You cant mix the devices)
In your case, I can say that 20GB is not a VLDB for SQL Server. Generally it wont take more than 20 min to backup to a single device.
For Restore, make the restore operation faster, use the following steps.
1. Run the create database command from Query Analyser with little more size than the actual database size.
2. Then Restore backup from local harddisk instead from tape. (Always have a backup copy in disk)
FYI:
--
BACKUP DATABASE pubs To [Test_Device1.bak],
[TestDevice2.bak]
Restore Database pubs from [Test_Device1.bak],
[TestDevice2.bak]
Hope this helps.
-SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>Could someone provide guidance or stats on the large >database backup and recovery? How long does it take to >backup 20GB database? 50GB? 500GB? How long does it take >to recover the db? Any suggestions on the hardware?
>Thanks in advance, Greg
>.
>