?
Solved

SQL Server 2000 Backup File Size is Excessive

Posted on 2009-04-19
10
Medium Priority
?
741 Views
Last Modified: 2012-08-14
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
Comment
Question by:ZekeLA
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 8

Assisted Solution

by:SNilsson
SNilsson earned 400 total points
ID: 24181961
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24182111
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24182130
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Accepted Solution

by:
RiteshShah earned 1200 total points
ID: 24182149
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
 
LVL 1

Author Comment

by:ZekeLA
ID: 24184030
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
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 1200 total points
ID: 24184054
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 400 total points
ID: 24184060
an article i wrote on the subject:  
http://blogs.techrepublic.com.com/datacenter/?p=448
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 24184076
There is always trusty old MS, here is for the full backup:
http://msdn.microsoft.com/en-us/library/ms190217.aspx
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24184089
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
 
LVL 1

Author Closing Comment

by:ZekeLA
ID: 31572112
Thank you all. I'll be implementing a more comprehensive backup strategy once I fix the immediate issue.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

840 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