SQL Server编程系列(2):SMO常用对象的有关操作

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using Microsoft.SqlServer.Management.Smo.RegisteredServers;//在microsoft.sqlserver.smo.dll中 
using Microsoft.SqlServer.Management.Smo;//需添加microsoft.sqlserver.smo.dll的引用 
using Microsoft.SqlServer.Management.Common;//需添加microsoft.sqlserver.connectioninfo.dll的引用 
namespace SSISStudy 
{ 
    /// <summary> 
    /// SQL Server编程些列文章(2):SMO常用对象的有关操作 
    /// 作者：周公  
    /// 创建日期：2012-05-23  
    /// 博客地址：http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com  
    /// 新浪微博地址：http://weibo.com/zhoufoxcn   
    class SMODemo02 
    { 
        /// <summary> 
        /// 显示数据库常见对象信息示例 
        /// </summary> 
        public static void ShowSMOObjects() 
        { 
            Console.WriteLine("Server Group Information"); 
            foreach (ServerGroup serverGroup in SmoApplication.SqlServerRegistrations.ServerGroups) 
            { 
                Console.WriteLine("Group Name:{0},Path:{1},ServerType:{2},State:{3},Urn:{4}", serverGroup.Name, serverGroup.Path, serverGroup.ServerType, serverGroup.State, serverGroup.Urn); 
            } 
            Console.WriteLine("Registered Server Information"); 
            foreach (RegisteredServer regServer in SmoApplication.SqlServerRegistrations.RegisteredServers) 
            { 
                Console.WriteLine("Server Name:{0},Login:{1},State:{2},Urn:{3}", regServer.Name, regServer.Login, regServer.State, regServer.Urn); 
            } 
            //创建ServerConnection的实例 
            ServerConnection connection = new ServerConnection(); 
            //指定连接字符串 
            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;"; 
            //实例化Server 
            Server server = new Server(connection); 
            Console.WriteLine("ActiveDirectory:{0},InstanceName:{1}", server.ActiveDirectory, server.InstanceName); 
            //下面列出每个数据库的具体信息 
            foreach (Database db in server.Databases) 
            { 
                Console.WriteLine("Database Name:{0},ActiveDirectory:{1},ActiveConnections:{2},DataSpaceUsage:{3},PrimaryFilePath:{4}", db.Name, db.ActiveDirectory, db.ActiveConnections, db.DataSpaceUsage, db.PrimaryFilePath); 
                //列出数据库的数据文件文件组信息 
                foreach (FileGroup fileGroup in db.FileGroups) 
                { 
                    Console.WriteLine("\tFileGroup Name:{0},Size:{1},State:{2},Urn:{3}", fileGroup.Name, fileGroup.Size, fileGroup.State, fileGroup.Urn); 
                    //列出每个文件组中的数据文件信息 
                    foreach (DataFile dataFile in fileGroup.Files) 
                    { 
                        Console.WriteLine("\t\tDataFile Name:{0},Size:{1},State:{2},Urn:{3},FileName:{4}", dataFile.Name, dataFile.Size, dataFile.State, dataFile.Urn, dataFile.FileName); 
                    } 
                } 
                //列出数据库日志文件信息 
                foreach (LogFile logFile in db.LogFiles) 
                { 
                    Console.WriteLine("\tLogFile Name:{0},Size:{1},State:{2},Urn:{3},FileName:{4}", logFile.Name, logFile.Size, logFile.State,logFile.Urn,logFile.FileName); 
                } 
            } 
        } 
        /// <summary> 
        /// 利用SMO创建SQL登录 
        /// </summary> 
        public static void CreateLogin() 
        { 
            string loginName = "zhoufoxcn";//要创建的数据库登录名 
            string loginPassword = "C#.NET";//登录密码 
            //创建ServerConnection的实例 
            ServerConnection connection = new ServerConnection(); 
            //指定连接字符串 
            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;"; 
            //实例化Server 
            Server server = new Server(connection); 
            #region [创建数据库登录对象] 
            //检查在数据库是否已经存在该登录名 
            var queryLogin = from Login temp in server.Logins 
                             where string.Equals(temp.Name, loginName, StringComparison.CurrentCultureIgnoreCase) 
                             select temp; 
            Login login = queryLogin.FirstOrDefault<Login>(); 
            //如果存在就删除 
            if (login != null) 
            { 
                login.Drop(); 
            } 
            login = new Login(server, loginName); 
            login.LoginType = LoginType.SqlLogin;//指定登录方式为SQL认证 
            login.PasswordPolicyEnforced = true; 
            login.DefaultDatabase = "master";//默认数据库 
            login.Create(loginPassword); 
            #endregion 
        } 
        /// <summary> 
        /// 利用SMO创建数据库 
        /// </summary> 
        public static void CreateDatabase() 
        { 
            string databaseName = "SMODemo"; 
            //创建ServerConnection的实例 
            ServerConnection connection = new ServerConnection(); 
            //指定连接字符串 
            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;"; 
            //实例化Server 
            Server server = new Server(connection); 
            #region [创建数据库对象] 
            //检查在数据库是否已经存在该数据库 
            var queryDatabase = from Database temp in server.Databases 
                                where string.Equals(temp.Name, databaseName, StringComparison.CurrentCultureIgnoreCase) 
                                select temp; 
            Database database = queryDatabase.FirstOrDefault<Database>(); 
            //如果存在就删除 
            if (database != null) 
            { 
                database.Drop(); 
            } 
            database = new Database(server, databaseName); 
            //指定数据库数据文件细节 
            FileGroup fileGroup = new FileGroup { Name = "PRIMARY", Parent = database, IsDefault = true }; 
            DataFile dataFile = new DataFile 
            { 
                Name = databaseName + "_data", 
                Parent = fileGroup, 
                FileName = @"F:\SQLData2005\" + databaseName + ".mdf" 
            }; 
            fileGroup.Files.Add(dataFile); 
            //指定数据库日志文件细节 
            LogFile logFile = new LogFile 
            { 
                Name = databaseName + "_log", 
                Parent = database, 
                FileName = @"F:\SQLData2005\" + databaseName + ".ldf" 
            }; 
            database.FileGroups.Add(fileGroup); 
            database.LogFiles.Add(logFile); 
            database.Create(); 
            #endregion 
        } 
        /// <summary> 
        /// 利用SMO备份数据库 
        /// </summary> 
        public static void BackupDatabase() 
        { 
            string databaseName = "msdb";//备份的数据库名 
            string bkPath = @"C:\";//存放备份后的数据的文件夹 
            //创建ServerConnection的实例 
            ServerConnection connection = new ServerConnection(); 
            //指定连接字符串 
            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;"; 
            //实例化Server 
            Server server = new Server(connection); 
            #region [创建数据库备份对象] 
            Backup backup = new Backup(); 
            backup.Action = BackupActionType.Database;//完全备份 
            backup.Database = databaseName; 
            backup.BackupSetDescription = "Full backup of master"; 
            backup.BackupSetName = "master Backup"; 
            //创建备份设备 
            BackupDeviceItem bkDeviceItem = new BackupDeviceItem(); 
            bkDeviceItem.DeviceType = DeviceType.File; 
            bkDeviceItem.Name = bkPath+databaseName+".bak"; 
            backup.Devices.Add(bkDeviceItem); 
            backup.Incremental = false; 
            backup.LogTruncation = BackupTruncateLogType.Truncate; 
            backup.SqlBackup(server); 
            #endregion 
        } 
        /// <summary> 
        /// 备份数据库 
        /// </summary> 
        public static void RestoreDatabase() 
        { 
            string databaseName = "SMODemo";//备份的数据库名 
            string bkPath = @"C:\";//存放备份后的数据的文件夹 
            //创建ServerConnection的实例 
            ServerConnection connection = new ServerConnection(); 
            //指定连接字符串 
            connection.ConnectionString = "Data Source=goodapp;Initial Catalog=master;User ID=sa;Password=root;"; 
            //实例化Server 
            Server server = new Server(connection); 
            Restore restore = new Restore(); 
            restore.NoRecovery = false; 
            restore.NoRewind = false; 
            restore.Action = RestoreActionType.Database; 
            restore.Database = databaseName; 
            //创建备份设备 
            BackupDeviceItem bkDeviceItem = new BackupDeviceItem(); 
            bkDeviceItem.DeviceType = DeviceType.File; 
            bkDeviceItem.Name = bkPath + databaseName + ".bak"; 
            //如果需要重新制定Restore后的数据库的物理文件位置，需要知道数据库文件的逻辑文件名 
            //可以RESTORE FILELISTONLY 来列出逻辑文件名，如果覆盖已有数据库可以通过SMO来获取 
            //因本处使用的是刚刚备份的msdb数据库来Restore，所以其分别为"MSDBData"和"MSDBLog" 
            //如果不指定Restore路径则默认恢复到数据库服务器存放数据的文件夹下 
            RelocateFile relocateDataFile = new RelocateFile { LogicalFileName = "MSDBData", PhysicalFileName = bkPath + databaseName + ".mdf" };//(databaseName + "_data", bkPath + databaseName + ".mdf"); 
            RelocateFile relocateLogFile = new RelocateFile { LogicalFileName = "MSDBLog", PhysicalFileName = bkPath + databaseName + ".ldf" };//(databaseName + "_log", bkPath + databaseName + ".ldf"); 
            restore.Devices.Add(bkDeviceItem); 
            restore.RelocateFiles.Add(relocateDataFile); 
            restore.RelocateFiles.Add(relocateLogFile); 
            restore.SqlRestore(server); 
        } 
    } 
}