从C#附加，备份和还原sql数据库 [英] Attach,Backup and Restore sql database from C#

问题描述

大家好；
我正在开发使用sql server 2008数据库的应用程序
如何:
-在客户端计算机上安装该应用程序后，我希望允许该客户端附加来自C#应用程序的.mdf和日志文件
-应用程序必须在每个时间段(例如每天9个时钟)对数据库进行备份.
-客户端可以从备份中还原数据库
那么如何从C#应用程序执行这些操作
我试图将备份放在C#执行备份的过程中，但是有一条消息说数据库正在使用中，因此我该如何解决这些问题
该应用程序已完成，我必须在明天完成，所以请任何人帮助我.

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.

推荐答案

要备份，请尝试我的文章(2005年经过测试，但应该可以使用)在2008年):

将备份文件从远程SQL Server实例传输到没有网络共享，FTP，HTTP的本地计算机上 [ ^ ]

要进行还原，请尝试使用普通的旧sql-restore命令(使用SqlCommand).但是，这仅适用于本地计算机. MS在2005年放弃了从管道中还原的操作(有些奇怪的政策).
但是，如果您拥有服务器权限并可以编写一些CLR代码，则可以执行此操作....

也可以使用sp_attach_db进行连接(分离类似)

祝你好运

Adrian

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

除了Adrian提供的建议外，您还应该调查 SQL Server管理对象(SMO)(只需在Google上进行搜索)，因为您的所有要求都可以

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.

我制作了一个帮助器类，可以为我执行备份和还原.

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.

这篇关于从C#附加，备份和还原sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！