Backup Software

Hi

We are getting ready to rollout a new system in the next few months and I have started to look at backup and recovery strategies for SQL Server 2005 on which this new system will be housed. The proposal is to by Veritas Backup Exec with the server this system will be housed on however my knowledge is "limited" to the backup and restore options available directly from SQL. I have been unable to find any opinion on using third party software to backup and restore SQL Server 2005 databases.

Can anyone help to give me an objective opinion on this subject. Should I simply use SQL Server and the tools it ships with or do these third-party applications make it easier?

Many thanks in advance of your help

Cheers

Danny

I find that using SQL's built in backup facility, backing up to a local drive, and then having a third party network backup product to move the backup to other locations/media is often the best choice.

|||I NEVER use backup agents to directly backup my SQL Server databases. The closest that I come is using SQL Litespeed at several customers so that we can take advantage of the compression and encryption features. Beyond that, I backup to disk and then use a utilty like Backup Exec to archive my backups to tape. I've simply had way too many very bad experiences in live disasters in the past to ever do backups any other way.|||

I have used redgate sql backup. this has more compresiion ratio , passowrd protected and backup will be encrypted.

However, SQL Server native backup also work for me lot of time. it will take more time than other tools but u can work with that

|||

I find SQL backup still the easiest (well, it could use lots of improvements) to use

We use Maintainence Plan to backup onto network file server on RAID5 (say we keep a weekly rotation)

and future plan is to use Veritas Backup Exec to backup those .BAK files (not sure how long the rotation will be, or if it's even necessary to backup SQL BAK files onto tape)

We did try Veritas SQL Agent as well, but due to network issues that's been halted for now

It wasn't too bad to use

|||

Backing-up to disk, using either SQL Server's native backups or a third-party backup tool, then backing-up from disk to tape is a great way to go if you have a sufficient time window. If you plan your backup regime properly you can keep several days' backups on disk and backup only the most recent backups to tape on a daily basis. This approach is beneficial when someone asks for a copy of a particular database from several days ago as you don't have to mess around recalling tapes etc...

Whether to use SQL Server's native backups or, instead, a third-party tool, such as Quest's SQL Litespeed or Red-Gate's SQL Backup, depends on whether you need to minimise the storage requirements of your backups and/or if you need to reduce the time taken to create the backup files. For instance, if you have only a couple of databases each of, say, 1GB in size then it probably isn't going to be worth investing in a third-party tool. If, on the otherhand, you have databases of, say, 200GB in size and you're backing-up to disk across a network then you'll probably see significant reductions in both backup file sizes and backup times if you use a third-party product that is capable of compression.

We use either SQL Server backups or SQL Litespeed backups, scheduled by SQL Agent, depending on the sizes of the databases on our servers - the decision is made on a server-by-server basis. We then use Backup Exec to backup the resultant backup files to tape. I must admit that I've never used Backup Exec to backup data straight from SQL Server, however to me it just doesn't 'feel' right doing it that way - anyway we're happy with the solution that we use so there's no need to change it. One other thing is that we've been using SQL Litespeed for around three years and performed literally hundreds of test restores and we have never had problems restoring databases from the compressed files (and, no, I don't work for Quest... ;) ).

Chris

|||Thank you all for your updates, most helpful!!!|||

The easiest way for me is to use a sql server backup program. The one I reccomend is www.4backuponline.com it will let you have a local copy and an online copy of you backup and it's really simple to use. You set it up once and choose when to run backups and it will run automatically and send daily emails with backup status.

I hope it helps,

|||I always do a quick backup of my project using Developer's Backup.|||

I do know that sqlbase a tape is not "allowed" to be ran over the database.

This is not the same situation for sql is it?

Backup Software

Hi

We are getting ready to rollout a new system in the next few months and I have started to look at backup and recovery strategies for SQL Server 2005 on which this new system will be housed. The proposal is to by Veritas Backup Exec with the server this system will be housed on however my knowledge is "limited" to the backup and restore options available directly from SQL. I have been unable to find any opinion on using third party software to backup and restore SQL Server 2005 databases.

