SQL 2005 Backup File

**Greg D. Moore \(Strider\)** · Mar 13 '07, 11:05 PM

Re: SQL 2005 Backup File

Can you please repost WITHOUT the HTML

Tht's basically impossible to read.

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

"Bryan" <bsockel@gmail. comwrote in message
news:1173818991 .982147.112650@ y66g2000hsf.goo glegroups.com.. .

We have a SQL 2005 server running the following backup job:
>
 <f ont color="blue">/* Created by
<a href="http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm"> free
online sql formatter</a*/ 
EXEC UTE <fon t color =
"maroon">master .db o.xp_cr eate_subdir
  &nbsp ; N'\\Serve r\Backups
\DBServerName\\ DB_Name__METABA SE'
>
 
GO
>
 
EXEC UTE <fon t color =
"maroon">master .db o.xp_cr eate_subdir
  &nbsp ; N'\\serve r\Backups
\DBServerName\\ DB_Name__MSCRM' 
>
 
GO
>
 
BACK UP <fon t color = "blue">DATABASE  <fon t color = "maroon">[db_name__metaba se] <fon t color = "blue">TO <fon t color =
"maroon">di sk <fon t color = "silver">= <fon t
color = "red">N'\\serve r\Backups\DBSer verName\\DB_Nam e__METABASE
\DB_Name__METAB ASE_backup_2006 10261158.bak' <fon t color =
"blue">WITH  <fon t color = "maroon">differ ential <fon t color = "silver">, <fon t color =
"maroon">noform at <fon t color = "silver">, <fon t color = "maroon">noinit  <fon t color =
"silver">, <fon t color = "maroon">na me <fon t
color = "silver">= <fon t color =
"red">N'DB_Name __METABASE_back up_200610261158 39' <fon t
color = "silver">, <fon t color = "maroon">sk ip <fon t color = "silver">, <fon t color =
"maroon">rewind  <fon t color = "silver">, <fon t
color = "maroon">nounlo ad <fon t color = "silver">, <fon t color = "maroon">st ats <fon t color =
"silver">= <fon t color = "black">10
>
 
GO
>
 
DECL ARE &nbs p;@back upSetId &nbs p;AS <fon t color = "black">INT 
>
 
SELE CT <fon t color =
"#8000FF">@back upSetId <fon t color = "silver">= <fon t color = "maroon">positi on
 FROM  &nbs p; ms db..backup set
 WHER E &nbs p;databa se_name <fon t color = "silver">= <fon t color = "red">N'DB_Name __METABASE'
  &nbsp ;  &n bsp; &nbsp ;AND <fon t color = "maroon">backup _set_id <fon t color = "silver">= <fon t color =
"silver">(SELE CT <fon t color =
"fuchsia">MA X(backup _set_id)
  &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ; FROM  &nbs p; ms db..backup set
  &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ; WHER E &nbs p;databa se_name <fon t color = "silver">= <fon t color = "red">N'DB_Name __METABASE')
>
 
IF <fon t color =
"#8000FF">@back upSetId <fon t color = "blue">IS <fon t color = "blue">NULL 
  &nbsp ;BEGI N
  &nbsp ;  <f ont color = "blue">RAISERRO R <fon t color = "silver">(N'Verify& nbsp;failed.&nb sp;Backup  information&nbs p;for data base ''DB_ Name__METABASE' ' not&nbsp ;found.',16,1)
  &nbsp ;END
>
 
REST ORE <fon t color =
"maroon">verify only <fon t color = "blue">FROM  <fon t color = "maroon">di sk <fon t color =
"silver">= <fon t color = "red">N'\\serve r\Backups
\DBServerName\\ DB_Name__METABA SE
\DB_Name__METAB ASE_backup_2006 10261158.bak' <fon t color =
"blue">WITH  <fon t color = "blue">FILE  <fon t
color = "silver">= <fon t color = "#8000FF">@back upSetId <fon t color = "silver">, <fon t color =
"maroon">nounlo ad <fon t color = "silver">, <fon t color = "maroon">norewi nd
>
 
GO
>
 
BACK UP <fon t color = "blue">DATABASE  <fon t color = "maroon">[db_name__mscrm] <fon t
color = "blue">TO <fon t color = "maroon">di sk <fon t color = "silver">= <fon t color = "red">N'\
\server\Backups \DBServerName\\ DB_Name__MSCRM
\DB_Name__MSCRM _backup_2006102 61158.bak' <fon t color =
"blue">WITH  <fon t color = "maroon">differ ential <fon t color = "silver">, <fon t color =
"maroon">noform at <fon t color = "silver">, <fon t color = "maroon">noinit  <fon t color =
"silver">, <fon t color = "maroon">na me <fon t
color = "silver">= <fon t color =
"red">N'DB_Name __MSCRM_backup_ 20061026115839'  <fon t color
= "silver">, <fon t color = "maroon">sk ip <fon t
color = "silver">, <fon t color = "maroon">rewind  <fon t color = "silver">, <fon t color =
"maroon">nounlo ad <fon t color = "silver">, <fon t color = "maroon">st ats <fon t color =
"silver">= <fon t color = "black">10
>
 
GO
>
 
DECL ARE &nbs p;@back upSetId &nbs p;AS <fon t color = "black">INT 
>
 
SELE CT <fon t color =
"#8000FF">@back upSetId <fon t color = "silver">= <fon t color = "maroon">positi on
 FROM  &nbs p; ms db..backup set
 WHER E &nbs p;databa se_name <fon t color = "silver">= <fon t color = "red">N'DB_Name __MSCRM'
  &nbsp ;  &n bsp; &nbsp ;AND <fon t color = "maroon">backup _set_id <fon t color = "silver">= <fon t color =
"silver">(SELE CT <fon t color =
"fuchsia">MA X(backup _set_id)
  &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ; FROM  &nbs p; ms db..backup set
  &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ;  &n bsp; &nbsp ; WHER E &nbs p;databa se_name <fon t color = "silver">= <fon t color = "red">N'DB_Name __MSCRM')
>
 
IF <fon t color =
"#8000FF">@back upSetId <fon t color = "blue">IS <fon t color = "blue">NULL 
  &nbsp ;BEGI N
  &nbsp ;  <f ont color = "blue">RAISERRO R <fon t color = "silver">(N'Verify& nbsp;failed.&nb sp;Backup  information&nbs p;for data base ''DB_ Name__MSCRM''&n bsp;not fo und.',16,1)
  &nbsp ;END
>
 
REST ORE <fon t color =
"maroon">verify only <fon t color = "blue">FROM  <fon t color = "maroon">di sk <fon t color =
"silver">= <fon t color = "red">N'\\serve r\Backups
\DBServerName\\ DB_Name__MSCRM\ DB_Name__MSCRM_ backup_20061026 1158.bak' <fon t color = "blue">WITH  <fon t color =
"blue">FILE  <fon t color = "silver">= <fon t
color = "#8000FF">@back upSetId <fon t color = "silver">, <fon t color = "maroon">nounlo ad <fon t color =
"silver">, <fon t color = "maroon">norewi nd

>
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
>

SQL 2005 Backup File

SQL 2005 Backup File

Comment