March 29, 2012 at 9:57 pm
Comments posted to this topic are about the item How to create a database backup using visual studio
March 29, 2012 at 11:18 pm
Hi, you can even go for using SMO to get your task done in a more easy way. Please refer this msdn link.
-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post.
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia
March 30, 2012 at 12:57 am
Agreed, use the backup.sqlbackup method via SMO. You'll also be able to display a nice little progress bar too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 30, 2012 at 1:51 am
I am in a situation of being an accidental DBA to databases that are controlled by 3rd party. We are allowed to perform tasks on the engine but not allowed to add stored procedures. Backups is a role we need to perform and I am also currenlty looking at maintenance plans.
I like the idea of this C# backup application but unsure what route I should take:
Put the stored procedure in the master database
or
Try and work out how to do the same but without the stored procedure
What are the thoughts of the community?
March 30, 2012 at 1:58 am
Adding any User objects to the Master DB is a very BAD idea. There are many ways you could achieve the backups -
1. Schedule the Backups using SQL Jobs.
2. If your version does not support it, you can create a .sql script file for backup and execute it using sqlcmd from the command prompt.
3. You can even schedule a batch file in your OS which intern would be calling the SQLCMD command.
4. Use SMO as I have already suggested.
Hope, this helps!
-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post.
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia
March 30, 2012 at 2:51 am
While the topic is interesting why would you need to have this.
MS SQL server already has plenty of backup tools, it would be handy for those rdbms that rely on command line rather then GUI interface.
March 30, 2012 at 3:05 am
@vineypugalia - Thank you for the comments. I shall leave the Master database alone then.
@Resender - There will be cases when something like this will be useful. With the software I am supporting, some of the customers have the database on their site and we have to provide the tools to perform the backups and support things remotely. Currently the backup is a batch file that runs the commandline version of a 3rd party application that deals with backups, attach and detachment of databases. I would rather provide some dedicated backup application that can check dates of the backup file created. DOS can't do this and I have more knowledge on C# tahn powershell. I guess I shall have to dive into Powershell.
March 30, 2012 at 4:18 am
I think there is a bad example of how to make a tool to backup a database.
In my opinion you have to use SMO (a good example is this http://www.sqlserverclub.com/articles/introduction-to-sql-server-shared-management-objects-smo.aspx). If you don't know C# maybe you couldn't write a technical article or maybe you could write in other medias like a forum or in your blog.
I have the email subscription to this website since 2 years and I never have seen an article like this. I read every article with lot of patience, there are lot of very complete articles and there are another non-complete articles, but in general the technical skills of the articles are good.
I'm Sorry to be picky about the technical article, I encourage you to keep writing and you post something with more consistency on SMO.
A lot of spirit.
PS: I'm Sorry for my english, I am learning still.
March 30, 2012 at 6:39 am
vinaypugalia (3/30/2012)
Adding any User objects to the Master DB is a very BAD idea.
Heh... At the risk of starting World War III, you are denying yourself one amazing tool for DBA's. Yeah, yeah... I know all the warnings everyone gives about MS maybe some day picking the same name as one of your procs (easily prevented by thoughtful naming) and MS maybe overwriting all of the master database and destroying your procs (easily overcome with a distribution sproc properly maintained in source control) but, as parenthetically indicated, those are minor almost trivial obstacles.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 6:51 am
This is what I like about forums. Different viewpoints so I can then look at what would suit our situation best. As with everything, there is no single solution.
Perhaps I was too hasty in accepting the quickest reply but all the comments will be looked at openly. I will admit I am scared to insert something into the Master database but unsure if this was just me being so fresh to DBA tasks.
March 30, 2012 at 6:59 am
I agree with all of you guys. Usually the SMO is the best option in C#, but sometimes, we already have the stored procedures done. If we already have T-SQL code done, there are alternatives different than the SMO to execute and call SQL Server using C#.
In my case I had a customers with 300 stored procedures used and already created for administrative purpouses. He wanted a custom C# application with nice buttons. If he already had stored procedures, it was not smart to create SMO code.
We have to learn different alternatives and we do not have to close our eyes because one problem can have multiple solutions.
I guess my solution will help to all the guys who do not like SMO and they have T-SQL experience and code already done and they just need to create an application quickly in C#.
There are many topics about SMO, so I did not find any reason to write about it again.
March 30, 2012 at 7:38 am
Peace of cake????!!!
Sorry, I stopped right there.
March 30, 2012 at 7:41 am
I think the best way to kick off backups would be to schedule them via SQL Server Agent job. If you're going to kick off your backups from something like a custum built application or service, then you'll probably want to maintain your schedule and run history in a set of tables somewhere. If not in a database you create for the purpose, then the best candidate system database for containing your custum procedure and run tables would be MSDB. I'd suggest MSDB over MASTER, because MSDB already contains run tables for backup, SSIS, etc. The MASTER database contains critical system tables, and it may be best not to burden it with too much other stuff.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 30, 2012 at 7:44 am
Another option (for adding a sproc to the master database) would be to add a separate schema of your own to the master database and place the stored procedure there.
This approach makes it doubly unlikely that Microsoft will overwrite your stored procedure because it would have to have the same schema name and stored procedure name to be overwritten.
March 30, 2012 at 7:51 am
Yeah, usually a nice sql job would be the best option. But, if a customers has already a C# tool centalized to monitor many things like IIS, Windows and SQL server. Also, he already has stored procedures and the C# tool. I found that calling the stored procedure was the best option. I just used the backup example because it was easier to understand, but the code itselft is used to run any DDL inside a stored procedure.
I like this discussion, because I think the people is learning more from the discussion than the article itself 😀
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply