Solved

backups for SQLServer

Posted on 2011-03-23
8
211 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
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
 

Author Comment

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

Thanks again,
Jerry
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL page split per second is high 19 71
Excel - Save a copy of work book 13 82
How to hault or freeze parent form when a 2d form is open in vb6 3 35
vbModal 12 31
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…

939 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now