SQL – Backup Methods, Some Cool Scripts

Question

SQL – Backup Methods, Some Cool Scripts

Prashant Pandey

SSChasing Mays

Points: 657
More actions
November 6, 2008 at 10:32 pm

#200953

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

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

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 47794 More actions · Answer 4

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 More actions · Answer 5

sobrien

SSC Journeyman

Points: 78

November 7, 2008 at 5:35 am

#895437

Very poor English....

Preet_S SSCrazy Points: 2184 More actions · Answer 6

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: 2184 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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

Heinrich

Manie Verster SSCertifiable Points: 7152 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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: 2184 More actions · Answer 13

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: 12968 More actions · Answer 14

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'

SQLServerNewbieMCITP: Database Administrator SQL Server 2005