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 1234»»»

SQL – Backup Methods, Some Cool Scripts Expand / Collapse
Author
Message
Posted Thursday, November 6, 2008 10:32 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2008 9:27 PM
Points: 81, Visits: 128
Comments posted to this topic are about the item SQL – Backup Methods, Some Cool Scripts

Thanks,
Prashant
Post #598672
Posted Thursday, November 6, 2008 10:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204
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
Post #598674
Posted Friday, November 7, 2008 12:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 29, 2009 4:59 AM
Points: 16, Visits: 37
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.



Post #598704
Posted Friday, November 7, 2008 4:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 5:45 PM
Points: 7, 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.
Post #598786
Posted Friday, November 7, 2008 4:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204
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 :P




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
Post #598794
Posted Friday, November 7, 2008 5:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2012 6:34 AM
Points: 2, Visits: 21
Very poor English....
Post #598819
Posted Friday, November 7, 2008 5:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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.


Post #598842
Posted Friday, November 7, 2008 6:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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.
Post #598845
Posted Friday, November 7, 2008 6:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2012 6:34 AM
Points: 2, 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.
Post #598865
Posted Friday, November 7, 2008 6:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 8:06 AM
Points: 7, Visits: 1,108
Nice script. Just enjoyed it. If @dbname AS VARCHAR(20) is increased a bit in size, it starts working;)

Heinrich
Post #598893
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse