Posted by Greg D. Moore \(Strider\) on 03/14/07 00:48

"Bryan" <bsockel@gmail.com> wrote in message
news:1173819995.556639.316820@y80g2000hsf.googlegroups.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\Backups\DBServerName\\DB_Name__METABASE\DB_Name__METABASE_backup_200610261158.bak'


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_create_subdir
> N'\\Server\Backups\DBServerName\\DB_Name__METABASE'
>
> GO
>
> EXECUTE master.dbo.xp_create_subdir
> N'\\server\Backups\DBServerName\\DB_Name__MSCRM'
>
> GO
>
> BACKUP DATABASE [db_name__metabase] TO disk = N'\\server\Backups
> \DBServerName\\DB_Name__METABASE
> \DB_Name__METABASE_backup_200610261158.bak' WITH differential ,
> noformat , noinit , name =
> N'DB_Name__METABASE_backup_20061026115839' , skip , rewind ,
> nounload , stats = 10
>
> GO
>
> DECLARE @backupSetId AS INT
>
> SELECT @backupSetId = position
> FROM msdb..backupset
> WHERE database_name = N'DB_Name__METABASE'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name =
> N'DB_Name__METABASE')
>
> IF @backupSetId IS NULL
> BEGIN
> RAISERROR (N'Verify failed. Backup information for database
> ''DB_Name__METABASE'' not found.',16,1)
> END
>
> RESTORE verifyonly FROM disk = N'\\server\Backups\DBServerName\
> \DB_Name__METABASE\DB_Name__METABASE_backup_200610261158.bak' WITH
> FILE = @backupSetId , nounload , norewind
>
> GO
>
> BACKUP DATABASE [db_name__mscrm] TO disk = N'\\server\Backups
> \DBServerName\\DB_Name__MSCRM\DB_Name__MSCRM_backup_200610261158.bak'
> WITH differential , noformat , noinit , name =
> N'DB_Name__MSCRM_backup_20061026115839' , skip , rewind , nounload ,
> stats = 10
>
> GO
>
> DECLARE @backupSetId AS INT
>
> SELECT @backupSetId = position
> FROM msdb..backupset
> WHERE database_name = N'DB_Name__MSCRM'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name = N'DB_Name__MSCRM')
>
> IF @backupSetId IS NULL
> BEGIN
> RAISERROR (N'Verify failed. Backup information for database
> ''DB_Name__MSCRM'' not found.',16,1)
> END
>
> RESTORE verifyonly FROM disk = N'\\server\Backups\DBServerName\
> \DB_Name__MSCRM\DB_Name__MSCRM_backup_200610261158.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

[Back to original message]