Can anyone help to give me an objective opinion on this subject. Should I simply use SQL Server and the tools it ships with or do these third-party applications make it easier?

Many thanks in advance of your help

Cheers

Danny

I find that using SQL's built in backup facility, backing up to a local drive, and then having a third party network backup product to move the backup to other locations/media is often the best choice.

|||I NEVER use backup agents to directly backup my SQL Server databases. The closest that I come is using SQL Litespeed at several customers so that we can take advantage of the compression and encryption features. Beyond that, I backup to disk and then use a utilty like Backup Exec to archive my backups to tape. I've simply had way too many very bad experiences in live disasters in the past to ever do backups any other way.|||

I have used redgate sql backup. this has more compresiion ratio , passowrd protected and backup will be encrypted.

However, SQL Server native backup also work for me lot of time. it will take more time than other tools but u can work with that

|||

I find SQL backup still the easiest (well, it could use lots of improvements) to use

We use Maintainence Plan to backup onto network file server on RAID5 (say we keep a weekly rotation)

and future plan is to use Veritas Backup Exec to backup those .BAK files (not sure how long the rotation will be, or if it's even necessary to backup SQL BAK files onto tape)

We did try Veritas SQL Agent as well, but due to network issues that's been halted for now

It wasn't too bad to use

|||

Backing-up to disk, using either SQL Server's native backups or a third-party backup tool, then backing-up from disk to tape is a great way to go if you have a sufficient time window. If you plan your backup regime properly you can keep several days' backups on disk and backup only the most recent backups to tape on a daily basis. This approach is beneficial when someone asks for a copy of a particular database from several days ago as you don't have to mess around recalling tapes etc...

Whether to use SQL Server's native backups or, instead, a third-party tool, such as Quest's SQL Litespeed or Red-Gate's SQL Backup, depends on whether you need to minimise the storage requirements of your backups and/or if you need to reduce the time taken to create the backup files. For instance, if you have only a couple of databases each of, say, 1GB in size then it probably isn't going to be worth investing in a third-party tool. If, on the otherhand, you have databases of, say, 200GB in size and you're backing-up to disk across a network then you'll probably see significant reductions in both backup file sizes and backup times if you use a third-party product that is capable of compression.

We use either SQL Server backups or SQL Litespeed backups, scheduled by SQL Agent, depending on the sizes of the databases on our servers - the decision is made on a server-by-server basis. We then use Backup Exec to backup the resultant backup files to tape. I must admit that I've never used Backup Exec to backup data straight from SQL Server, however to me it just doesn't 'feel' right doing it that way - anyway we're happy with the solution that we use so there's no need to change it. One other thing is that we've been using SQL Litespeed for around three years and performed literally hundreds of test restores and we have never had problems restoring databases from the compressed files (and, no, I don't work for Quest... ;) ).

Chris

|||Thank you all for your updates, most helpful!!!|||

The easiest way for me is to use a sql server backup program. The one I reccomend is www.4backuponline.com it will let you have a local copy and an online copy of you backup and it's really simple to use. You set it up once and choose when to run backups and it will run automatically and send daily emails with backup status.

I hope it helps,

|||I always do a quick backup of my project using Developer's Backup.|||

I do know that sqlbase a tape is not "allowed" to be ran over the database.

This is not the same situation for sql is it?

sql

backup method question

hi
We are evaluating our backup plans. We currently do a live backup with a
backup exec SQL agent. Thinking about doing it to disk instead without
using veritas, just dumping the database to a file and then backing up
the file. My veritas rep is telling me it will be hard to do a restore
that way.
Is that true? Any other suggestions on backing up?
Thanks
Will
What your Veritas rep is saying is "If you backup to disk, then you won't
need to pay me for an overpriced and underfeatured SQL agent for each
server". Backup to disk and archive to tape is the preferred method for SQL
backups. Make sure you don't backup to the same disk as your data or log
files. I prefer to backup across a dedicated network link to a file server.
That file server is also my tape host so it can backup the files locally.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Will Kubly" <wkubly@.wi.rr.com> wrote in message
news:u0KoQkAIFHA.1392@.TK2MSFTNGP10.phx.gbl...
> hi
> We are evaluating our backup plans. We currently do a live backup with a
> backup exec SQL agent. Thinking about doing it to disk instead without
> using veritas, just dumping the database to a file and then backing up
> the file. My veritas rep is telling me it will be hard to do a restore
> that way.
> Is that true? Any other suggestions on backing up?
> Thanks
> Will
|||Geoff N. Hiten wrote:
> What your Veritas rep is saying is "If you backup to disk, then you won't
> need to pay me for an overpriced and underfeatured SQL agent for each
> server". Backup to disk and archive to tape is the preferred method for SQL
> backups. Make sure you don't backup to the same disk as your data or log
> files. I prefer to backup across a dedicated network link to a file server.
> That file server is also my tape host so it can backup the files locally.
>
That is kinda what I thought. I am not an SQL guru by any means. I do
more of the plumging. Are there any special considerations to restoring
that file into SQL?
Thanks
Will
|||Hi
I was using backup exec to do the backup of the sql server 2000 database.
It is easy and has good GUI to restore.
But apart from that I did not find any compelling reasons to use it.
I am doing a backup on the USB attached Network drive and it takes about the
same time as taking it on tape.
Mangesh
"Geoff N. Hiten" wrote:

> What your Veritas rep is saying is "If you backup to disk, then you won't
> need to pay me for an overpriced and underfeatured SQL agent for each
> server". Backup to disk and archive to tape is the preferred method for SQL
> backups. Make sure you don't backup to the same disk as your data or log
> files. I prefer to backup across a dedicated network link to a file server.
> That file server is also my tape host so it can backup the files locally.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Will Kubly" <wkubly@.wi.rr.com> wrote in message
> news:u0KoQkAIFHA.1392@.TK2MSFTNGP10.phx.gbl...
>
>
|||SQL backup files are self-contained and self-describing. That is, they
contain all the information required to recreate the database on the
original or any other SQL server. I strongly suggest you read the section
in BOL (Books On-Line) on Backing Up and Restoring Databases in the
Administering SQL Server section. There are some decisions you must make
when you set up a database or there will be painful consequences down the
road. Read and understand this section now, rather than when your system is
down.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Will Kubly" <wkubly@.wi.rr.com> wrote in message
news:ep68s0AIFHA.560@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Geoff N. Hiten wrote:
won't[vbcol=seagreen]
SQL[vbcol=seagreen]
log[vbcol=seagreen]
server.[vbcol=seagreen]
locally.
> That is kinda what I thought. I am not an SQL guru by any means. I do
> more of the plumging. Are there any special considerations to restoring
> that file into SQL?
> Thanks
> Will

backup method question

hi
We are evaluating our backup plans. We currently do a live backup with a
backup exec SQL agent. Thinking about doing it to disk instead without
using veritas, just dumping the database to a file and then backing up
the file. My veritas rep is telling me it will be hard to do a restore
that way.
Is that true? Any other suggestions on backing up?
Thanks
WillWhat your Veritas rep is saying is "If you backup to disk, then you won't
need to pay me for an overpriced and underfeatured SQL agent for each
server". Backup to disk and archive to tape is the preferred method for SQL
backups. Make sure you don't backup to the same disk as your data or log
files. I prefer to backup across a dedicated network link to a file server.
That file server is also my tape host so it can backup the files locally.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Will Kubly" <wkubly@.wi.rr.com> wrote in message
news:u0KoQkAIFHA.1392@.TK2MSFTNGP10.phx.gbl...
> hi
> We are evaluating our backup plans. We currently do a live backup with a
> backup exec SQL agent. Thinking about doing it to disk instead without
> using veritas, just dumping the database to a file and then backing up
> the file. My veritas rep is telling me it will be hard to do a restore
> that way.
> Is that true? Any other suggestions on backing up?
> Thanks
> Will|||Geoff N. Hiten wrote:
> What your Veritas rep is saying is "If you backup to disk, then you won't
> need to pay me for an overpriced and underfeatured SQL agent for each
> server". Backup to disk and archive to tape is the preferred method for S
QL
> backups. Make sure you don't backup to the same disk as your data or log
> files. I prefer to backup across a dedicated network link to a file serve
r.
> That file server is also my tape host so it can backup the files locally.
>
That is kinda what I thought. I am not an SQL guru by any means. I do
more of the plumging. Are there any special considerations to restoring
that file into SQL?
Thanks
Will|||Hi
I was using backup exec to do the backup of the sql server 2000 database.
It is easy and has good GUI to restore.
But apart from that I did not find any compelling reasons to use it.
I am doing a backup on the USB attached Network drive and it takes about the
same time as taking it on tape.
Mangesh
"Geoff N. Hiten" wrote:

