How important are log and database backups in a production environment?

Question

How important are log and database backups in a production environment?

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Question #1:
What are the reasons, if any, for this company to seriously consider a SQL Server upgrade from SQL Server 2000?

2000 is no longer under MS support.

Security vulnerabilities

Performance

Far better reporting

The list can go on for days...

Question #2:
What are the ramifications from not performing periodic daily log backups and a nightly complete database backup?

You can't do any better recovery than yesterday's data. You might loose an entire day of data.

Question #3:
Is it sufficient based on the scenario described above to have your only production database backup be a backup once a night via Backup Exec to tape?

Certainly not. It sounds like the 2 hour window of lost data is the outside of what is acceptable.

Question #4:
Is it possible that the issue with the SQL 2000 Server Agent not running the scheduled maintenance jobs is a precursor to other problems that have not yet been discovered?

Sure it is possible but without more information it is impossible to surmise what happened.

Questions #5:
Can anyone suggest what steps should be taken to get the scheduled jobs to run again?

Start by looking at the job history and see what errors if any there are. Again, without some details it is not really very feasible to offer much advice.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 2

I agree with Sean.

I'll also add that, if the company continues to resist the upgrade of a 13 year old product, it's time to get 3 envelopes... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 3

hmbtx (12/13/2013)
Question #1:
What are the reasons, if any, for this company to seriously consider a SQL Server upgrade from SQL Server 2000?

Media. Make darn sure you have several copies of the SQL Server 2000 media because you will have a hard time getting it from Microsoft going forward. Already mentioned, security patches, performance, OS patches, OS support (make darn sure you have media for your OS as well because the SQL Server 2000 support window on operating systems is also closing rapidly). Technical support (I'm not sure I could effectively troubleshoot 2000 any more. I haven't touched it for years now). Third party support. The list goes on and on and on and on.

Question #2:
What are the ramifications from not performing periodic daily log backups and a nightly complete database backup?

If the databases are in full recovery and there are no log backups, your system will be offline soon. Have fun. As to the general backup maintenance. Nothing to worry about.[/url] Nothing at all.

Question #3:
Is it sufficient based on the scenario described above to have your only production database backup be a backup once a night via Backup Exec to tape?

No. That makes me very nervous. Backup Exec is notorious for taking backups that it can't restore. Test the restore operation of a couple of the backups to ensure that the version of Backup Exec being used is transaction aware. If not, you could be "backing up" databases that can't be restored. I wrote an article[/url] about this too.

Question #4:
Is it possible that the issue with the SQL 2000 Server Agent not running the scheduled maintenance jobs is a precursor to other problems that have not yet been discovered?

I'd suspect so, yes. But, hard to say.

Questions #5:
Can anyone suggest what steps should be taken to get the scheduled jobs to run again?

Not without more understanding of errors from the Agent log, SQL Server log or the Windows log. We need to know why it's not starting.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 4

And I agree with Sean and Jeff too. I didn't look at their answers first. I was just excited by the questions. These are pretty classic types of issues that can lead to data loss.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

free_mascot One Orange Chip Points: 27164 More actions · Answer 5

In addtion to the above posts...

Here is the link to start SQL Agent automatically.

http://technet.microsoft.com/en-us/library/aa213810(v=sql.80).aspx

You can also configure mail alert to notify when the SQL Server Agent starts than send e-mail so that you can take care of execution of required jobs.

There is a huge risk running the database on obsolet version which is not supported by Microsoft.

Even though company is open 7 days a week. It is quite possible to migrate the database to newer version smoothly during off peak hours. Considering business do not work 24x7 from your reply as off business hrs you copy all the backup to remote loaction.

I am not sure what is the DR solution in place? If data is important it is must. For remote location you can think of Logshipping or replication so that data loss can be minimal. And for same site you can have database cluster.

Also check and verify the recovery plan agreed by the customer.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."