471,082 Members | 765 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,082 software developers and data experts.

SQL 2005 Backup Problems

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

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_200610 261158.bak' WITH
FILE = @backupSetId , nounload , norewind

GO

BACKUP DATABASE [db_name__mscrm] TO disk = N'\\server\Backups
\DBServerName\\DB_Name__MSCRM\DB_Name__MSCRM_backu p_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

Mar 13 '07 #1
1 3948
"Bryan" <bs*****@gmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.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_200610 261158.bak' WITH
FILE = @backupSetId , nounload , norewind

GO

BACKUP DATABASE [db_name__mscrm] TO disk = N'\\server\Backups
\DBServerName\\DB_Name__MSCRM\DB_Name__MSCRM_backu p_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
Mar 14 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by R.A.M. | last post: by
8 posts views Thread by RAM | last post: by
1 post views Thread by Leszekwl | last post: by
2 posts views Thread by m19peters | last post: by
10 posts views Thread by =?Utf-8?B?UHVuaXQgS2F1cg==?= | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.