March 30, 2012 at 7:52 am
Really, this article could be more of a general primer on how to call a stored procedure from a windows form application. If it included a best practive example of error handling and input / output parameters, then that would add more value.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 30, 2012 at 7:55 am
There are two more reasons for not using SMO for this type of project:
1) Microsoft tends to change them with every next version of SQL Server, thus the project needs to be updated with each server upgrade;
2) If those libraries are not already installed on the target computer, they should be included into Setup package and this may cause a licensing issue.
Because of that use of T-SQL is a preferable way.
There is no real need to create a stored procedure for performing backup - raw SQL statement will work perfectly in this case, and if you need to backup more than one database at a time it is easy to do it that way (example - Great Plains package, where you need to make backups of GP system database and Company(s) database(s) at once in a single button click.)
March 30, 2012 at 8:35 am
I've been working on something similar to this for a while. We wanted our database update utility to do an automatic backup before it started changing tables and stored procedures in the customer database.
My problem with using T-SQL to back up the database is it worked fine for the tiny databases, then we hit the 120 gigabyte database that wouldn't backup before the command timeout.
We switched to using SMO, then we ran into version compatibility problems between 2005 / 2008. So now we test for the version, call an appropriate DLL, and show a nice little progress bar.
I had a calculus professor who said "Sometimes you want the elegant solution, sometimes you want to use brute force and ignorance." I have to take a backup, and when all else fails, our version of brute force and ignorance is:
- Warn the user the database is going down, and everyone needs to log off
- get the physical file names and locations
- put the database in single user mode
- detach the database
- physically copy the files
- hope it reattaches
- put in an error message that says "Call me for help"
It's not pretty, but when all else fails, it works....most of the time. If not, I get a phone call and walk them through re-attaching the database.
March 30, 2012 at 8:41 am
hahahahaha, instead of brute force you should plan differential backups, file and filegroup backups and transaction log backups.
March 30, 2012 at 9:28 am
wayne.mcdaniel (3/30/2012)
then we hit the 120 gigabyte database that wouldn't backup before the command timeout
You can set CommandTimeout property to 0 and then SQLCommand will wait for statement's completion indefinitely.
March 30, 2012 at 9:32 am
wayne.mcdaniel (3/30/2012)
I've been working on something similar to this for a while. We wanted our database update utility to do an automatic backup before it started changing tables and stored procedures in the customer database.My problem with using T-SQL to back up the database is it worked fine for the tiny databases, then we hit the 120 gigabyte database that wouldn't backup before the command timeout.
We switched to using SMO, then we ran into version compatibility problems between 2005 / 2008. So now we test for the version, call an appropriate DLL, and show a nice little progress bar.
I had a calculus professor who said "Sometimes you want the elegant solution, sometimes you want to use brute force and ignorance." I have to take a backup, and when all else fails, our version of brute force and ignorance is:
- Warn the user the database is going down, and everyone needs to log off
- get the physical file names and locations
- put the database in single user mode
- detach the database
- physically copy the files
- hope it reattaches
- put in an error message that says "Call me for help"
It's not pretty, but when all else fails, it works....most of the time. If not, I get a phone call and walk them through re-attaching the database.
When attempting to execute long running T-SQL commands from an application connection, you can encounter things like timeouts for various reasons. It may work better to create a SQL Agent job for your backup, and then kick it off asynchonously via sp_start_job.
http://msdn.microsoft.com/en-us/library/ms186757.aspx
The job can keep everyone posted on completion status via email notifications using Database Mail.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 30, 2012 at 9:36 am
Great article. The best ones tend to fire off the most discussion
You can avod the stored procedures. They add complexity without a positive.
I took a more brute force approach. I ask the server for a list of databases. I build a SQL statement to back up that database. I write those to a text file (extension .SQL) I wrote a windows command file that executes my program then executes SQLCMD to run my SQL staments. This we have implemented on multiple servers. it backs up my databases and thrid party databases.
By the way, for those folks who saw "Visual Studio" and cringed at the cost of getting that, look at SharpDevelop.
Still this is part of the answer. What do you do with the backup files once you have them? Take them. home Upload to cloud storeage.
Then have you practiced doing the restores?
ATBCharles Kincaid
March 30, 2012 at 9:43 am
wayne.mcdaniel (3/30/2012)
- Warn the user the database is going down, and everyone needs to log off- get the physical file names and locations
- put the database in single user mode
- detach the database
- physically copy the files
- hope it reattaches
- put in an error message that says "Call me for help"
I'd have to agree with the original Author's comments on this methodology, that you really need a sounder backup plan than this, especially for a production system. There are many good resources for learning more about the overall strategy such as:
http://www.sqlmag.com/article/backup-recovery/sql-server-backup-best-practices
March 30, 2012 at 2:47 pm
I am always open to new ways of doing things, but I am also of the old school of thought. "Don't spend time trying to re-invent the wheel." There are plenty of great backup tools in and out of SQL Server that can already do this just fine as is. Don't go around the barn when you can easily go straight through it. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
March 30, 2012 at 3:36 pm
I agree that there are lots of good tools on the market. What makes me so tired, and sad, is hearing from my clents that they have yet another thing to buy. I also hear from people with small databases or not I.T. staff that the backup tools are too expensive, complicated, time consuming, fill in the blank, etc. Therefore they are not backing up at all. :w00t:
ATBCharles Kincaid
March 30, 2012 at 3:51 pm
Charles Kincaid (3/30/2012)
I agree that there are lots of good tools on the market. What makes me so tired, and sad, is hearing from my clents that they have yet another thing to buy. I also hear from people with small databases or not I.T. staff that the backup tools are too expensive, complicated, time consuming, fill in the blank, etc. Therefore they are not backing up at all. :w00t:
This is completely correct. In addition, most of the posters in this discussion consider things from the DBA point of view (and the corresponding permissions to the databases). But there is a demand from a regular business users (not an IT staff) to be able to do database backups for some special occasions (for example before doing some massive processing in the database application which can go wrong - in such case this special backup will serve as a restore point). For being able to do that they need to have a very simple tool not requiring any special technical knowledge, and the project described by the OP fits nicely for this purpose.
March 30, 2012 at 4:02 pm
wayne.mcdaniel (3/30/2012)
- Warn the user the database is going down, and everyone needs to log off- get the physical file names and locations
- put the database in single user mode
- detach the database
- physically copy the files
- hope it reattaches
- put in an error message that says "Call me for help"
Sounds like an Oracle offline backup 🙂
Totally unnecessary under SQL Server 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 30, 2012 at 4:13 pm
Say, Grasshopper, that remids me. COPY ONLY! Don't let a rogue backup mess up your chains.
Somebody else mentions log backups and differnetials. This thread is like a recipe for "white sauce" or "corn bread". Ther are countless variations.
ATBCharles Kincaid
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply