I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.
Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?
Corruption was occurring in the backup process and was not related to SQL Server itself.
I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.
Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?
Corruption was occurring in the backup process and was not related to SQL Server itself.
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'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.
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
Will backup get completed quickly (the time taken for backing up large databases) on backup device or using backup command? If backup device, why?
You are talking about 2 different things here. You use the backup command to backup the DB and the backup device to store the backed up files.
Unless I dont understand your question.
|||Hi Dinakar,I need to know which one completes backup faster, backup device or by using backup command. I heard that backup device completes backup quicker than using backup command.
Thanks|||
It's the same procedure, whether you commit it via a GUI (window menu->next->next...) or command prompt (BACKUP command) it makes no difference.
In both cases you use a backup device to backup your database. If you are a learner you'll probably use the user friendly windows menu. If you are advanced you'll probably use the SQL command: BACKUP
|||Have you checked the same on your environment?
IMHO I don't see much difference in between and it makes a lot of difference betwen normal disks & RAID based ones.
BTW do you have any issues in your environment for such behaviour, if so let us know.
|||Hi Satya,Im using RAID disks, when backing up 200GB of database, backup device completes faster than backup command. A 30 min difference..
Thanks|||If SQL Server stripes physical devices that have different input/output (I/O) throughputs, SQL Server optimizes for speed. This means that faster devices receive more backup data that is written to disk than the slower device in the same period of time. In SQL Server 2000, regardless of the I/O throughput differences, SQL Server tries to distribute the backup data evenly to the devices. In this case, the slower disk may become a backup bottleneck in terms of performance. If increasing performance is your primary goal, you must avoid using the slow disk in striping and use disks with comparable throughputs instead.
