SQL – Backup Methods, Some Cool Scripts

  • Prashant Pandey

    SSChasing Mays

    Points: 657

    Comments posted to this topic are about the item SQL – Backup Methods, Some Cool Scripts

    Thanks,
    Prashant

  • philcart

    SSC-Forever

    Points: 47792

    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.

    --------------------
    Colt 45 - the original point and click interface

  • surreydude

    Valued Member

    Points: 66

    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

    SSC Veteran

    Points: 273

    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

    SSC-Forever

    Points: 47792

    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 😛

    --------------------
    Colt 45 - the original point and click interface

  • sobrien

    SSC Journeyman

    Points: 78

    Very poor English....

  • Preet_S

    SSCrazy

    Points: 2074

    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

    SSCrazy

    Points: 2074

    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

    SSC Journeyman

    Points: 78

    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

    SSC Rookie

    Points: 36

    Nice script. Just enjoyed it. If @dbname AS VARCHAR(20) is increased a bit in size, it starts working;)

    Heinrich

  • Manie Verster

    SSCertifiable

    Points: 7020

    sobrien (11/7/2008)


    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.

    Hear, hear!!! I agree. Unfortunately all the people writing for this site is not good at english and they just do their best to add their value. I think the editor of this site should edit the articles written and then only place them.

    Prashant, another thing that I noticed is that you only take weekly backups. I do mine (including system databases) daily and my transaction logs hourly.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Ted Krueger

    Ten Centuries

    Points: 1171

    I think the title was misleading, "Some Cool Scripts". This is backup 101 as stated. The article wasn't bad and good for a newb but definitely draws attention where not warranted. The script shown on the backup all database is very common. It would be nice if you went further on the script and alterations that can be made to it using the BACKUP options and things like performance, restore considerations, retain days etc...

  • dominiquesb

    Newbie

    Points: 7

    I added some interisting trick to the script. Like creating a sub directory for each database (required in my environment) and detecting offline database (it`s impossible to backup).

    Have fun!

    DECLARE @cursor AS CURSOR

    DECLARE @dbname AS VARCHAR(20),

    @query AS VARCHAR(100)

    SET @cursor = CURSOR SCROLL FOR

    SELECT NAME FROM MASTER..Sysdatabases

    WHERE NAME NOT IN ('tempdb', 'ReportServerTempDB') AND cast(status as integer) & 512 <> 512

    OPEN @cursor

    FETCH NEXT FROM @cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @query = 'master.dbo.xp_create_subdir N''D:\Backup\'+ @dbname+ ''''

    EXEC(@query)

    SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''D:\backup\'+ @dbname+'\'+ @dbname+'.bak '' WITH INIT'

    EXEC(@query) FETCH

    NEXT FROM @cursor INTO @dbname

    END

    CLOSE @cursor

    DEALLOCATE @cursor

    Dominique St-Pierre Boucher

    MCDBA, MCT

  • Preet_S

    SSCrazy

    Points: 2074

    sobrien (11/7/2008)


    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.)

    Sorry to upset you so much. What I meant is that we should try to look beyond the shortcomings in his language skills and try to gain what we can from the article, if anything. We don't want to discourage people investing their own time writing possibly useful articles. Personally, I think you can certainly understand what he is communicating but that is only my opinion...

  • Jerry Hung

    SSChampion

    Points: 12908

    Wow, so many negative opinions, I read it and just take away whatever I can, doesn't hurt me anyway

    By the way, to continue on SP_MSforeachdb.... I know it's undocumented (maybe unsupported too)

    but it's so cool to use it, and short neat code

    User DB backup

    SP_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') BACKUP DATABASE ? TO DISK = ''C:\backup\?.bak'' WITH INIT'

    System DB backup

    SP_MSforeachdb 'if ''?'' IN (''tempdb'',''master'',''model'',''msdb'') BACKUP DATABASE ? TO DISK = ''C:\backup\?.bak'' WITH INIT'

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply