SQL Server Backup & Restore startergies

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » SQL Server Backup & Restore startergies

11 posts, Page 1 of 2

1 2 »»

SQL Server Backup & Restore startergies

Rate Topic

Display Mode

Topic Options

Author

Message

sabarish

Posted Monday, May 03, 2010 6:40 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 29, 2010 3:04 AM
Points: 12, Visits: 22

Hi Everyone ,

We need to implement a datbase back up statergy for one of clients ... Some of the requirements mentioned by the client are as given below

1) Ability to back up database - Scheduled back up @ regular intervals as well as manual back up
2) Ability to archive a database.
3) Abililty to search from a archive / back up

I am a new bie to this ...

Can any one recommend any effective startergies for this

Regards
Sabarish

wwewew

Post #914620

Jason Shadonix

Posted Monday, May 03, 2010 6:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:06 PM
Points: 1,409, Visits: 2,027

sabarish (5/3/2010)

Just out of curiosity, why are you taking on this job for your client if you don't have backup experience/knowledge?

Jason Shadonix
MCTS, SQL 2005

Post #914625

sabarish

Posted Monday, May 03, 2010 6:58 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 29, 2010 3:04 AM
Points: 12, Visits: 22

My experience is mainly on the C++ , C# ... Have not used much of databases , just a few queries here and there , so i am not aware of best practices and approaches ...

wwewew

Post #914632

Steve Jones - SSC Editor

Posted Monday, May 03, 2010 7:08 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433, Visits: 13,745

For backups, typically you would write a script or use a maintenance plan for the backup and schedule that as a job. Here are a few resources for that:

http://www.sqlservercentral.com/articles/1535/
http://www.sqlservercentral.com/articles/1353/

Note that depending on what your clients need for recovery, you might need log backups in addition to full backups. I would suggest you hire someone if you don't know how this works. Get them to teach you and you will then have good knowledge and your client will be protected.

In terms of archiving, it depends on what you mean. You can use a backup as an archive, just copy it elsewhere and then restore it and search if if needed. Because of the structure of the database, there isn't an easy way to archive off into something like a text file.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #914644

GilaMonster

Posted Monday, May 03, 2010 7:11 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

So why does the client want you to do it if they know that you know almost nothing about databases? Better to get someone in who does.

From your requirements, there's little I can suggest.
The ability to take a backup - SQL has that built in. Native backups, maintenance plans. What kind of backups and how often depends on what the allowable data loss is, what the maintenance windows are, what's the max allowable time to restore and other factors.
Archive a database - can be as simple as back it up and restore under another name, or can be a complex custom archiving process.
Search a backup - not with the native backup tools. I don't offhand know of any 3rd party backup tools that allow full-blown searches.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #914649

Jason Shadonix

Posted Monday, May 03, 2010 7:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:06 PM
Points: 1,409, Visits: 2,027

GilaMonster (5/3/2010)

So why does the client want you to do it if they know that you know almost nothing about databases? Better to get someone in who does.

Yes, That's what I was trying to get at...not that I want to discourage you from learning backups or anything, just that I would suggest not offering that service to a client until you have a handle on it.

Jason Shadonix
MCTS, SQL 2005

Post #914653

sabarish

Posted Monday, May 03, 2010 8:16 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 29, 2010 3:04 AM
Points: 12, Visits: 22

Thanks for yor comments .... I fully agree with what you are suggesting ... I know that i might not the right person to implement this , but just wanted some information on various SQL Server Backup & Restore stratergies so that, even if a hire a new guy, we can ensure he is giving us the right solutions and we are proceeding in the right direction

wwewew

Post #914706

Marcos Leandro Rosa

Posted Thursday, July 01, 2010 1:56 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 12, 2012 12:03 PM
Points: 17, Visits: 70

I know it's been a long time to answer this question, but for those who may need:

For regular backups you can use a maintenance plan with the following schedule:
Take Full Backup for all User database at 5pm and another at 11pm
Between 11pm and 5pm you can create another maintenance (or add subplans) to take Log Backups.

It is not required to build a process to save information about what backups are being generated as SQL already saves
this info also you can see by checking the Job logs.

If they do want it, you can create a simple code within each job to add rows in a table you also create
to watch this.

If you still have any problem you can reach me at marcosfac@gmail.com

Best Regards,
Marcos Rosa / marcosfac@gmail.com

Post #945983

GilaMonster

Posted Thursday, July 01, 2010 2:16 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996

marcosfac (7/1/2010)

IFor regular backups you can use a maintenance plan with the following schedule:
Take Full Backup for all User database at 5pm and another at 11pm
Between 11pm and 5pm you can create another maintenance (or add subplans) to take Log Backups.

It may work fine for you, but it cannot work for every single situation out there.
Consider 24x7 operations. Not running log backups for 6 hours will have unacceptable impact on database recovery (not to mention log growth).
What about huge databases that take 6+ hours to do a full backup?
If that's for a 9-5 operation, why two full backups each night? If it's for a 24x7 operation what about the impact of the backups on performance?

Backup and restore strategies tend to be very specific to the environment, the SLAs for downtime and data loss, the available maintenance windows, etc. There's no one-size-fits-all solution to backup/restore strategy.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #945990

Marcos Leandro Rosa

Posted Thursday, July 01, 2010 5:03 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 12, 2012 12:03 PM
Points: 17, Visits: 70

You are right Gilamonster. It depends on environment. But as you can see, if it was really important to their corporation they should hire a skilled DBA and not a bie. As it was a basic question, I got to the basics;

You are totally right, thanks for your post!

Best Regards,
Marcos Rosa / marcosfac@gmail.com

Post #946068

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions