473,836 Members | 1,509 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL 2005 Backup Problems

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

Mar 13 '07 #1
1 4060
"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
Mar 14 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4349
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();
8
4291
by: RAM | last post by:
Hello, I am learning SQL Server 2005. I need to know how to make a backup of a database. I tried (according to my book): BACKUP DATABASE DemoSQLServer TO DISK = "G:\DemoSQLServer.bak" But I got error: Incorrect syntax near 'G:\DemoSQLServer.bak'. Please help. Thank you very much. /RAM/
1
3337
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 Device (this option is unavaible). Also sys.backup_devices and sys.dm_io_backup_tapes show nothing....
2
21141
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 (Message 0) Status is ONLINE dbname / dbdevice = master / SQLBUmaster (Message 0) Processed...
5
34055
by: Steve | last post by:
Hi; I thought I would rephrase a question I asked in another post to more quickly get to the heart of the matter. My apologies for anyone who is offended by what appears to be a repetition. My company has two identical web sites. One copy is for our customer, and one copy is for us to test our code changes on. We developed a hard to isolate bug in the copy of the web stie for our
2
6316
by: Oonz | last post by:
Hi Friends, Is there any tool or assistant to upgrade SQL 2000 to SQL 2005. Thanks, Arunkumar
10
6112
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 deployment tool ... to make sure that the database and tables were created when the client installed my...
0
1800
by: Jens | last post by:
Hi! I'm trying to do a log backup of a SQL Server 2005 database with Backup Exec v11d (incl. SQL agent). But I get the following error: The item server.domain.local\DATABASE in use - skipped. Backup- server.domain.local V-79-57344-33938 - An error occurred on a query to database DATABASE.
0
9825
marktang
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9388
agi2029
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6981
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4460
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4023
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.