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


SQL – Backup Methods, Some Cool Scripts


SQL – Backup Methods, Some Cool Scripts

Author
Message
Prashant Pandey
Prashant Pandey
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 128
Comments posted to this topic are about the item SQL – Backup Methods, Some Cool Scripts

Thanks,
Prashant
philcart
philcart
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 1434
With your exclusion/inclusion of system databases, what about the distribution, ReportServer and ReportServerTempDB databases?

Backup of system databases not required normally

After making the point about having backups to avoid the risk of loss, now you say a backup isn't required. Well if you want some hope of recovering in the event of a disaster, you might want to backup your system databases. Full maintenance and backup of system databases only takes seconds, so why take the risk.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
surreydude
surreydude
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: 38
Hmm, a bit of a "Backups 101" - It's interesting you don't mention anything about checkpoints, transaction logs, recovery modes, tail backups. These are all important considerations when providing a resilient and reliable environment.

Do you take backups manually?

Nope! I am not, taking backup manually on daily basis not an easy task. I make use of SQL SERVER JOBS to take backup automatically.


I work in DB support - ad-hoc/daily backups should not be viewed as any less differently from scheduled; if you have a robust process, it should not be any less difficult except for the risk, impact and logistics.



Lorenzo DBA
Lorenzo DBA
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 217
When backing up all the databases via (1) process, I have found that if in any event that a backup of (1) database out of many fails for some reason the whole process will stop and will not backup up the databases after the point of failure.

This routine, I feel would not be recommended for high availability, high usage, large databases. This I feel will be good for small db's 5GB or less and for db's that are set to simple recovery mode only.
philcart
philcart
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 1434
Lorenzo DBA (11/7/2008)
When backing up all the databases via (1) process, I have found that if in any event that a backup of (1) database out of many fails for some reason the whole process will stop and will not backup up the databases after the point of failure.

This routine, I feel would not be recommended for high availability, high usage, large databases. This I feel will be good for small db's 5GB or less and for db's that are set to simple recovery mode only.


Using T-SQL to perform the backups with any sort of looping mechanism, only a batch terminating error will prevent subsequent backups from being taken.

I have a stored procedure that I use to perform backups and its worked its way through many a failure. Database sizes range from 5GB to >200GB with some transaction log backups being taken at 5 minutes intervals. One of these days I might get motivated and post the script on my much unloved blog Tongue

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
sobrien
sobrien
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 21
Very poor English....
Preet_S
Preet_S
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 240
Basic intro for newbies.
Isn't SP_MSforeachdb undocumented, therefore unsupported ? I'm always a bit wary of using undocumented sprocs as they may be withdrawn in later releases thus requiring you reauthor your procedures.
Preet_S
Preet_S
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 240
sobrien (11/7/2008)
Very poor English....


We shouldn't be overly critical of somebodys work unless it is seriously wrong. I am guessing English is this guy's second language. At least he has taken the time to try to provide an article that may be of use to somebody. i.e. SQL newbies could definitely benefit.
sobrien
sobrien
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 21
I wasn't being overly critical. I was stating my opinion. I found the article very hard to get through because of the way it was written; therefore, I got nothing out of it. (If you have to read a sentence more than once to figure out what the person is trying to say, then it isn't written properly.) Obviously English was not the author's first language, but if it is going to be posted on a site written in English, then it should be proofed by the editor of the site beforehand to make sure there are no typos and that it makes sense because it is a direct reflection on the professionalism of the site itself. (Magazine editors do that.) So, it is no reflection on the author, but rather the site's editor.

I am sure someone got something out of it. I am a newbie and didn't because of the way it was written. My opinion, to which I am entitled.
Heinrich Braun
Heinrich Braun
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1111
Nice script. Just enjoyed it. If @dbname AS VARCHAR(20) is increased a bit in size, it starts workingWink

Heinrich
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