Click here to Skip to main content
15,881,757 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 26-May-10 1:24am
v2

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
 
Share this answer
 
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.
 
Share this answer
 
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.
 
Share this answer
 
hi i have this problem
please if you get the answer
please tell me at:
hadeel-1988@windowslive.com

hadeel - syria
 
Share this answer
 
oh i have this problem, too
please guide me.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900