July 16, 2010 at 6:25 am
Hi,
I have been trying to a back up of 10 Databases as SQL Agent job but each time I tried to back them up only 3 or three Databases back up and the rest will just throw the following error Error 3041,Severity 16,State 1 and Operating System Error 23(error not found). I would be very grateful if someone would let me know how to solve this issue please, as I have been trying to back up the same Databases for nearly two weeks and I cannot find anything on other Forums.
Thank you in advance.
July 16, 2010 at 8:26 am
If it's of any help, Windows operating system error 23 reads as 'Data error (cyclic redundancy check)'.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 16, 2010 at 8:37 am
Error Codes can be found at http://msdn.microsoft.com/en-us/library/ms681381(v=VS.85).aspx
August 20, 2010 at 5:36 am
Hi all,
I have been backing up the same 10 databases with differential backups on the daily biases for about a month and last night, one of the databases failed to back up with the following error messages:
112(error not found) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Would someone be able to let me know why this would happen just suddenly?
Thank you
August 20, 2010 at 6:23 am
Error 112 is "not enough space on the disk"
As a wild guess, I'd say there isn't enough free space to write the backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 7:58 am
I'm using the following code BACKUP DATABASE 'database'
TO DISK = 'E:\'filename'.BAK' WITH DIFFERENTIAL, so I would only get data that has not been saved yet. The sizes of all 10 databases combined = 324.51GB, the size of the external harddrive is 453GB.
Would you advice me on what I should do different please.
Thank you!
August 20, 2010 at 8:19 am
Is this a maintenance plan? If so, what's the retention period of the backups?
Appending or overwriting?
How much space is free on the drive?
What's the frequency of your full and diff backups, and do they both go to the external?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 8:31 am
It’s not maintenance plan, I have done the back up through SQL Server agent. It takes about 1 hour and 30 minutes to back up all databases. It looks like if the backup is growing far more then it should be but all I want to do is for it to do differential back up.
There is only 18.1GB left from 453GB.
Thank you
August 20, 2010 at 8:34 am
GilaMonster (8/20/2010)
Appending or overwriting?What's the frequency of your full and diff backups, and do they both go to the external?
Remember that a diff backs up changes since the last full backup, not changes since the last diff.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2010 at 8:54 am
The Differential back up happens on the daily biases. Currently it looks like if it’s appending but I need it to backup all the data that I have not been saved since the last differential back up
August 20, 2010 at 8:56 am
Sorry I forgot to let you know that only differential back up occurs and it is on the external hard drive.
Thank you
August 20, 2010 at 11:14 am
tt-615680 (8/20/2010)
... but I need it to backup all the data that I have not been saved since the last differential back up
A single differential will do that. Differential backups back up all changes since the last full backup, not the last differential. Hence when restoring you only need the full backup and the last differential.
If the backups are appending, that's why you're running out of space. There isn't enough space to put down another copy of the changed data. 300 GB of databases (and if you haven't done a full for a while, the differentials will likely be a large portion of that) and only 18 GB of space available.
Sorry I forgot to let you know that only differential back up occurs and it is on the external hard drive.
How often do you do full backups? When was the last full backup?
Since differential backups back up all changes since the last full backup, if you don't do full backups reasonably often, the differential backups start approaching the size that a full backup would be. When that happens, there's no real gain from doing just differentials.
I know I haven't heard much, but this backup strategy doesn't seem ideal. The databases aren't all that big. What are your maintenance windows like daily and weekly? What's your data-loss allowances for these databases? Are you doing log backups at all, or are the databases in Simple recovery?
I also tend to recommend not appending backups, but creating a new file for each backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply