Why we require to create stored procedure for backup?

Is it possible to take backup of database with stored procedure using Sql Server 2005/2008 ?开发者_运维技巧 What is the stored procedure for it...and what would be the scenarios where my project required to create such procedure in my project? front end language will be C#.Net and backend is sql server. Can anybody guide me in this... Thanks in advance..

You can create your own stored procedure:

CREATE PROCEDURE sp_MyBackup
AS BEGIN
  BACKUP DATABASE myDatabase to disk='c:\BACKUP\myDatabase.bak'
END

and then you can execute this procedure from your T-SQL code:

exec sp_MyBackup

EDIT: to execute your stored proceure in c#, you need do something like that:

SqlConnection c= new SqlConection ( connectionstring );
c.Open();
SqlCommand cmd = new SqlCommand ("sp_MyBackup", c);
cmd.CommadType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
c.Close();

EDIT AGAIN: I think, front-end Application can use SP to create backups to realize next scenario: Before mass-update operation (import of data, mass-cleaning etc) user can start backup from application. In this case user does not need the appropriate sql-server and database rights, he must not have any databse tools and sql knowledge, but backup can be done.

You can take back of database by using "MDF and LDF" files or taking ".bak" file of particular database, This will cover all tables, stored procedures and functions.

Front End : C# Back End: Sql Server

Ado.Net act as bridge between Front End and Back End.

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).

This will be called by C# using SqlCommand object

http://www.exforsys.com/tutorials/vb.net-2005/access-and-manipulate-sql-server-data-using-stored-procedures.html

The scenario would be if you wanted to kick off a hourly/nightly/weekly/someothertime-ly backup on your database server.

You could then write a script (e.g powershell) to execute the stored procedure which kicks off the backup.

This has nothing to do with your actual application. Your application shouldn't care or be concerned with backups. This is a maintenance/administrative task, and should be handled on the database server.

Have a google to find out how to do it.

I can think of few reasons to do this unless you are creating a utility for DBAs and support staff to use. Allowing non-expert users to initiate backups is almost certainly not a good idea in a multi-user environment.

If you just want to allow users to create a recovery point or save a version of the database then I would suggest you create a transaction log mark. Assuming you are running in Full Recovery mode, log marks will be much faster and easier to support than allowing users to do their own backups.