add login,create,backup,restore database to Sql Server using SMO

本章主要是实现asp.net后台程序实现数据库服务器角色名的创建，数据库的创建,备份,恢复操作。

实现：在底层通过Sql Server数据库提供程序(System.Data.SqlClient)下的类来与Sql Server来进行交互的。通过编程的方式利用SMO(SqlServer管理对象系列)来管理。

SMO体系架构

　　

注：很多新人不清楚"SQL Server实例"是什么意思,简单地说 如果你的机器只安装了一个版本数据库服务器那么实例也就是安装时默认的，比如你访问你的数据库服务器时,登录界面服务器名称一栏只需输入"."就可以了，或者输入默认的"机器名/实例名",比如我的数据库服务器安装时默认的实例名为：SQLEXPRESS，如图：

　　

当然如果你的服务器安装了多个版本的数据库服务器 则按"机器名/实例名"规则来输入,如果访问其他机器上的数据库服务器，则按"ip/实例名"

下面是查看自己数据库服务器实例名的步骤，如下图：

已在：win7(32位)+Visual Studio2008+SQL Server2008 环境下 测试成功。

代码如下,相关注意事项 已注释说明：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Common;//需要引用Microsoft.SqlServer.ConnectionInfo.dll
using Microsoft.SqlServer.Management.Smo;//需要引用Microsoft.SqlServer.Smo.dll
using Microsoft.SqlServer.Management.Sdk.Sfc;//需要添加引用
using System.Text;
using Microsoft.SqlServer.Management.Smo.RegisteredServers;//需要引用Microsoft.SqlServer.SmoExtended.dll
//using Microsoft.SqlServer.Management.RegisteredServers;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Dmf;
namespace YS.Web.Admin.myDemo
{
    public partial class SmoMDB : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            createLogin();
            createDataBase();
            backupDataBase();
            restoreDataBase();
        }
        /// <summary>
        /// 利用SMO创建SQL登陆名
        /// 并指派数据库权限：只可访问master数据库
        /// </summary>
        private void createLogin()
        {
            string loginName = "Ivan";
            string loginPwd = "123abc";
            //创建ServerConnection实例
            ServerConnection connection = new ServerConnection();
            //指定连接字符串
            connection.ConnectionString = "Data Source=*.*.*.*;Initial Catalog=testDB;User ID=sa;Password=pwd;";
            //实例化Server
            Server server = new Server(connection);
            //检查数据库是否已经存在该登录名

            //var queryLogin = from Microsoft.SqlServer.Management.Smo.Login temp in server.Logins
            //                 where string.Equals(temp.Name, loginName, StringComparison.CurrentCultureIgnoreCase)
            //                 select temp;
            //Microsoft.SqlServer.Management.Smo.Login lo = queryLogin.FirstOrDefault<Microsoft.SqlServer.Management.Smo.Login>();
            //if (lo != null) 
            //{
            //    lo.Drop();
            //}
            Microsoft.SqlServer.Management.Smo.Login login = server.Logins[loginName];//注：这里面的Login 与系统Login页面发生冲突 需要加引用
            if (login != null) 
            {
                login.Drop();
            }
            login = new Microsoft.SqlServer.Management.Smo.Login(server, loginName);
            login.LoginType = LoginType.SqlLogin;//需要添加 Microsoft.SqlServer.SmoEnum.dll
            login.PasswordPolicyEnforced = true;
            login.DefaultDatabase = "master";
            login.Create(loginPwd);
            Response.Write("创建SQLServer登入账户成功,loginName="+loginName+"&loginPwd="+loginPwd);
        }
        /// <summary>
        /// 利用SMO创建数据库
        /// </summary>
        private void createDataBase()
        {
            string dbName = "DBSMO";//你需要创建的数据库的名称
            ServerConnection connection = new ServerConnection();
            connection.ConnectionString = "Data Source=*.*.*.*;Initial Catalog=testDB;User ID=sa;Password=pwd;";
            Server server = new Server(connection);
            Database db = server.Databases[dbName];
            //检查数据库中是否存在同名数据库 如果同名则删除，还可以使用Linq进行排查 见注释
            #region Linq
            //var queryDatabase = from Microsoft.SqlServer.Management.Smo.Database temp in server.Databases
            //                    where string.Equals(temp.Name, dbName, StringComparison.CurrentCultureIgnoreCase)
            //                    select temp;
            //                  Database database=queryDatabase.FirstOrDefault<Database>();
            //                  if (database != null) { database.Drop(); }
            #endregion
            if (db != null) 
            {
                db.Drop();
                Response.Write("已存在同名数据库 将其删除");
            }
            db = new Database(server, dbName);
            //指定数据库 数据文件细节
            FileGroup fileGroup = new FileGroup { Name = "PRIMARY", Parent = db };//, IsDefault = true 是DEFAULT 并不是关键字。DEFAULT 是默认文件组的标识符 || PRIMARY：[PRIMARY]是表示表是建立在主文件组上； 
                                                                                        //异常详细信息: Microsoft.SqlServer.Management.Smo.SmoException: 主文件组已经是默认文件组。

            DataFile dataFile = new DataFile
            {
                Name = dbName + "_data",
                Parent = fileGroup,
                FileName = @"D:\SqlDBTest\" + dbName + ".mdf"//对文件 "D:\SqlDBTest\DBSMO.mdf" 的目录查找失败，出现操作系统错误 2(系统找不到指定的文件。)。
                                                                //CREATE DATABASE 失败。无法创建列出的某些文件名。请查看相关错误。 注意：需要创建文件夹
            };
            fileGroup.Files.Add(dataFile);
            //指定数据库 日志文件细节
            LogFile logFile = new LogFile { Name = dbName + "_log", Parent = db, FileName = @"D:\SqlDBTest\" + dbName + ".ldf" };

            db.FileGroups.Add(fileGroup);
            db.LogFiles.Add(logFile);
            db.Create();
            Response.Write("成功创建了数据库"+dbName);
        }
        /// <summary>
        /// 利用SMO备份数据库
        /// 调试过了 备份数据库无需切断连接该数据库的进程**
        /// </summary>
        private void backupDataBase()
        {
            string dbName = "DBSMO";//不用说了 肯定是你需要备份的数据库名 DBSMO
            string bkPath=@"D:\SqlDBTest\";//存放数据库备份后的数据文件的文件夹
            ServerConnection connection = new ServerConnection();//创建ServerConnection的实例
            connection.ConnectionString = "Data Source=*.*.*.*;Initial Catalog=testDB;User ID=sa;Password=pwd;";//指定连接字符串
            Server server = new Server(connection);//实例化Server

            Backup backup = new Backup();//实例化创建数据库备份对象
            backup.Action = BackupActionType.Database;//Database:完全备份  Files：文件 Log:日志文件
            backup.Database = dbName;
            backup.BackupSetDescription = "Full Backup of DBSMO";
            backup.BackupSetName = "DBSMO Backup";
            //创建备份设备
            BackupDeviceItem bkDeviceItem = new BackupDeviceItem();
            bkDeviceItem.DeviceType = DeviceType.File;
            bkDeviceItem.Name = bkPath + dbName + ".bak";

            backup.Devices.Add(bkDeviceItem);
            backup.Incremental = false;
            backup.LogTruncation = BackupTruncateLogType.Truncate;
            backup.SqlBackup(server);
            Response.Write("数据库"+dbName+"备份成功");
        }
        /// <summary>
        /// 利用SMO恢复数据库
        /// </summary>
        private void restoreDataBase()
        {
            string dbName = "DBSMO";
            string bkPath = @"D:\SqlDBTest\";
            ServerConnection connectioin = new ServerConnection();
            connectioin.ConnectionString = "Data Source=*.*.*.*;Initial Catalog=testDB;User ID=sa;Password=pwd;";
            Server server = new Server(connectioin);
            //创建数据库恢复对象
            Restore restore = new Restore();
            restore.NoRecovery = false;//异常：尚未备份数据库 "DBSMO" 的日志尾部。如果该日志包含您不希望丢失的工作，请使用 BACKUP LOG WITH NORECOVERY 备份该日志。
                                        //请使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来只覆盖该日志的内容。RESTORE DATABASE 正在异常终止。
            restore.ReplaceDatabase = true; //异常解决 注意这个：ReplaceDatabase 更换数据库 
            restore.NoRewind = false;
            restore.Action = RestoreActionType.Database;
            restore.Database = dbName;

            //创建数据库恢复设备
            //创建备份设备  
            BackupDeviceItem bkDeviceItem = new BackupDeviceItem();
            bkDeviceItem.DeviceType = DeviceType.File;
            bkDeviceItem.Name = bkPath + dbName + ".bak";


            //如果需要重新制定Restore后的数据库的物理文件位置，需要知道数据库文件的逻辑文件名  
            //可以RESTORE FILELISTONLY 来列出逻辑文件名，如果覆盖已有数据库可以通过SMO来获取  
            //因本处使用的是刚刚备份的msdb数据库来Restore，所以其分别为"MSDBData"和"MSDBLog"  
            //如果不指定Restore路径则默认恢复到数据库服务器存放数据的文件夹下  
            RelocateFile relocateDataFile = new RelocateFile { LogicalFileName = "DBSMO", PhysicalFileName = bkPath + dbName + ".mdf" };//(databaseName + "_data", bkPath + databaseName + ".mdf");  //LogicalFileName 是逻辑名需要对应不同的数据库获取
            RelocateFile relocateLogFile = new RelocateFile { LogicalFileName = "DBSMO_log", PhysicalFileName = bkPath + dbName + ".ldf" };//(databaseName + "_log", bkPath + databaseName + ".ldf");  //异常：逻辑文件 'MSDBData' 不是数据库 'DBSMO' 的一部分。
                                                                                                                                                                                                         // 请使用 RESTORE FILELISTONLY 来列出逻辑文件名。 RESTORE DATABASE 正在异常终止。 
            
            restore.Devices.Add(bkDeviceItem);
            restore.RelocateFiles.Add(relocateDataFile);
            restore.RelocateFiles.Add(relocateLogFile);
            restore.SqlRestore(server);
            Response.Write("数据库"+dbName+"恢复成功");
        }
    }
}