• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

SQL Server 2000 Backup File Size is Excessive

When applying changes to our web site's database, I make a backup first in case I need to roll back the changes. The database is at about 5.5 Gb in size. Until now, my manual backups have always created a backup file sized about 75 Mb. There is also a daily job which creates the backup around midnight each day and that backup file is also about 75 Mb.

But tonight, for some reason, the manual backup I generated came out to about 5 Gb in size. This took that drive to less than 5% free disk space which is how I found out about the problem.

As far as I know I created the manual backup the same as always. I verify upon completion, overwrite existing media, and save to a new disk file. I'm wondering if I did append instead of overwrite or forgot to remove the existing filename when entering the new one. Even so, I would only expect a doubling of the file size, not the full database sizing.

Can someone explain or guess at what I did or what could have happened? Thanks.
0
ZekeLA
Asked:
ZekeLA
  • 4
  • 2
  • 2
  • +1
4 Solutions
 
SNilssonCommented:
0
 
RiteshShahCommented:
small note of advice, actually backup strategy should be something like take differential backup, transaction backup and full backup. you should run transaction backup every 5-20 minutes, differential backup every 1 hour and full backup should be at every 24 hours. Since your database is small, these time frames are pretty good for you. SNilsson is right, this is because of log files and you can first backup your log file and than try getting full backup.


0
 
chapmandewCommented:
More frequent trans log backups are necessary.  It is preemptive for RiteshShah to suggest a timeframe, because one cannot  be established at this point.  But, the more often you do trans log backups, the better.  The differentials aren't really going to help in terms of backup size, it will just make it easier to recover to a point in time.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
RiteshShahCommented:
in short, you can run following script right now and you will be done.

don't forget to change path and database name, moreover, you have to have write permission on the folder you specified for backup.
--first transaction backup
BACKUP LOG AdventureWorks --database name
 TO  DISK = 'D:\Ritesh\adv-log.trn' --path and filename where you want to put your backup,
 WITH 
 NOUNLOAD, SKIP, NOFORMAT 
 
--full backup
BACKUP DATABASE AdventureWorks
 TO  DISK = 'D:\Ritesh\adv-fullbackup.bak'
 WITH INIT,  
 NOUNLOAD, SKIP, NOFORMAT 

Open in new window

0
 
ZekeLAAuthor Commented:
I looked further into this and it was the log file that had grown excessive. Apparently, SQL Agent stopped running about 10 days ago. Our daily backup job is run via SQL Agent which is the cause of the problem.

RiteshShah and all, if I want to implement a more frequent backup strategy, it looks like I would have to track multiple backup files so I could do succesive sequential restores in the event of a failure. Can any of you explain further or provide any links to backup strategies that would help me to configure things better?

Thanks.
0
 
RiteshShahCommented:
sure, no problem.



----------------------------------------------------------------------------
-- Transaction Log Backup script
-- Should be scheduled to run every 10 min
---------------------------------------------------------------------------- 
DECLARE @BackupFolder AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @DBName AS varchar(250);
SET @DBName = 'SMXP';
SET @CurDT = getdate();
IF NOT EXISTS(select * from master.dbo.sysprocesses where cmd = 'BACKUP DATABASE' and DB_NAME(dbid) = @DBName)
BEGIN
 SET @BackupFolder = 'j:\SQL-BACKUP\';
 SET @BackupFolder = @BackupFolder + @DBName + '\';
 EXEC master..SMXP_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';
 EXEC master..SMXP_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(second,@CurDT) AS varchar(2))+'.trn'; 
 BACKUP LOG @DBName
 TO  DISK = @BackupFolder
 WITH 
 NOUNLOAD, SKIP, NOFORMAT 
END
ELSE 
 print 'Database backup in process! Skiping...'
 
----------------------------------------------------------------------------
-- Differential Backup script
-- Should be scheduled to run hourly
---------------------------------------------------------------------------- 
DECLARE @BackupFolder AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @DBName AS varchar(250);
 
SET @DBName = 'SMXP';
SET @CurDT = getdate();
IF NOT EXISTS(select * from master.dbo.sysprocesses where cmd = 'BACKUP DATABASE' and DB_NAME(dbid) = @DBName)
BEGIN
 SET @BackupFolder = 'J:\SQL-BACKUP\';
 SET @BackupFolder = @BackupFolder + @DBName + '\';
 EXEC master..SMXP_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';
 EXEC master..SMXP_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(second,@CurDT) AS varchar(2))+'.dif'; 
 BACKUP DATABASE @DBName
 TO  DISK = @BackupFolder 
 WITH DIFFERENTIAL,  
 NOUNLOAD, SKIP, NOFORMAT 
END
ELSE 
 print 'Database backup in process! Skiping...'
 
