SQL Server programming series (2): operations on common SMO objects

In the previous article, Zhou Gong briefly described some basic concepts of SMO. In fact, the SMO architecture is far more than that described by Zhou Gong in the previous article. It is a complete structure diagram provided by msdn:

Is a complete structure that involves various relationships. However, the hierarchical relationship between them has been briefly outlined in the previous article.
In this article, we will give a more in-depth introduction on the basis of the previous article. This article mainly describes how to obtain common database object information and how to use SMO for some daily operations: such as creating login, creating a database, backing up a database, and restoring a database. The SQL statement for executing the above operations may have been written. Let's take a look at how to write the code using SMO. For ease of understanding, the comments in the Code are more detailed, so there is not much to explain beyond the code in this article. In addition, it should be noted that the dll version of SMO changes greatly with the upgrade, which is different from. NET Framework and does not consider backward compatibility.
The Code is as follows:

Using system; using system. collections. generic; using system. LINQ; using system. text; using Microsoft. sqlserver. management. SMO. registeredservers; // in Microsoft. sqlserver. SMO. using Microsoft. sqlserver. management. SMO; // you need to add Microsoft. sqlserver. SMO. DLL reference using Microsoft. sqlserver. management. common; // you need to add Microsoft. sqlserver. connectioninfo. DLL reference namespace ssisstudy {/// <summary> // SQL Server programming columns (2): SMO Related operation with object // Author: Zhou Gong // creation date: 2012-05-23 // blog address: http://blog.csdn.net/zhoufoxcn http://zhoufoxcn.blog.51cto.com // Sina Weibo address: http://weibo.com/zhoufoxcn class smodemo02 {// <summary> // displays the database Common Object Information example, the code in this method is only for the 9.0 version of smo dll (sql2005 with Assembly) /// </Summary> Public static void showsmoobjects () {console. writeline ("server group information"); foreach (servergroup in smoapplication. sqlserverreg Istrations. 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}, S TATE: {2}, urn: {3} ", regserver. name, regserver. login, regserver. state, regserver. urn);} // create the serverconnection instance serverconnection connection = new serverconnection (); // specify the connection string. connectionstring = "Data Source = goodapp; initial catalog = Master; user id = sa; Password = root;"; // instantiate server Server = new server (connection); console. writeline ("ActiveDirectory: {0}, InstanceName: {1}", server. active Directory, server. instanceName); // The following lists the specific information of each database, 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); // list the data file group information of the database foreach (filegroup in dB. filegroups) {console. writeline ("\ tfilegro Up name: {0}, size: {1}, state: {2}, urn: {3} ", filegroup. name, filegroup. size, filegroup. state, filegroup. urn); // list the data file information foreach (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) ;}} // list the Database Log File Information foreach (logfile in dB. logfiles) {con Sole. 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 an SQL logon // </Summary> Public static void createlogin () {string loginname = "zhoufoxcn"; // The database login name to be created string loginpassword = "C #. net "; // login password // create the serverconnection instance serverconnection connection = new serverconnection ();// Specify the connection string. connectionstring = "Data Source = goodapp; initial catalog = Master; user id = sa; Password = root;"; // instantiate server Server = new server (connection ); # region [Create a database login object] // check whether the login name var querylogin = from login temp in server already exists in the database. logins Where string. equals (temp. name, loginname, stringcomparison. currentcultureignorecase) Select temp; login Login = querylogin. firstordefault <login> ();/ /Delete if (LOGIN! = NULL) {login. drop ();} login = New Login (server, loginname); login. logintype = logintype. sqllogin; // specify the logon method as SQL-based login authentication. passwordpolicyenforced = true; login. defaultdatabase = "master"; // default database login. create (loginpassword); # endregion} // <summary> // create a database using SMO /// </Summary> Public static void createdatabase () {string databasename = "smodemo"; // create a serverconnection instance, serverconnection connectio N = new serverconnection (); // specify the connection string. connectionstring = "Data Source = goodapp; initial catalog = Master; user id = sa; Password = root;"; // instantiate server Server = new server (connection ); # region [Create a database object] // check whether the database already exists. var querydatabase = from database temp in server. databases where string. equals (temp. name, databasename, stringcomparison. currentcultureignorecase) Select temp; database da Tabase = querydatabase. firstordefault <database> (); // Delete if (Database! = NULL) {database. drop ();} database = new database (server, databasename); // specify the details of the database data file filegroup = new filegroup {name = "primary", parent = database, isdefault = true}; datafile = new datafile {name = databasename + "_ data", parent = filegroup, filename = @ "F: \ sqldata2005 \" + databasename + ". MDF "}; filegroup. files. add (datafile); // specify the details of the Database Log File 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 :\"; // folder for storing the backed up data // create the serverconnection instance serverconnection connection = new serverconnection (); // specify the connection string. connectionstring = "Data Source = goodapp; initial catalog = Master; user id = sa; Password = root;"; // instantiate server Server = new server (connection ); # region [Create a database backup object] backup = new backup (); backup. action = backupactiontype. database; // full backup. database = databasename; backup. backupsetdescription = "Full backup of master"; backup. backupsetname = "Master backup"; // create the 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> // back up the database // </Summary> Public static void restoredatabase () {string databasename = "smodemo "; // name of the backup database string bkpath = @ "C: \"; // folder for storing the backup data // create the serverconnection instance serverconnection = new serverconnection (); // specify the connection string. connectionstring = "Data Source = goodapp; initial catalog = Master; user id = sa; Password = root;"; // instantiate server Server = new server (connection ); restore restore = new restore (); restore. norecovery = false; restore. norewind = false; restore. action = restoreactiontype. database; restore. database = databasename; // create the backup device backupdeviceitem bkdeviceitem = new backupdeviceitem (); bkdeviceitem. devicetype = devicetype. file; bkdeviceitem. name = bkpath + databasename + ". bak "; // if you need to reset the location of the physical file of the database after restore, you need to know the logical File Name of the database file. // you can use restore filelistonly to list the logical file names, if the existing database is overwritten, you can use SMO to obtain it. // because this is the backup of the MSDB database, therefore, they are "msdbdata" and "msdblog". // If the restore path is not specified, the system restores them to the relocatefile relocatedatafile = new relocatefile {logicalfilename = "msdbdata" by default", 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 );}}}

The execution result is not shown here. It is tested in the Chinese environment and office English environment of Zhou Gong's home. The next article describes how to obtain SQL statements for creating SQL Server objects, such as tables, stored procedures, and functions.

2012-05-23
Zhou Gong