?
Solved

backups for SQLServer

Posted on 2011-03-23
8
Medium Priority
?
217 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 500 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

800 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