Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

backups for SQLServer

I am backing up a database in sqlserver 2005 express using a stored procedure from vb6.  It seems like the backedup file size is different each day and not just larger.  One day it might be 2 gigs and the next day 720 meg.  I'm trying to do a full backup not an incremental.

Any ideas,

Thanks,
Jerry
0
JDL129
Asked:
JDL129
2 Solutions
 
Anthony PerkinsCommented:
Have you attempted to restore the database?  If you have not, than your backups are suspect at best.  In other words, there is simply no point in doing a full backup if you cannot restore from it.
0
 
jimponderCommented:
Are the transaction log files being backed up as well?  If so, are they cleaned/deleted every so often?
0
 
kbireckiCommented:
acperkins' suggestion is a good one.  Another idea: on the days that you get two of these wide variations, manually run a backup and compare the resulting size of this to your automated process results and see if you get the same or different results.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JDL129Author Commented:
Thanks for the responses guys!!  Will get back to you tomorrow evening.

Thanks again,
Jerry
0
 
JDL129Author Commented:
Hey guys,  Sorry I just got back!!  Below is the stored procedure I am running.  Is there a better way to create a stored procedure for this operation?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_FullBackup]
@BackupPath nvarchar(200)
As
Declare @WeekDay tinyint
     Declare @Backup_File nvarchar(200)
     Declare @Desc nvarchar(255)                
     Declare @Name nvarchar(128)                

BEGIN

     Set @Backup_File = @BackupPath + '\BarCodeSQL_' + DATENAME(weekday, CURRENT_TIMESTAMP) + '.bak'
     Set @Desc = 'Full Backup'
     Set @Name = 'Barcode FULL BACKUP'

     BACKUP DATABASE [BarCodeSQL] TO DISK = @Backup_File
      WITH
       DESCRIPTION = @Desc
     ,NAME = @Name
Return @@Error
END

Thanks for the response,
Jerry
0
 
jimponderCommented:
Let me add on to my original thought...  Do you have the auto-shrink option enabled on your transaction logs?  This would cause you size differentials.
0
 
JDL129Author Commented:
Jim, I am  a newbie at this and I'm not sure where the transaction logs are or how I would check the auto-shrink option.

Any help would certainly be appreciated!!!!!!!!!!!!!!!

Jerry
0
 
JDL129Author Commented:
Thanks for the responses!!!!!!!!!!!

Jerry
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now