Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Backup & Restore startergies


SQL Server Backup & Restore startergies

Author
Message
sabarish
sabarish
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1864 Visits: 2592
sabarish (5/3/2010)
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


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

The Redneck DBA
sabarish
sabarish
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35979 Visits: 18727
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
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47147 Visits: 44346
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, MVP, M.Sc (Comp Sci)
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


TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1864 Visits: 2592
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.

The Redneck DBA
sabarish
sabarish
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
Marcos Leandro Rosa
Marcos Leandro Rosa
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 71
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

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47147 Visits: 44346
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, MVP, M.Sc (Comp Sci)
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


Marcos Leandro Rosa
Marcos Leandro Rosa
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 71
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search