SQL 2005 Backup Problems

[Bryan]

We have a SQL 2005 server running the following backup job:

EXECUTE master.dbo.xp_c reate_subdir
N'\\Server\Back ups\DBServerNam e\\DB_Name__MET ABASE'

GO

EXECUTE master.dbo.xp_c reate_subdir
N'\\server\Back ups\DBServerNam e\\DB_Name__MSC RM'

GO

BACKUP DATABASE [db_name__metaba se] TO disk = N'\\server\Back ups
\DBServerName\\ DB_Name__METABA SE
\DB_Name__METAB ASE_backup_2006 10261158.bak' WITH differential ,
noformat , noinit , name =
N'DB_Name__META BASE_backup_200 61026115839' , skip , rewind ,
nounload , stats = 10

GO

DECLARE @backupSetId AS INT

SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'DB_Name__META BASE'
AND backup_set_id = (SELECT MAX(backup_set_ id)
FROM msdb..backupset
WHERE database_name =
N'DB_Name__META BASE')

IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed. Backup information for database
''DB_Name__META BASE'' not found.',16,1)
END

RESTORE verifyonly FROM disk = N'\\server\Back ups\DBServerNam e\
\DB_Name__METAB ASE\DB_Name__ME TABASE_backup_2 00610261158.bak ' WITH
FILE = @backupSetId , nounload , norewind

GO

BACKUP DATABASE [db_name__mscrm] TO disk = N'\\server\Back ups
\DBServerName\\ DB_Name__MSCRM\ DB_Name__MSCRM_ backup_20061026 1158.bak'
WITH differential , noformat , noinit , name =
N'DB_Name__MSCR M_backup_200610 26115839' , skip , rewind , nounload ,
stats = 10

GO

DECLARE @backupSetId AS INT

SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'DB_Name__MSCR M'
AND backup_set_id = (SELECT MAX(backup_set_ id)
FROM msdb..backupset
WHERE database_name = N'DB_Name__MSCR M')

IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed. Backup information for database
''DB_Name__MSCR M'' not found.',16,1)
END

RESTORE verifyonly FROM disk = N'\\server\Back ups\DBServerNam e\
\DB_Name__MSCRM \DB_Name__MSCRM _backup_2006102 61158.bak' WITH FILE =
@backupSetId , nounload , norewind

This job was set up long before i started here and the problem is that
the backup file itself has grown to be over 230 GB. It does not
appear that the backup job is pruning the file. is there a way to
view the contents of this file and then prune it so we keep no more
then two weeks worth of data.

Thanks

[Greg D. Moore \(Strider\)]

"Bryan" <bs*****@gmail. comwrote in message
news:11******** **************@ y80g2000hsf.goo glegroups.com.. .

We have a SQL 2005 server running the following backup job:

I'd do several things here.

I'd probably rewrite the entire thing.

For one it has a date hard coded, which obviously will cause confusion.

Anyway, try

RESTORE FILELISTONLY from disk =
N'\\server\Back ups\DBServerNam e\\DB_Name__MET ABASE\DB_Name__ METABASE_backup _200610261158.b ak'
I'd probably copy this file someplace else and start over with daily and
transactional bakcups.

In fact I'd make a full backup right away.

It looks like you're only doing differential backups. Without a full to
base these off of, they're basically useless.

(and at this point that means you have 230 GB of useless backups.)

As for pruning, I recommend using a single backup file per file and simply
deleting the older ones.

EXECUTE master.dbo.xp_c reate_subdir
N'\\Server\Back ups\DBServerNam e\\DB_Name__MET ABASE'

GO

EXECUTE master.dbo.xp_c reate_subdir
N'\\server\Back ups\DBServerNam e\\DB_Name__MSC RM'

GO

BACKUP DATABASE [db_name__metaba se] TO disk = N'\\server\Back ups
\DBServerName\\ DB_Name__METABA SE
\DB_Name__METAB ASE_backup_2006 10261158.bak' WITH differential ,
noformat , noinit , name =
N'DB_Name__META BASE_backup_200 61026115839' , skip , rewind ,
nounload , stats = 10

GO

DECLARE @backupSetId AS INT

SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'DB_Name__META BASE'
AND backup_set_id = (SELECT MAX(backup_set_ id)
FROM msdb..backupset
WHERE database_name =
N'DB_Name__META BASE')

IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed. Backup information for database
''DB_Name__META BASE'' not found.',16,1)
END

RESTORE verifyonly FROM disk = N'\\server\Back ups\DBServerNam e\
\DB_Name__METAB ASE\DB_Name__ME TABASE_backup_2 00610261158.bak ' WITH
FILE = @backupSetId , nounload , norewind

GO

BACKUP DATABASE [db_name__mscrm] TO disk = N'\\server\Back ups
\DBServerName\\ DB_Name__MSCRM\ DB_Name__MSCRM_ backup_20061026 1158.bak'
WITH differential , noformat , noinit , name =
N'DB_Name__MSCR M_backup_200610 26115839' , skip , rewind , nounload ,
stats = 10

GO

DECLARE @backupSetId AS INT

SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'DB_Name__MSCR M'
AND backup_set_id = (SELECT MAX(backup_set_ id)
FROM msdb..backupset
WHERE database_name = N'DB_Name__MSCR M')

IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed. Backup information for database
''DB_Name__MSCR M'' not found.',16,1)
END

RESTORE verifyonly FROM disk = N'\\server\Back ups\DBServerNam e\
\DB_Name__MSCRM \DB_Name__MSCRM _backup_2006102 61158.bak' WITH FILE =
@backupSetId , nounload , norewind

This job was set up long before i started here and the problem is that
the backup file itself has grown to be over 230 GB. It does not
appear that the backup job is pruning the file. is there a way to
view the contents of this file and then prune it so we keep no more
then two weeks worth of data.

Thanks

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com