----------------------------------------------------------------------------
-- Full Backup
-- Should be scheduled to run daily
---------------------------------------------------------------------------- 
DECLARE @BackupFolder AS varchar(250);
DECLARE @Path AS varchar(250);
DECLARE @DBName AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @Status AS int;
SET @CurDT = getdate();
SET @Path = 'J:\SQL-BACKUP\';
DECLARE crDatabases CURSOR
FOR
 select name from master..sysdatabases where name not in ('tempdb','Northwind','pubs','model','EDDVOA','ASPState')
OPEN crDatabases
FETCH NEXT FROM crDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
 SET @BackupFolder = @Path + @DBName + '\';
 EXEC master..SMXP_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';
 EXEC master..SMXP_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.0'+'.bak';
 BACKUP DATABASE @DBName
 TO  DISK = @BackupFolder 
 WITH INIT,  
 NOUNLOAD, SKIP, NOFORMAT 
   FETCH NEXT FROM crDatabases INTO @DBName
END
CLOSE crDatabases
DEALLOCATE crDatabases
 
--------------------------------------------------------------------------
-- Deleting 1 days old backups....
--------------------------------------------------------------------------
DECLARE @BackupFolder2DaysBack AS varchar(250); 
DECLARE @BackupFolder1DayBack AS varchar(250);
DECLARE @Path AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @PrevDT AS datetime;
DECLARE @DBName AS varchar(250);
SET @Path = 'E:\BACKUP\';
SET @CurDT = DateAdd(Day,-1,getdate());
SET @PrevDT = DateAdd(Day,-2,getdate());
-- Loop through databases
DECLARE delExpDatabases CURSOR
FOR
 select name from master..sysdatabases where name not in ('tempdb','Northwind','pubs','model','CEIAS','EDDVOA','ASPState') 
OPEN delExpDatabases
FETCH NEXT FROM delExpDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
 SET @BackupFolder2DaysBack = @Path + @DBName + '\' + CONVERT(varchar(10), @PrevDT, 110);
 SET @BackupFolder1DayBack = @Path + @DBName + '\' + CONVERT(varchar(10), @CurDT, 110); 
 DECLARE @cmdStr AS varchar(250);
 SET @cmdStr = 'rmdir /Q /S '+@BackupFolder2DaysBack
 --print @cmdStr
 EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
 SET @cmdStr = 'ERASE /Q /S '+@BackupFolder1DayBack+'\*.dif '+@BackupFolder1DayBack+'\*.trn' 
 --print @cmdStr
 EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
   FETCH NEXT FROM delExpDatabases INTO @DBName
END
CLOSE delExpDatabases
DEALLOCATE delExpDatabases
 
 
-----------------------------------------------------------------------------
---- Stored Procedures to be created in Master Database which creates folder.
----------------------------------------------------------------------------- 
 
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[SMXP_CreateFolder]    Script Date: 08/06/2007 09:24:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[SMXP_CreateFolder](
    @Path varchar(255)
    )
AS
BEGIN
 SET NOCOUNT ON
 
 DECLARE @retCode as int;
 CREATE TABLE #tmpRes
 (
  IsFileExists int,
  IsDirectory int,
  IsParentExists int
 )
 INSERT INTO #tmpRes
 EXEC xp_fileexist @Path
 
 IF EXISTS(SELECT * FROM #tmpRes WHERE IsParentExists = 1 AND IsDirectory = 0)
 BEGIN
  DECLARE @cmd as Varchar (250)
  SET @Path = 'MKDIR '+@Path
  
  EXEC @retCode = xp_cmdshell @Path ,NO_OUTPUT
  IF @@ERROR <> 0 AND @retCode <> 0
  BEGIN
      GOTO Failure
  END
 END
 ELSE IF EXISTS(SELECT * FROM #tmpRes WHERE IsDirectory = 0) 
 BEGIN
  DECLARE @errMgs as Varchar (250)
  SET @errMgs = 'Unable to create folder '+@Path
  RAISERROR(@errMgs, 16, 1)
 END
 DROP TABLE #tmpRes
 SET NOCOUNT OFF
 RETURN 0
Failure:
 SET ROWCOUNT 0
 DROP TABLE #tmpRes
 SET NOCOUNT OFF
 RETURN 1
END

Open in new window

0
 
chapmandewCommented:
an article i wrote on the subject:  
http://blogs.techrepublic.com.com/datacenter/?p=448
0
 
SNilssonCommented:
There is always trusty old MS, here is for the full backup:
http://msdn.microsoft.com/en-us/library/ms190217.aspx
0
 
RiteshShahCommented:
The script I gave you, has to be set in jobs with proper login credential, it will generate, transaction log backup, differencial back and full backup. it will delete 24 hours old backup as well. When I was using sql server 2000, I have started using that script, very trusted and give good performance. set low traffic hours for full backup.
0
 
ZekeLAAuthor Commented:
Thank you all. I'll be implementing a more comprehensive backup strategy once I fix the immediate issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now