Backup Options - low disk space

Howdy,

We have very a tight limit on disk space for backups.

We have a 3 GB db that we do full a weekly full backup on, followed by hourly tran log backups for the rest of the week - this adds up to a LOT of disk space usage ( we cant do differential backups due to space limitations, as diffs are almost 3 GB in size... ).
As disk space is limited, we can only keep a weeks worth of backups on the disk, and previous weeks get backed up onto tape. No big deal there....

So, when weekly full backup occurs, the only way to recover the previous weeks data is to restore previous full backup & allpy the required number of tran logs.

Is there another way to do backups perhaps using file backups , so we dont have to go all the way back to the previous weeks backups?

Thanks in advance,

SG.Do you have a tape device on the machine? If so you could copy the backup off every night and run a full backup every night...if this is an important server, buy more disks!! I bought an external 80gb drive for $90, I would push back and have the company buy more space.

HTH|||Hi,

Thanks - I have suggested to the clients the disk space option is the best way of going, I just wanted someone else to say it too. I figure its not worth the grief for skimping on disk space for the sake of operational integrity.

Cheers

SG|||Hi,
If you haven't yet bought hard disk, you could do this. You could schedule the full backups daily and 'overwrite the existing media' instead of appending. Also you can delete the transactional log after the successful backup at the end of the day.
You could continue with the weekly tape backups too. If you need to restore, you need to the full with no recovery and the tran log for just the previous day. Ofcourse if the restoration is beyond a days time, you will need to use the tape, previous days full backup and transaction logs.

Hope this help you.

Regards,

Backup of Log fails.

It appears that the data is backing up fine but logs do not. Obviously something has changed but I cannot see what. I have plenty of space and privs but the backups fail with the below errorlog. If anyone can point me in a new direction I would greatly appreciate it.

2005-12-31 08:22:34.16 spid11 Analysis of database 'CEMS_PRO' (7) is 100% complete (approximately 0 more seconds)
2005-12-31 08:22:34.25 spid5 Starting up database 'tempdb'.
2005-12-31 08:22:34.58 spid3 Recovery complete.
2005-12-31 08:22:34.58 spid3 SQL global counter collection task is created.
2005-12-31 08:22:36.22 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2005-12-31 09:11:52.18 spid173 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2005-12-31 09:11:54.85 spid173 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'.
2005-12-31 21:00:00.75 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 22:00:00.92 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 23:00:00.86 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 00:45:00.91 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH INIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 02:25:22.67 backup Database backed up: Database: CEMS_PRO, creation date(time): 2005/01/17(12:36:00), pages dumped: 17259208, first LSN: 45585:18685:21, last LSN: 45586:81369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'CEMS_Backup'}).
2006-01-01 02:25:23.03 backup BACKUP failed to complete the command backup log CEMS_PRO TO CEMS_Log_Back2 WITH INIT , NOUNLOAD , NAME = N'CEMS_Log_Backup', NOSKIP , STATS = 10, NOFORMAT

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

Backup of Log fails.

It appears that the data is backing up fine but logs do not. Obviously something has changed but I cannot see what. I have plenty of space and privs but the backups fail with the below errorlog. If anyone can point me in a new direction I would greatly appreciate it.

2005-12-31 08:22:34.16 spid11 Analysis of database 'CEMS_PRO' (7) is 100% complete (approximately 0 more seconds)
2005-12-31 08:22:34.25 spid5 Starting up database 'tempdb'.
2005-12-31 08:22:34.58 spid3 Recovery complete.
2005-12-31 08:22:34.58 spid3 SQL global counter collection task is created.
2005-12-31 08:22:36.22 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2005-12-31 09:11:52.18 spid173 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2005-12-31 09:11:54.85 spid173 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'.
2005-12-31 21:00:00.75 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 22:00:00.92 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 23:00:00.86 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 00:45:00.91 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH INIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 02:25:22.67 backup Database backed up: Database: CEMS_PRO, creation date(time): 2005/01/17(12:36:00), pages dumped: 17259208, first LSN: 45585:18685:21, last LSN: 45586:81369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'CEMS_Backup'}).
2006-01-01 02:25:23.03 backup BACKUP failed to complete the command backup log CEMS_PRO TO CEMS_Log_Back2 WITH INIT , NOUNLOAD , NAME = N'CEMS_Log_Backup', NOSKIP , STATS = 10, NOFORMAT

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

Backup files

Hi,
I have a scheduled maintenance plan for backing up my database.
I have recently ran out of space and I need to delete some old back up files.

Question:
if I make a backup of a database (full backup) if I want to restore my databse using the transaction logs I can only go back to the latest backup?
If so, why should I keep my old backup files?
Is there any way to tell sql server to overwrite backup files each time it makes a back up and is this a good idea?

thanksWhat I have done here is to have two scheduled maintenance plans for backing up. One that runs every 24 hours that overwrites every time, and a second that appends an incremental every three hours. We then store everything on tape for two weeks.

When you set up the job there is a radio button group to select overwrite or append. If you want to edit your job step change NOINIT to INIT and then the job will overwrite.

Originally posted by Sia
Hi,
I have a scheduled maintenance plan for backing up my database.
I have recently ran out of space and I need to delete some old back up files.

Question:
if I make a backup of a database (full backup) if I want to restore my databse using the transaction logs I can only go back to the latest backup?
If so, why should I keep my old backup files?
Is there any way to tell sql server to overwrite backup files each time it makes a back up and is this a good idea?

thanks