Solved

backups for SQLServer

Posted on 2011-03-23
8
212 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
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…

786 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