SQL Server Programming Series (2): About operations for SMO common objects

Original: SQL Server Programming Series (2): About operations for SMO common objects

In the previous week, I briefly described some of the basic concepts of SMO, in fact, the SMO architecture is far more than the simple one described in the previous article, and is a complete structure shown on MSDN:

is a complete structural diagram that involves a variety of relationships. But the hierarchical relationship between them the week public has made a brief overview of the previous article.
In this article in the first week or on the basis of a little more in-depth introduction, in this article mainly on how to get the database common object information, and how to use SMO to do some daily operations: such as creating login, creating a database, backing up the database and restoring the database. The SQL statements that do the above may have already been written, so let's look at how the code that operates with SMO will write. For the sake of understanding, the comments in the code are more detailed, so there is not much to explain outside the code in this article.Also, it is important to note that the version of the SMO DLL varies greatly with the upgrade, which differs from the. NET Framework, which does not consider backwards compatibility.
The code is as follows:

Using system;using system.collections.generic;using system.linq;using system.text;using The using of microsoft.sqlserver.management.smo.registeredservers;//in Microsoft.sqlserver.smo.dll microsoft.sqlserver.management.smo;//need to add Microsoft.sqlserver.smo.dll reference using microsoft.sqlserver.management.common;//need to add Microsoft.sqlserver.connectioninfo.dll references namespace ssisstudy{//< OUFOXCN http://zhoufoxcn.blog.51cto.com///Sina Weibo address: HTTP://WEIBO.COM/ZHOUFOXCN class SMODemo02 {//&lt ;summary>///Display database Common Object Information example, the code in this method is only for the 9.0 version of the SMO DLL (SQL2005 included assembly)///</summary> public stat            IC void Showsmoobjects () {Console.WriteLine ("Server Group Information"); foreach (ServerGroup servergroup in SmoApplication.SqlServerRegistrations.ServerGroups) {Consol E.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);            }//Create an instance of serverconnection serverconnection connection = new Serverconnection (); Specifies the connection string connection. ConnectionString = "Data source=goodapp;initial catalog=master; User Id=sa;            Password=root; ";            Instantiating Server server server = new server (connection); Console.WriteLine ("Activedirectory:{0},instancename:{1}", Server. ActiveDirectory, server.            INSTANCENAME); The following lists the specific information for each database, foreach (database db in Server.            Databases){Console.WriteLine ("Database name:{0},activedirectory:{1},activeconnections:{2},dataspaceusage:{3},primary FILEPATH:{4} ", Db. Name, Db. ActiveDirectory, Db. Activeconnections, Db. DataSpaceUsage, Db.                Primaryfilepath); Lists data file filegroup information for the database foreach (FileGroup FileGroup in db. Filegroups) {Console.WriteLine ("\tfilegroup name:{0},size:{1},state:{2},urn:{3}", Fileg Roup.                    Name, Filegroup.size, Filegroup.state, Filegroup.urn);                        Lists data file information in each filegroup 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); }}//Lists database log file information 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>//Use SMO to create a SQL login///</summary> public Stati c void Createlogin () {String loginName = "ZHOUFOXCN";//Database login to create string loginpassword = "C #            . NET "//Login password//Create an instance of serverconnection serverconnection connection = new Serverconnection (); Specifies the connection string connection. ConnectionString = "Data source=goodapp;initial catalog=master; User Id=sa;            Password=root; ";            Instantiating Server server server = new server (connection); #region [CREATE Database Login Object]//Check if the login name already exists in the database var querylogin = from Login temp in server. Logins the where string. Equals (temp.            Name, LoginName, stringcomparison.currentcultureignorecase) Select temp; Login Login = QuerYlogin.firstordefault<login> (); If present, delete if (login! = null) {login.            Drop ();            Login = new Login (server, loginName); Login. Logintype = logintype.sqllogin;//Specifies the login method for SQL Authentication login.            passwordpolicyenforced = true; Login. DefaultDatabase = "Master";//default database login.            Create (Loginpassword);  #endregion}///<summary>///Use SMO to create a database///</summary> public static void            CreateDatabase () {String databaseName = "Smodemo";            Create an instance of serverconnection serverconnection connection = new Serverconnection (); Specifies the connection string connection. ConnectionString = "Data source=goodapp;initial catalog=master; User Id=sa;            Password=root; ";            Instantiating Server server server = new server (connection); #region [Create DATABASE object]//Check if the database already exists in the database var querydatabase = frOm Database temp in server. Databases the where string. Equals (temp.            Name, DatabaseName, stringcomparison.currentcultureignorecase) Select temp;            Database Database = Querydatabase.firstordefault<database> (); If it exists, delete if (database! = NULL) {database.            Drop ();            } database = new database (server, databaseName);            Specify database data File details FileGroup FileGroup = new FileGroup {Name = "PRIMARY", Parent = database, IsDefault = true}; datafile datafile = new DataFile {Name = databaseName + "_data", Paren            t = fileGroup, FileName = @ "F:\SQLData2005\" + DatabaseName + ". mdf"};            FILEGROUP.FILES.ADD (datafile);                Specify database log file details 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>///Use SMO to back up the database///</summary> public static void BackupDatabase () {String databaseName = "msdb";//Backup database name string bkpath = @ "C: \";//Store the backed up data            Folder//Create an instance of serverconnection serverconnection connection = new Serverconnection (); Specifies the connection string connection. ConnectionString = "Data source=goodapp;initial catalog=master; User Id=sa;            Password=root; ";            Instantiating Server server server = new server (connection);            #region [CREATE database Backup Object] Backup backup = new Backup (); Backup. Action = backupactiontype.database;//fully backs up backup.            Database = DatabaseName; Backup. BackupSetDescription = "Full backupof Master "; Backup.            BackupSetName = "Master Backup";            Create a backup device 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>//BACKUP DATABASE///</summary> public static void Rest Oredatabase () {String databaseName = "Smodemo";//The database name of the backup string bkpath = @ "C: \";//The text that stores the backed up data            Folder//Create an instance of serverconnection serverconnection connection = new Serverconnection (); Specifies the connection string connection. ConnectionString = "Data source=goodapp;initial catalog=master; User Id=sa;            Password=root; "; Instantiating Server server server = new ServER (connection);            Restore restore = new restore (); Restore.            NoRecovery = false; Restore.            Norewind = false; Restore.            Action = Restoreactiontype.database; Restore.            Database = DatabaseName;            Create a backup device Backupdeviceitem Bkdeviceitem = new Backupdeviceitem ();            Bkdeviceitem.devicetype = Devicetype.file;            Bkdeviceitem.name = Bkpath + DatabaseName + ". Bak";            If you need to re-establish the physical file location of the restore database, you need to know the logical file name of the database files//can restore FILELISTONLY to list the logical file names, if overwriting an existing database can be obtained by SMO Because this service is using the msdb database that was just backed up for restore, it is "msdbdata" and "msdblog"////If you do not specify a restore path, the default reverts to the folder where the database server holds the data Reloc Atefile relocatedatafile = new Relocatefile {logicalfilename = "Msdbdata", Physicalfilename = Bkpath + DatabaseName + ". M            DF "};//(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); }    }}

The results are not mapped here, anyway, is in the Zhou public home of the Chinese environment and the Office English Environment Test passed. In advance, the next article will describe how to get SQL Server object creation SQL statements, such as tables, stored procedures, functions, and so on.

2012-05-23
Zhou Gong

SQL Server Programming Series (2): About operations for SMO common objects