SQL 2005 Backup File - Microsoft SQL Server

Bryan

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

Mar 13 '07 #1

Subscribe Reply

1505

Greg D. Moore \(Strider\)

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" <bs*****@gmail. comwrote in message
news:11******** **************@ 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

Mar 13 '07 #2

Similar topics

4341

2005: using SMO

by: R.A.M. | last post by:

Hello, I am learning SQL Server 2005. I would like to write a procedure making database backup using SMO. According to my book I should write such code: using Microsoft.SqlServer.Management.SMO; public static void MakeBackup() { Server server = new Server("localhost"); Backup backup = new Backup();

Microsoft SQL Server

2145

Migrating data from SQL Server 6.5 to SQL Server 2005

by: info.nrieger | last post by:

Hello, I'm not very familiar to SQL Server, but I've to upgrade a DBMS from V6.5 to V2005. the same, at least it has the same size). After installing V2005 - at another system - I could not find any option to import a database from an old system, e.g. importing/reading a dump file, backup file.

Microsoft SQL Server

2748

Migrating to VS 2005

by: wandii | last post by:

Hi, I have written couple of projects which include several crystal reports in VS.net 2003 (VB.Net) and company would like to move to VS 2005. What does it take to convert to Visual Studio.Net 2005? Can the 2003 project just be opened up in 2005? Will that convert it. The interface for 2005 very different from 2003. I got the VS 2005...

Visual Basic .NET

3331

SQL Serwer 2005 + HP LTO Ultrium

by: Leszekwl | last post by:

Hi My company buys new server( HP Proliant ML 570 with HP LTO ULTRIUM 232 Tape Drive. OS (Windows 2003 Enterprise - SP1) see it correctly (nt backup works, diagnostic tools don't report problems) But SQL Server 2005 - SP1 don't see this tape drive ( MS SQL Server Management Studio Backup Devices is empty and I can't add new Tape Backup...

Microsoft SQL Server

4050

SQL 2005 Backup Problems

by: Bryan | last post by:

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'

Microsoft SQL Server

21121

SQL Server 2005 full backup script

by: m19peters | last post by:

We have a script that I had to rework a little bit for 2005 that does a full backup for every database on the server... For some reason on some nights the script does not backup all databases... Its like it skips over it for some reason... Output of the script below on the night in question was: Executed as user: NT AUTHORITY\SYSTEM. master...

Microsoft SQL Server

6074

Sql Server express 2005 deployment

by: =?Utf-8?B?UHVuaXQgS2F1cg==?= | last post by:

Hi, I am developing a windows application in VS.NET 2005. The application is database driven and I need to deploy it on a client's pc. How should I go about developing such an application. I remember I did something once long back .. like including .mdf files into my project in the solution explorer of VS 2005 and then used Click Once...

C# / C Sharp

8266

Restore LDF file after restoring backups in SQL Server 2005?

by: l0b0 | last post by:

I'm working on a restore procedure for the case where all MDF files are missing, but the LDF files are all intact. A full backup is done every 24 hours, and a log backup is done every 3 hours. After restoring the last full + log backups, is it at all possible to use the LDF files to recover data from that point up to a newer point in time? ...

Microsoft SQL Server

7795

Copy 2000 database to 2005 - cannot detach/attach

by: dpatel75 | last post by:

I am trying to copy a database from a SQL 2000 SP3 Windows 2000 server to a 2005 SP2 Windows 2003 server. I am trying to use detach and attach method (have tried both within Management Studio and T-SQL) and experience an error when attaching to the 2005 server: "CREATE FILE encountered operating system error 5 (error not found) while...

Microsoft SQL Server

7701

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...

General

7615

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7924

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

7677

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...

Windows Server

6284

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...

Career Advice

5219

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3653

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...

Networking - Hardware / Configuration

1223

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

940

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

General