Solved

backups for SQLServer

Posted on 2011-03-23
8
215 Views
Last Modified: 2012-05-11
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
Comment
Question by:JDL129
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 35198578
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
 
LVL 2

Expert Comment

by:jimponder
ID: 35198662
Are the transaction log files being backed up as well?  If so, are they cleaned/deleted every so often?
0
 
LVL 11

Expert Comment

by:kbirecki
ID: 35203534
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:JDL129
ID: 35204082
Thanks for the responses guys!!  Will get back to you tomorrow evening.

Thanks again,
Jerry
0
 

Author Comment

by:JDL129
ID: 35216335
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
 
LVL 2

Assisted Solution

by:jimponder
jimponder earned 125 total points
ID: 35216565
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
 

Author Comment

by:JDL129
ID: 35218197
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
 

Author Closing Comment

by:JDL129
ID: 35245289
Thanks for the responses!!!!!!!!!!!

Jerry
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question