SQL – Backup Methods, Some Cool Scripts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prashant Pandey » SQL – Backup Methods, Some Cool Scripts

32 posts, Page 1 of 4

1 2 3 4 »»»

SQL – Backup Methods, Some Cool Scripts

Rate Topic

Display Mode

Topic Options

Author

Message

Prashant Pandey

Posted Thursday, November 06, 2008 10:32 PM

SSC 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

philcart

Posted Thursday, November 06, 2008 10:40 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:53 AM
Points: 2,692, Visits: 1,074

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

surreydude

Posted Friday, November 07, 2008 12:45 AM

Grasshopper

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

Lorenzo DBA

Posted Friday, November 07, 2008 4:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 10:16 AM
Points: 7, Visits: 187

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

philcart

Posted Friday, November 07, 2008 4:51 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:53 AM
Points: 2,692, Visits: 1,074

Lorenzo DBA (11/7/2008)

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

sobrien

Posted Friday, November 07, 2008 5:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2012 6:34 AM
Points: 2, Visits: 21

Very poor English....

Post #598819

Preet_S

Posted Friday, November 07, 2008 5:59 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:15 AM
Points: 236, Visits: 235

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

Preet_S

Posted Friday, November 07, 2008 6:03 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:15 AM
Points: 236, Visits: 235

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

sobrien

Posted Friday, November 07, 2008 6:22 AM

Forum 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

Heinrich Braun

Posted Friday, November 07, 2008 6:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 2:47 AM
Points: 6, Visits: 1,104

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 »

32 posts, Page 1 of 4

1 2 3 4 »»»

Permissions