SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL – Backup Methods, Some Cool Scripts


SQL – Backup Methods, Some Cool Scripts

Author
Message
Manie Verster
Manie Verster
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2325 Visits: 1030
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!

:-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)
sharadov
sharadov
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 776
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98596 Visits: 38996
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.

Cool
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)
Prashant Pandey
Prashant Pandey
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 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
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2460 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/
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9919 Visits: 1407
Bravo Prashant, well written article. Criticism will make you stronger. Hope your next article will come soon and it will be excellent...Smile



Etienne_Lemire
Etienne_Lemire
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 150
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
Adam Gojdas
Adam Gojdas
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 1451
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;


Etienne_Lemire
Etienne_Lemire
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 150
Ok, Thanks... I will try this and I'll let you know if it works well. Smile
philcart
philcart
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10043 Visits: 1441
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search