Click here to Skip to main content
13,193,600 members (41,711 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi all ;
I am developing an application that uses sql server 2008 database
how to :
- after the application has been installed in the client computer i want to allow that client to attach both the .mdf and log files from the C# application
- The application must take a backup of the database every period of time say every day at 9 clocks.
- The client can restore the database from the backup
so how to do these from C# application
I tried to put the backup in a procedure that the C# executes it but there was a message says that the database is in use so how I can came over these problem
the application is completed and I must finish it tomorrow so please can any one help me.
Posted 25-May-10 22:10pm
Updated 26-May-10 1:24am
Abhinav S415.9K
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

For backup try my article (it is tested on 2005 but it should work on 2008):

Transferring backup files from a remote SQL Server instance to a local machine without network shares, FTP, HTTP[^]

For restore, try plain old sql-restore command (using SqlCommand). However this will only work on local machine. MS dropped restore from pipes in 2005 (some strange policy).
However you CAN do this if you have rights on server and can write some CLR code....

Attaching can be also done with sp_attach_db (detaching similar)

Wish You luck

Adrian
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

In addition to the advice given by Adrian, you should investigate SQL Server Management Objects (SMO) (just google it), since all your requirements can be achieved by their use.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

I made a helper class to perform backups and restores for me.

public class SqlUtil
    {
        public delegate void StatusUpdatedDelegated(string message, int workDone, int totalWork);
        public event StatusUpdatedDelegated StatusUpdated;
        public delegate void OperationCompleteDelegated(SqlUtil me, Exception Error);
        public event OperationCompleteDelegated OperationComplete;
        public delegate void InfoDelegated(string message);
        public event InfoDelegated Info;
 
        Server _srv = default(Server);
        BackupDeviceItem _bdi = default(BackupDeviceItem);
        string _dbName = string.Empty;
        string _sqlInstance = string.Empty;
 
        public bool Connect(string userName, string password, string sqlInstance, string databaseName = "master")
        {
            bool result = false;
            try
            {
                _dbName = databaseName;
                _sqlInstance = sqlInstance;
 
                _srv = new Server();
                _srv.ConnectionContext.ServerInstance = sqlInstance;
                _srv.ConnectionContext.LoginSecure = false;
                _srv.ConnectionContext.Login = userName;
                _srv.ConnectionContext.Password = password;
                _srv.ConnectionContext.Connect();
 
                SQLUtility.MyConnectionString = @"Data Source=" + sqlInstance + ";User Id=" + userName + ";Password=" + password + ";database=" + _dbName;
                result = true;
            }
            catch
            {
                result = false;
            }
            return result;
        }
 
        public void Backup(string fileLocation)
        {
            if (_srv.Databases.Contains(_dbName))
            {
                _bdi = new BackupDeviceItem(fileLocation, DeviceType.File);
 
                Backup sqlBackup = new Backup();
 
                sqlBackup.Action = BackupActionType.Database;
 
                sqlBackup.BackupSetDescription = "ArchiveDataBase: " + DateTime.Now.ToShortDateString();
 
                sqlBackup.BackupSetName =Path.GetFileName(fileLocation);
 
                if (_srv.Configuration.DefaultBackupCompression.Maximum > 0)
                {
                    UpdateInfo("Backing up using Compression");
                    sqlBackup.CompressionOption = BackupCompressionOptions.On;
                }
                else
                    UpdateInfo("SQL Instance doesn't support Compression");
 
                sqlBackup.Database = _dbName;
 
                Database db = _srv.Databases[_dbName];
 
                sqlBackup.Initialize = true;
 
                sqlBackup.Checksum = true;
 
                sqlBackup.ContinueAfterError = true;
 
                sqlBackup.Devices.Add(_bdi);
 
                sqlBackup.Incremental = false;
 
                sqlBackup.ExpirationDate = DateTime.Now.AddYears(999);
 
                sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
 
                sqlBackup.FormatMedia = false;
 
                sqlBackup.Information += (sv, ev) =>
                {
                    UpdateInfo(ev.Error.Message);
                    if (ev.Error.Message.Contains("BACKUP DATABASE WITH COMPRESSION is not supported"))
                    {
                        UpdateInfo("Attempting back up without compression");
                        sqlBackup.CompressionOption = BackupCompressionOptions.Off;
                        sqlBackup.SqlBackupAsync(_srv);
                    }
                };
 
                //sqlBackup.SqlBackup(_srv);
                sqlBackup.PercentComplete += (svv, evv) =>
                {
                    UpdateStatus("Backing up " + _dbName + " from " + _sqlInstance + " (" + evv.Percent + "%)", evv.Percent, 100);
                };
                sqlBackup.Complete += (sv, ev) =>
                {
                    Complete(new Exception(ev.Error.Message));
                };
                sqlBackup.SqlBackupAsync(_srv);
            }
            else
            {
                Complete(new Exception("The Database " + _dbName + " doesn't exist on the SQL Instance"));
            }
        }
 
        public bool DatabaseExists()
        {
            return _srv.Databases.Contains(_dbName);
        }
 
        public void Restore(string fileLocation, bool overwriteIfExists)
        {
            if (_srv.Databases.Contains(_dbName))
            {
                if (overwriteIfExists)
                {
                    UpdateStatus("Droping " + _dbName + " in " + _sqlInstance, 0, 0);
                    _srv.Databases[_dbName].Drop();
                    UpdateStatus("Droping " + _dbName + " Complete", 0, 0);
                }
                else
                {
                    Complete(new Exception("The database " + _dbName + " already exist. To replace this db check 'Overwrite if Exists'"));
                    return;
                }
            }
 
            Restore rs = default(Restore);
            _bdi = new BackupDeviceItem(fileLocation, DeviceType.File);
 
            rs = new Restore();
            rs.NoRecovery = false;
            rs.Devices.Add(_bdi);
            rs.Database = _dbName;
            rs.ReplaceDatabase = overwriteIfExists;
            rs.ContinueAfterError = true;
 
            rs.Information += (sv, ev) =>
            {
                UpdateInfo(ev.Error.Message);
            };
 
            string path = Path.GetDirectoryName(fileLocation).TrimEnd('\\');
            foreach (DataRow dr in rs.ReadFileList(_srv).Rows)
            {
                rs.RelocateFiles.Add(new RelocateFile(dr[0].ToString(), _srv.InstallDataDirectory + "\\DATA\\" + Path.GetFileName(dr[1].ToString())));
            }
            
            UpdateStatus("Restoring " + _dbName,0,0);
            //rs.SqlRestore(_srv);
            rs.PercentComplete += (svv, evv) =>
            {
                UpdateStatus("Restoring " + _dbName + " to " + _sqlInstance + " (" + evv.Percent + "%)", evv.Percent,100);
            };
            rs.Complete += (sv, ev) =>
            {
                Complete(new Exception(ev.Error.Message));
            };
            rs.SqlRestoreAsync(_srv);
            //UpdateStatus("Full Database Restore complete");
        }
 
        public List<string> GetDatabases()
        {
            List<string> databases = new List<string>();
            foreach (DataRow dr in SQLUtility.GetDataTable("sp_databases", commandType: CommandType.StoredProcedure).Rows)
            {
                databases.Add(dr["DATABASE_NAME"].ToString());
            }
            return databases;
        }
 
        private void UpdateStatus(string message, int workDone, int totalWork)
        {
            if (StatusUpdated != null)
            {
                StatusUpdated(message, workDone, totalWork);
            }
        }
 
        private void UpdateInfo(string message)
        {
            if (Info != null)
            {
                Info(message);
            }
        }
 
        private void Complete(Exception Error)
        {
            if (OperationComplete != null)
                OperationComplete(this, Error);
        }
    }


Hope it helps you.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

hi
i have this problem
please if you get the answer
please tell me at:
hadeel-1988@windowslive.com

hadeel - syria
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

oh i have this problem, too
please guide me.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.171018.2 | Last Updated 12 Oct 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100