> What your Veritas rep is saying is "If you backup to disk, then you won't
> need to pay me for an overpriced and underfeatured SQL agent for each
> server". Backup to disk and archive to tape is the preferred method for S
QL
> backups. Make sure you don't backup to the same disk as your data or log
> files. I prefer to backup across a dedicated network link to a file serve
r.
> That file server is also my tape host so it can backup the files locally.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Will Kubly" <wkubly@.wi.rr.com> wrote in message
> news:u0KoQkAIFHA.1392@.TK2MSFTNGP10.phx.gbl...
>
>|||SQL backup files are self-contained and self-describing. That is, they
contain all the information required to recreate the database on the
original or any other SQL server. I strongly suggest you read the section
in BOL (Books On-Line) on Backing Up and Restoring Databases in the
Administering SQL Server section. There are some decisions you must make
when you set up a database or there will be painful consequences down the
road. Read and understand this section now, rather than when your system is
down.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Will Kubly" <wkubly@.wi.rr.com> wrote in message
news:ep68s0AIFHA.560@.TK2MSFTNGP12.phx.gbl...
> Geoff N. Hiten wrote:
won't[vbcol=seagreen]
SQL[vbcol=seagreen]
log[vbcol=seagreen]
server.[vbcol=seagreen]
locally.[vbcol=seagreen]
> That is kinda what I thought. I am not an SQL guru by any means. I do
> more of the plumging. Are there any special considerations to restoring
> that file into SQL?
> Thanks
> Will

backup job sometimes fails

Hi

We have a nightly job on a production server which backs up all databases
(about 90 on the server)

Sometimes it'll only backup some of them, yet it reports success.

Our proc to manage the process is below:

Any ideas on what we need to do to make it reliable ?

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + '
>>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' +
'.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

Print "Finished backing up the databases"

CLOSE DB_cursor
DEALLOCATE DB_cursor

My suggestion is to look into a 3rd party package for reliable backups of business critical data....SQL 2005 just isn't there yet in this area.

Jeff

|||create a job with the above script and keep the script in step1 of the job and then in the advanced options set an output file so that the output will be written to a text file, which will be used for troubleshooting purposes..............in the step2 you can have the mail step......and see whats the output after the backup completes.......is this box with sql 2005 sp1? if yes better go to SP2 as there are few bugs which are fixed in sp2.........also try to go with maintenance plan and see whats the result if the above option fails....|||

Hi

Thanks for this

We already write the output to a file - it doesn't contain any errors or anything unusual - most strange...

The box is running SQL 2005 sp2.

I could go and buy something like Red-gate backup - but I don't see why I should have to ! Most frustrating..

thanks

Bruce

|||

You don't need a third party solution - SQL native backups were fine and have for years. Third party solutions are the ones that tend to be a bit more problematic. The only problem I can think of that Jeff would have been referring to is maintenance plans. The problem is with maintenance plans, not SQL Server backups.

I'm curious - you are using both single quotes as well as double quotes for print statements? Is that the actual script you are using? What is in the output file? Does it have the name of the database that are skipped or not? What is in the error log? Have you tried running a server side trace during the times you run the backups to see what is actually being executed?

-Sue

|||

Hi Sue

Thanks for replying..

The line with the double-quotes we ended up commenting out.

I also put the backup statement in a 'try catch' as follows below and there was no error logged

No - it doesn't have the name of the databases that are skipped - it just seems to exit without logging any errors - and it's not consistent - some nights it gets to the end.

There is nothing logged in the error logs.... No I haven't tried running a server side trace..

thanks, Bruce.

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases order by name

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BEGIN TRY
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT ;
END TRY
BEGIN CATCH
Print 'Error -- ' + ERROR_MESSAGE();
END CATCH;
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

|||

So you need to run a trace during the backups (and for a time prior to them starting) to see more of what is going on. You'll also want to look at the logs prior to and during the backups.

-Sue

|||

Bruce,

I too have something like this. I noticed that this happens when you have many databases needs to be backed up.

What we did here is to split this backup jobs into multiple steps. Each Job will backup few databases may be 50.

Try this and let me know.

Thanks

Shilpi

|||Can you check whether the existing backup file is in use? If possible alter the script to append date at the end of backup filename. Since we are backingup nearly 88 databases but didnt find any issues.

In which account is the job running and does the account has backup operator privilege to those failed db's?

backup job sometimes fails

Hi

We have a nightly job on a production server which backs up all databases
(about 90 on the server)

Sometimes it'll only backup some of them, yet it reports success.

Our proc to manage the process is below:

Any ideas on what we need to do to make it reliable ?

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + '
>>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' +
'.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

Print "Finished backing up the databases"

CLOSE DB_cursor
DEALLOCATE DB_cursor

My suggestion is to look into a 3rd party package for reliable backups of business critical data....SQL 2005 just isn't there yet in this area.

Jeff

|||create a job with the above script and keep the script in step1 of the job and then in the advanced options set an output file so that the output will be written to a text file, which will be used for troubleshooting purposes..............in the step2 you can have the mail step......and see whats the output after the backup completes.......is this box with sql 2005 sp1? if yes better go to SP2 as there are few bugs which are fixed in sp2.........also try to go with maintenance plan and see whats the result if the above option fails....|||

Hi

Thanks for this

We already write the output to a file - it doesn't contain any errors or anything unusual - most strange...

The box is running SQL 2005 sp2.

I could go and buy something like Red-gate backup - but I don't see why I should have to ! Most frustrating..

thanks

Bruce

|||

You don't need a third party solution - SQL native backups were fine and have for years. Third party solutions are the ones that tend to be a bit more problematic. The only problem I can think of that Jeff would have been referring to is maintenance plans. The problem is with maintenance plans, not SQL Server backups.

I'm curious - you are using both single quotes as well as double quotes for print statements? Is that the actual script you are using? What is in the output file? Does it have the name of the database that are skipped or not? What is in the error log? Have you tried running a server side trace during the times you run the backups to see what is actually being executed?

-Sue

|||

Hi Sue

Thanks for replying..

The line with the double-quotes we ended up commenting out.

I also put the backup statement in a 'try catch' as follows below and there was no error logged

No - it doesn't have the name of the databases that are skipped - it just seems to exit without logging any errors - and it's not consistent - some nights it gets to the end.

There is nothing logged in the error logs.... No I haven't tried running a server side trace..

thanks, Bruce.

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases order by name

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'd:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BEGIN TRY
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT ;
END TRY
BEGIN CATCH
Print 'Error -- ' + ERROR_MESSAGE();
END CATCH;
print ''
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

|||

So you need to run a trace during the backups (and for a time prior to them starting) to see more of what is going on. You'll also want to look at the logs prior to and during the backups.

-Sue

|||

Bruce,

I too have something like this. I noticed that this happens when you have many databases needs to be backed up.

What we did here is to split this backup jobs into multiple steps. Each Job will backup few databases may be 50.

Try this and let me know.

Thanks

Shilpi

|||Can you check whether the existing backup file is in use? If possible alter the script to append date at the end of backup filename. Since we are backingup nearly 88 databases but didnt find any issues.

In which account is the job running and does the account has backup operator privilege to those failed db's?