April 26, 2012 at 11:22 pm
Comments posted to this topic are about the item Run SQL Server Scripts using C#
April 27, 2012 at 12:18 am
Nice code but how can I check if everything goes correcty? Is there any error code returned?
April 27, 2012 at 12:29 am
My preference would be to invoke the SMO objects directly within C# to backup the database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 27, 2012 at 12:42 am
I use it too but I think that it is more easy to get errors without notification during the operation with SMO.
April 27, 2012 at 2:00 am
Should really be using SMO for this sort of thing.
IMHO
April 27, 2012 at 2:07 am
mark.harby (4/27/2012)
Should really be using SMO for this sort of thing.IMHO
+1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 27, 2012 at 2:22 am
Other than capturing STDOUT and STDERR, and doing potentially interesting things with them (when the dos PIPE commands work just fine too), I can see no reason to go through the trouble of writing a C# app that is simply shelling out to cmd.exe anyway! About 20 seconds (on a slow day) at the command line, to type:
COPY CON backup.bat
@echo off
sqlcmd", @" -S .\sqlexpress -i C:\backup.sql
[Ctrl-Z][Enter] to save the file, voila. You can launch the shortcut on the desktop manually, put it somewhere 'safe' and create an "at" command to run it at intervals, etc.
Compare that to launching Visual Studio, creating a console app, adding the needed using statement creating an Object, setting many properties, only to finally call a method that then calls "cmd.exe [whatever you passed in as application path and parameters].
Perhaps I am being naive and missing something here? This is is an excellent example of calling any "command line" from within a C# program, but I fail to see what advantages it gives to a DBA, unless they are hand-writing their own DB maintenance application logic, which is not something I would attempt unless I had a compelling business case not covered by existing functionality.
Why would someone do this rather than using SQL Server maintenance plans, which are very robust and can perform all the needed steps, and email and/or page operators and/or perform other complex chains of actions in the event of success or failure of a job or step of a job?
No offense intended to the OP, I do not mean to come off as sarcastic. This is just my humble opinion. I truly don't understand how this might be of use in a real-world scenario? (I think all blog posts should include this, unless they are intended to be scientific/theory in the first place.)
I understand by using SMO you can pull off some cool stuff; I just haven't come across a scenario that warrants that level of development... Perhaps the projects I work on are all too small? (In the DBs I work with, the biggest tables have a few million rows (that have 1-to-many FK relationships)).
Please excuse my ignorance, and educate a poor fellow!!
April 27, 2012 at 2:52 am
One scenario that I can realize is that you are using an SQL Express that doesn't provide you Maintanance Plans and other.
However if you need to give a backup restore capability to user you can't use at, etc..
The user feel more secure if he can make a backup o restore provided by the program itself. A shortcut can alway be deleted etc..
This is my opinion.
April 27, 2012 at 3:41 am
Ah yes, you make a good point about SQL Express; I rarely use that edition, so I forget about the limitations of it. That would be a case where it might be cheaper to write 'good enough' maintenance tasks to avoid buying Standard Edition.
At the risk of sounding snarky, in reference to "the shortcut could be deleted"; well, so could the C# program, right?? Perhaps I missed the intent of your comment there; easy enough to do with only text. 🙂
Thanks for the reply!
April 27, 2012 at 4:46 am
Nothing very important but I often see that for customers everything you can put inside a program, maybe in some menu -> backup / restore, is more "secure" than a link placed on desktop. The relevance of this, from 1 to 10, is near 1.1 🙂
April 27, 2012 at 4:53 am
Ah, I see your point, and my misunderstanding. I was thinking from an Admin's point of view (who would possess slightly more clue), and not something under control by / mercy of the end user. 🙂
April 27, 2012 at 4:59 am
I think many people are missing the point, or let's say use case, of this article. Users are not always rationale, nor do they necessarily have SSMS installed. So if you have someone that says "I MUST be able to generate a full backup at any time during the day and I need 1 minute turnaround time (can you say developers?)" then you might put together something like this app as an .exe and say "Just click the button and you are all set."
April 27, 2012 at 5:15 am
I for one am an Analyst Programmer with some SQL responsibilities on top, not a full on DBA.
This kind of thing is useful to me if I'm writing a application with SQL Express in the background - To be able to give certain users of my application access to administrative functions like backup/restore WITHOUT them installing management studio is quite useful. For me, if the users have no idea what SQL is, but my application works and they can create backup files to burn to a CD then my application is good.
So yea, useful for Visual Studio developers, not necesarily the most useful thing for a dba, unless you're giving a junior tech services guy an application he can use to manage backups/maintenance jobs without having to give him management studio if you dont want to.
That being said I'd probably use SMO. Calling command line programs is certainly a useful feature of Visual Studio but I probably wouldn't implement that here.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
April 27, 2012 at 6:22 am
I created a few vb.Net apps to backup and restore databases, but I used SMO as well. This way it's also easy to add a progress bar and thread it so your user doesn't think your program stopped responding. I also like the additional security of not relying on opening sql scripts that could be easily modified. Nice demonstration though for executing processes programmatically. 🙂
April 27, 2012 at 7:28 am
I wouldn't use this for a backup specifically however it will be very useful in updating the hundreds to thousands of databases in our multitenant environment with the latest dacpac file from the new VS Data Tier Application project by iterating through a list of databases and generating the sqlcmd needed to run the dacpac dynamically for each one.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply