SQL Server Backup & Restore startergies
-
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 -
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
-
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 -
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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
-
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 -
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
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 -
For newbies like yourself you can start off by using a Maintenance Plan!:w00t:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply