database backup programmatically

Question

 

Hello all !

I am Delveloping Visual C# 2005 windows application which is connected to SQL server 2000 database at the back end.and my task is to create a button and when i press that button it should create the database backup automatically.

Can anybody Help me

thanks

 

Answer 1

You have to write a stored procedure which does the backup.

 

You fire up the sproc from Visual C#.

 

Answer 2

Hello,

 

I've found a prog ( in VB 2005 the translation in VC# is simple ) which is doing that with Sql Server Management Objects

http://msdn.microsoft.com/en-us/library/ms162166.aspx

 

The problem is i'm not sure whether SMO is working versus a Sql Server 2000 database ( using DMO )

 

The idea of a stored procedure is far from stupid, even interesting.I would prefer :

- to create a backup script inside Entreprise Manager

- to save it in a file .sql

- to import this file in a project VC# so after the installation of the prog, this file .sql is in the same directory as the executable

- in the VC# prog, you read this file with ReadTotEnd() in a string which will be the Sql command text you will pass to a SqlCommand

- to code a SqlCommand.ExecuteNonQuery() and that's working

 

 

SqlConnection conn = new SqlConnection();

SqlConnectionStringBuilder bu = new SqlConnectionStringBuilder();

bu.DataSource = "yoursqlserverinstance";

bu.IntegratedSecurity = true;  // Windows authentification

//no need of a database by default database master

conn.ConnectionString = bu.ConnectionString;

StreamReader sr = new StreamReader("yourfilesql");

String query = sr.ReadToEnd();

conn.Open();

SqlCommand cmd = new SqlCommand(query,conn);

cmd.ExecuteNonQuery();

conn.Close();

 

I've written this code from my memory as i have not my favorite VC# available and i hope there is no fault

 

I hope that post will help you

If you want some more explanation or if you have some problem, please repost.I will try to answer as quickly as possible

 

Have a nice day

 

PS :

- my code will work if  the Windows Connection has the right to backup a database

- you have to obtain a backup script ( either you are able to create it, or the DBA creates it for you). To avoid any problem, please no GO "statement" , it's not a T-SQL statement.

- i will look for SMO ( i've not worked on a Sql server 2000 from 2005 so i've forgotten a part about it )  

- for the code you need using System.IO and using System.Data.SqlClient

Answer 3

Here's some c# code which will back up your user databases - modify it to your needs:using System:

Code Snippet

using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace DBBackupCS
{
    class Program
    {
        static void Main(string[] args)
        {
            Server srv = new Server("MyServer\MyInstance");
            ServerConnection srvConn = srv.ConnectionContext;
            srvConn.LoginSecure = true;
            string strBackupDir = srv.Settings.BackupDirectory;
            DatabaseCollection objDatabases = srv.Databases;
            foreach (Database objDB in objDatabases)
            {
                if (objDB.IsSystemObject == false && objDB.IsMirroringEnabled == false)
                {
                    DateTime datNow = DateTime.Now;
                    string strDBName = objDB.Name;
                    string strBKDate = datNow.ToString("yyyyMMddhhmmss");
                    Backup objBackup = new Backup();
                    objBackup.Action = BackupActionType.Database;
                    objBackup.Database = strDBName;
                    objBackup.BackupSetDescription = "Full backup of " + strDBName;
                    objBackup.BackupSetName = strDBName + " Backup";
                    objBackup.MediaDescription = "Disk";
                    objBackup.Devices.AddDevice(strBackupDir + "\\" + strDBName + "_db_" + strBKDate + ".bak", DeviceType.File);
                    objBackup.SqlBackup(srv);
                    if (objDB.DatabaseOptions.RecoveryModel != RecoveryModel.Simple)
                    {
                        datNow = DateTime.Now;
                        string strTBKDate = datNow.ToString("yyyyMMddhhmmss");
                        Backup objTrnBackup = new Backup();
                        objTrnBackup.Action = BackupActionType.Log;
                        objTrnBackup.BackupSetDescription = "Trans Log backup of " + strDBName;
                        objTrnBackup.BackupSetName = strDBName + " Backup";
                        objTrnBackup.Database = strDBName;
                        objTrnBackup.MediaDescription = "Disk";
                        objTrnBackup.Devices.AddDevice(strBackupDir + "\\" + strDBName + "_tlog_" + strTBKDate + ".trn", DeviceType.File);
                        objTrnBackup.SqlBackup(srv);
                   }
                }
            }

        }
    }
}

 

 

 

 

 

Answer 4

Hello,

 

Have you tried the solution of Allen White ?

 

If yes, could you post it the result ?

 

If you have found another solution, please, could you post it ?

It would be kind to share this solution.

 

Thanks beforehand

 

Have a nice day

 

Answer 5

 

I tried the solution, it worked flawlessly. Remember to incude this at the top of your code:

 

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

 

and include references to :

 

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SqlEnum

Answer 6

Working with Smo is a nice solutions for backups.

Is it possible to create the backup on a local disk of a user instead of on a disk on the server? It would be nice if the backup could be downloaded as a stream, then encrypted and zipped to a by the user selected location on his disk.

I searched the whole day, but cannot find anything. I cannot connect thru FTP to the Sql Database server.

 

Answer 7

This code is awasome, its work real nice.

 

Thanks

 

Answer 8

Hello Allen and Aeroc,

 

This's simply simple,clear, well-formatted code , like every program should be written ( 20/20)

 

Have a nice day

 

 

Answer 9

Papy Normand wrote:

The idea of a stored procedure is far from stupid, even interesting.I would prefer : - to create a backup script inside Entreprise Manager - to save it in a file .sql - to import this file in a project VC# so after the installation of the prog, this file .sql is in the same directory as the executable

 

You can execute .sql file using OSQL or SQLCMD command prompts utilities.

 

To do this on the server side is preferable unless you have reason(s) to do it on the client side.

 

 

Answer 10

Hello SQLUSA,

 

Just a small remark about your answer

I prefer to use SQLCMD which was introduced with Sql Server 2005 than OSQL which came with Sql Server 7.0.

Both would exist with Sql Server 2008 , but the future of OSQL is rather dark

I suppose that, when you advice to execute the script on the server side, it's because of possible problems of "Access denied" ( for example ) on the backup directory ( especially when this directory is not the default one ).

As i'm not sure of the answer, it's the opportunity to ask a confirmation

 

Have all a nice day

 

Answer 11

Can anyone please help me? i am trying to coding the same as Allen's code but it does not work. I am a noob and do not know much about visual studio. It will be great if anyone can help me step by step. thanks a lots.
Basicly what i did is just create a new window application form in visual studio C#. Then right click on the form to copy all of Allan's code into it any changed my server name. It keep showing the error: "Unrecognized escape sequence Line12"

Thanks in advance for your help

Answer 12

Hello,

 

Firstly, as it's your first post, i send you a good welcome on our forums and i hope you will enjoy them especially you will find a quick solution to your problem.

 

It's difficult to help you because of your error :

"Unrecognized escape sequence Line12"
usually , this error appears in VC# when you use in a string  a \  ( backslash ) without doubling it or preceding the string with @

String str = "C:\Program Files";     is false

String str = @"C\Program Files";   is OK

String str = "C:\\Program Files";    is OK

 

1st remark : this forum is SMO/DMO forum , not for VC# problems

http://forums.msdn.microsoft.com/en-US/forums/

http://forums.msdn.microsoft.com/en-US/csharplanguage/threads/

 

2nd remark: it would be difficult to help you if you don't post your code ( at least around the line 12 noticed by the compiler )

 

A little advice : try before to translate the Allen VB code in VC# ( as a console application ). When your code will work, you will try to create a windows application to do the same thing . I'm programing from more 25 years ( 6 in VC#, 20 in C )and i've learnt a good lesson : never to try to mix the problems. You will learn quickly as you will have less problems to solve in the same time 

 

Anyway, post your code ( i'm very fond of VC# )

 

Have a nice day

Answer 13

Allen. As a newcomer to this all, a few questions?
a) if I create a .net application using visual c# 2008 can i use your code in my application and when i publish and give the application to anyone else it will backup the database my app uses?
b) where would that backup file end up?
c) in case of problems and the need to use the backup, how do I restore it back into my application?

thanks

Answer 14

Hi,

Im using this code and working fine in windows authentication. How to make it work in sql server authentication mode, because in the code no where specified Username and Password.

many thanks