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 1:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:58 PM
Points: 1,207, Visits: 928
Prashant Pandey (11/7/2008)
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).


That's the spirit! I like a positive attitude like that!


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 #599274
Posted Friday, November 7, 2008 1:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:07 PM
Points: 47, Visits: 710
Why does SQLServerCentral.com publish the same articles just written by different authors over and over again.
Just a mindless rehash of kiddie concepts, copied from elsewhere and changed here and there.
Post #599280
Posted Friday, November 7, 2008 2:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 20,857, Visits: 32,871
As long as what is written is the authors own work, why not? Each author has a different perspective on a topic and presents the information from that perspective. Looking at things from a different view point is one way of learning something that you may not have known prior to reading an article.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #599294
Posted Friday, November 7, 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
sarath (11/7/2008)
Why does SQLServerCentral.com publish the same articles just written by different authors over and over again.
Just a mindless rehash of kiddie concepts, copied from elsewhere and changed here and there.


Hi Sarath,

Is the same article written by anyone else? I haven't seen it yet, please show me. As far as i know this whole article is written by me. I think whatever articles I have written for sqlservercentral are not copied from here and there. Those all are my original works. OK

Thanks!


Thanks,
Prashant
Post #599387
Posted Sunday, November 9, 2008 12:45 PM


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: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
The article was well written, but probably extremely basic for people who read this site.

One thing I will point out is that you may want to make backups of the system databases in most real world situations. If you have no user defined information in your system databases, then backing them up may be worthless, but that most people do.

Remember that information such as defined operators, sql server agent schedules, and certain types of replication information are stored in msdb. Also, (whether this is smart or not is a completely separate topic) some people put utility procedures and functions which are used for server maintenance or used for multiple other databases inside of the master database. Your model database may be highly customized for your situation if you are doing certain types of analytics that regularly create new databases. So, for most real world situations it makes sense to backup at least some of the system databases.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #599588
Posted Monday, November 10, 2008 8:54 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
Bravo Prashant, well written article. Criticism will make you stronger. Hope your next article will come soon and it will be excellent...:)


Post #599939
Posted Friday, November 14, 2008 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:27 AM
Points: 7, Visits: 133
Hi,

I'm managing 10 SQL Servers here with around 10 to 30 DB each. I'm having a hard time to setup a script to automatically backup all database (user and system DB) at once.

I tried both scripts: SP_MSforeachdb and the script with a cursor. I faced the same problem with both script, sometimes it works great but some other times, the job completes after backing-up 2 or 3 DB leaving all other DB not backed-up and I really don't understand why...

I did some debug of the script with the cursor, sometime it ends before the end when the "fetch next" returns some blanks instead of the DB name, with the fetch_status = -2

I really appreciate you help!


Etienne
Post #602850
Posted Friday, November 14, 2008 9:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
It appears that a row trying to be fetched is no longer "valid". Sounds like the logic needs to be something more along these lines for the cursor. This example takes that scenario into account. It will only try to do something for a succesful fetch and just moves on to the next fetch if it is a -2. (The code is not a real working version however but the logic is there.):

   
DECLARE objectsCur CURSOR FAST_FORWARD LOCAL
FOR
SELECT Column1, Column2 FROM SomeTable

OPEN objectsCur;
FETCH NEXT FROM objectsCur INTO @command, @name;
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS = 0) BEGIN
BEGIN TRY
EXEC (@command);
END TRY
BEGIN CATCH
SET @error = 1;
--note the error and move on to next ones
PRINT N'Error: ' + @command
PRINT N' ' + CAST(ERROR_MESSAGE() AS nvarchar(max));
PRINT N''
END CATCH;
END;
FETCH NEXT FROM objectsCur INTO @command, @name;
END;

SET @curStatus = Cursor_Status('local', 'objectsCur');
IF (@curStatus >= 0) BEGIN
CLOSE objectsCur;
DEALLOCATE objectsCur;
END;

Post #602881
Posted Friday, November 14, 2008 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 8:27 AM
Points: 7, Visits: 133
Ok, Thanks... I will try this and I'll let you know if it works well. :)
Post #602885
Posted Sunday, November 16, 2008 2:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:14 PM
Points: 2,693, Visits: 1,223
You really only need to check @@FETCH_STATUS = 0, that indicates that a row was successfully fetched.



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 #603369
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse