SQL – Backup Methods, Some Cool Scripts
-
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.
-
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 -
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
-
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.
-
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 AfricaI 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) -
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.
-
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.
-
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 -
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/ -
Bravo Prashant, well written article. Criticism will make you stronger. Hope your next article will come soon and it will be excellent...:)
-
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
-
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;
-
Ok, Thanks... I will try this and I'll let you know if it works well. 🙂
-
You really only need to check @@FETCH_STATUS = 0, that indicates that a row was successfully fetched.
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply