Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Backup & Restore startergies Expand / Collapse
Author
Message
Posted Monday, May 03, 2010 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, May 03, 2010 6:45 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:43 AM
Points: 1,477, Visits: 2,100
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?


Jason Shadonix
MCTS, SQL 2005
Post #914625
Posted Monday, May 03, 2010 6:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, May 03, 2010 7:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
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
Posted Monday, May 03, 2010 7:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
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
Posted Monday, May 03, 2010 7:18 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:43 AM
Points: 1,477, Visits: 2,100
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
Posted Monday, May 03, 2010 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, July 01, 2010 1:56 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 1:43 PM
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
Post #945983
Posted Thursday, July 01, 2010 2:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
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
Posted Thursday, July 01, 2010 5:03 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 1:43 PM
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
Post #946068
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse