database maintenance - best practice

Question

database maintenance - best practice

eseeweb

SSC-Addicted

Points: 437
More actions
November 24, 2011 at 6:25 am

#263390

Hi
We have sql 2008 server with 2 instances. One for production and one for testing. Both instances has multiple databases. We have a maintenance job running everyday to do a full backup of database. We don't have a true db admin here. Part of my job is to deal with these databases.
Lately, I am trying to find if there is any best practice articles to handle the server and databases. Please let me know if you come across or have any suggestions...
My goal is not to surprise ourselves in case of any crashes... 🙁
thx, Ted.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Ted

In the event of a crash, what is the largest amount of data (in days, hours and minutes) that you can afford to lose? Do you need to be able to do a point-in-time recovery, or will going back to the last full backup be sufficient? Your answers to those questions will dictate your backup strategy. You may also wish to read about DBCC CHECKDB, and you'll want to consider testing that you can actually restore a sample of your databases.

John

viiki.seth Ten Centuries Points: 1127 More actions · Answer 2

article that is what you need to know...about SQL Server Maintenance

http://sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx

eseeweb SSC-Addicted Points: 437 More actions · Answer 3

hi,

Thank you for your reply. It is not a must to be 24/7 type of operation here. We prefer to be able to retrieve data up to the minutes but it is not a must. Is there a step to step guide for this. Our sql server is setup since last summer, we are able to restore full backup.

Also, thank you for the link, i will go through it.

thx, Ted.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 4

If you're new to SQL Server then use SQL Server Management Studio (SSMS) GUI to point-and-click your way through the configuration. Open SSMS, connect to the database instance (by default same as server name). In Object Explorer, expand Management and then Maintenance Plans, right-click and New Maintenance Plan. Use the Toolbox window on the left and drag/drop your desired backup jobs into the subplan - create new subplans if necessary. Double click on the object to edit the settings. Once done, and scheduled, find the Job in the SQL Server Agent tree on the left and Start Job At Step... to test. It sounds complex but easy when you see it.

The previous poster was right in saying it depends on the application needs when considering your backup strategy. The London Stock Exchange's backup strategy is going to be far more demanding than the database back-end for Norma's Cake Shop. Just consider that if you have to restore the database, having a full set of backups & transaction logs is going to give you much more choice than if you didn't.

Graze TechNet / MSDN and the articles/stairways on this site for more help.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 5

Make sure DB Backups are in place...Which might help in case of crash.

If Possible use some high avilability approach like clustering or mirroring.

- Pradyothana http://www.msqlserver.com