| Author |
Topic |
|
jay1
Starting Member
17 Posts |
 Posted - 2010-08-21 : 03:58:40
|
| 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 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-08-21 : 21:22:36
|
| Error 112 is disk out of space. Are you backing up your differentials to the same file and appending (WITH NOINIT)?When, and how often - do you perform full backups and transaction log backups? |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
jay1
Starting Member
17 Posts |
Posted - 2010-08-23 : 11:39:23
|
| Thank you for your reply. I am using the following script onlyBACKUP DATABASE 'Databasename'TO DISK = 'E:\'filename'.BAK' WITH DIFFERENTIALI am running the job daily, for all 10 Databases and I don't perform any transaction log backup.Thank you. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-08-23 : 22:10:48
|
| That command will append your backups to the same file - and that file will continue to grow until you run out of space on your disk.A better method would be to make sure you use a different file for each backup. Generally, this is done by using a date/time stamp on the filename - and then using a utility to delete old backup files after some period.If your databases are set with full recovery model - you are going to end up running out of space for your logs. In full recovery model, you have to perform frequent transaction log backups (at least every hour, if not more often). If your databases are in simple recovery - then you are okay and don't need to perform transaction log backups. |
|
|
|
jay1
Starting Member
17 Posts |
Posted - 2010-08-24 : 05:09:46
|
quote:
Originally posted by jeffw8713
That command will append your backups to the same file - and that file will continue to grow until you run out of space on your disk.A better method would be to make sure you use a different file for each backup. Generally, this is done by using a date/time stamp on the filename - and then using a utility to delete old backup files after some period.If your databases are set with full recovery model - you are going to end up running out of space for your logs. In full recovery model, you have to perform frequent transaction log backups (at least every hour, if not more often). If your databases are in simple recovery - then you are okay and don't need to perform transaction log backups.
All the Databases are set to Simple recovery. I was wondering if using Incremental back up would be any better or not please?Thank you. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-24 : 06:37:40
|
quote:
Originally posted by jay1
quote:
Originally posted by jeffw8713
That command will append your backups to the same file - and that file will continue to grow until you run out of space on your disk.A better method would be to make sure you use a different file for each backup. Generally, this is done by using a date/time stamp on the filename - and then using a utility to delete old backup files after some period.If your databases are set with full recovery model - you are going to end up running out of space for your logs. In full recovery model, you have to perform frequent transaction log backups (at least every hour, if not more often). If your databases are in simple recovery - then you are okay and don't need to perform transaction log backups.
All the Databases are set to Simple recovery. I was wondering if using Incremental back up would be any better or not please?Thank you.
You can't run an incremental backup when the database is using SIMPLE recovery model. Whether or not it is better depends on your data recovery requirement. We run full backups, differential backups, and incremental backups (tlog). So we run all 3 types.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jay1
Starting Member
17 Posts |
Posted - 2010-08-24 : 06:56:09
|
quote:
Originally posted by tkizer
quote:
Originally posted by jay1
quote:
Originally posted by jeffw8713
That command will append your backups to the same file - and that file will continue to grow until you run out of space on your disk.A better method would be to make sure you use a different file for each backup. Generally, this is done by using a date/time stamp on the filename - and then using a utility to delete old backup files after some period.If your databases are set with full recovery model - you are going to end up running out of space for your logs. In full recovery model, you have to perform frequent transaction log backups (at least every hour, if not more often). If your databases are in simple recovery - then you are okay and don't need to perform transaction log backups.
All the Databases are set to Simple recovery. I was wondering if using Incremental back up would be any better or not please?Thank you.
You can't run an incremental backup when the database is using SIMPLE recovery model. Whether or not it is better depends on your data recovery requirement. We run full backups, differential backups, and incremental backups (tlog). So we run all 3 types.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
All the Databases are set to Simple recovery so is the best solution the one that @@jeffw8713 suggested; which is to use a different file for each backup or are there another options. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
External Backup keeps throwing error message