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 Friday, November 7, 2008 6:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
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.


Manie 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)
Post #598897
Posted Friday, November 7, 2008 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 47, Visits: 784
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...
Post #598913
Posted Friday, November 7, 2008 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 12, 2009 10:40 AM
Points: 1, Visits: 4
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
Post #598942
Posted Friday, November 7, 2008 8:34 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)
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...
Post #599006
Posted Friday, November 7, 2008 9:20 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:36 AM
Points: 772, Visits: 1,183
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
Post #599060
Posted Friday, November 7, 2008 9:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 47, Visits: 784
I don't think it should be called negative at all. Other than a poor reply on the English which should be ignored as we must understand we are not all using English as our primary language and only respect the attempt to post and use it to better reach the audience. The replies have been nothing more than help to build on the posters knowledge and who reads the article. Posts like that can be handled by the administration of this site and not by endless discussions again far out of scope.

A few things I noticed. sysname should be the data type when going after things like instance, database, tables etc..

DECLARE @dbname AS SYSNAME

Another is cursors should be avoided. Reasoning is out there and way out of scope of this article but just something to make others look into.

Lastly I would recommend against using SP_MSforeachdb. undocumented or not it is on the deprecation list and if it is being used these types of things are commonly overlooked by DBA's and developers when upgrades are done. Thus causing loss of production and sometimes lengthy troubleshooting tasks that could have been prevented.
Post #599089
Posted Friday, November 7, 2008 10:22 AM


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
Hi,

Thanks to all for giving your suggestions and thoughts, "Good comments always boost your confidence" but "Bad comments always boost your confidence to do the best as well as tell your weak points on which you can work", I have to work more on my language skills, which i am doing. Will definitely try to give a more better article next time.
Actually, I have explained the problems what I was facing while maintaining my websites database backup's, surely these scripts will not be beneficial in a big DBA kind of environment (where we need a properly planned maintenance plans). But yes I found these scripts useful, so I thought to share them on a widely known platform (SQLServerCentral).


Hey "@Jerry Hung", these are useful and short, thanks!

Jerry Hung (11/7/2008)


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'



Thanks,
Prashant
Post #599125
Posted Friday, November 7, 2008 10:52 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, Visits: 222
Hmmm... this sure looks familliar...
Post #599148
Posted Friday, November 7, 2008 1:33 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 9:54 AM
Points: 15, Visits: 34
I think the article was a good overview for beginners who do not have access to an automated backup system like Backup Exec w/the SQL Server backup client or others that do the same. This is more for smaller businesses/clients where the cost of such a system would be prohibitive.

At the place I currently work for I would only do this if I was going to apply a change to the database and I would back it up before I start. If the restore is something from last week or within the last year then it could take an hour to get the tapes from the off site storage. Otherwise we would be backup and running with 15 min.

Technically the subject is a good one and there is insight in the article. It is just not what I would do.
Regards,
MIT560





Post #599265
Posted Friday, November 7, 2008 1:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:43 AM
Points: 79, Visits: 1,329
I would be careful about using the stored proc - sp_MSforeachdb.

1. it uses global cursors to do its work in sp_MSforeach_worker.
2. it really has no error handling

So it may be a nice quick and dirty tool to use when manually doing stuff. But when set into an automated process you may be asking for trouble.
Post #599272
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse