Backup fails using SMO with big databases

Hello
I'm seeing the backup using SMO fails with big databases (>15GB). I
have a 2GB 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
SunitOn Feb 8, 11:42 pm, "sjoshi" <sjo...@.ingr.comwrote:

Quote:

Originally Posted by

Hello
I'm seeing the backup using SMO fails with big databases (>15GB). I
have a 2GB 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

I would check the event log as well, there may more information there.
I have too encountered situations which are similair, however, never
where there was absolutely no information in any of the typical log
locations. 15 gig is not paticulary large, I am sure there must be
more to it. What is the result when you execute via OSQL prompt?
Cheers,
Gsql

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 Restore to Different Database Servers

I need to programmatically (C# w/ smo) backup a database on one SQL server
and restore the backup on a different SQL server. The problem I have is that
I cannot guarantee that either SQL Server is running as a domain user (either
one may be running as Local System). This results in "Access Denied" errors
when attempting to backup to (or restore from) a spot one of the servers
cannot see. In this situation, is there any way to programmatically backup
and restore an entire database from one SQL server to another?
Thanks.The backup file has to "land" somewhere. I.e., produced by one instance and read by the other
instance. And those instance need permissions to write/read the backup file.
Of course, you can have an intermediate party pick up the file written by source instance and
copying it to somewhere else where then the destination instance need permissions. Now the
intermediate party need permissions on both these locations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stevep" <stevep@.discussions.microsoft.com> wrote in message
news:5907BBED-870F-4928-9B92-842E2A99DA17@.microsoft.com...
>I need to programmatically (C# w/ smo) backup a database on one SQL server
> and restore the backup on a different SQL server. The problem I have is that
> I cannot guarantee that either SQL Server is running as a domain user (either
> one may be running as Local System). This results in "Access Denied" errors
> when attempting to backup to (or restore from) a spot one of the servers
> cannot see. In this situation, is there any way to programmatically backup
> and restore an entire database from one SQL server to another?
> Thanks.

Backup and Restore to Different Database Servers

I need to programmatically (C# w/ smo) backup a database on one SQL server
and restore the backup on a different SQL server. The problem I have is tha
t
I cannot guarantee that either SQL Server is running as a domain user (eithe
r
one may be running as Local System). This results in "Access Denied" errors
when attempting to backup to (or restore from) a spot one of the servers
cannot see. In this situation, is there any way to programmatically backup
and restore an entire database from one SQL server to another?
Thanks.The backup file has to "land" somewhere. I.e., produced by one instance and
read by the other
instance. And those instance need permissions to write/read the backup file.
Of course, you can have an intermediate party pick up the file written by so
urce instance and
copying it to somewhere else where then the destination instance need permis
sions. Now the
intermediate party need permissions on both these locations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stevep" <stevep@.discussions.microsoft.com> wrote in message
news:5907BBED-870F-4928-9B92-842E2A99DA17@.microsoft.com...
>I need to programmatically (C# w/ smo) backup a database on one SQL server
> and restore the backup on a different SQL server. The problem I have is t
hat
> I cannot guarantee that either SQL Server is running as a domain user (eit
her
> one may be running as Local System). This results in "Access Denied" erro
rs
> when attempting to backup to (or restore from) a spot one of the servers
> cannot see. In this situation, is there any way to programmatically backup
> and restore an entire database from one SQL server to another?
> Thanks.