Using SMO to manage a MS SQL Database

Using SMO
To use SMO (SQL Managment Objects) you will need to have the SQL Server Management Studio installed locally. The file you must have is Microsoft.SqlServer.Smo.dll, with on a default install should be in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\. You may also need:
 * Microsoft.SqlServer.SmoEnum.dll
 * Microsoft.SqlServer.SqlEnum.dll
 * Microsoft.SqlServer.ConnectionInfo.dll

You will need the following code before you can do anything else. import sys import clr sys.path.append(r"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies") clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll') clr.AddReferenceToFile('Microsoft.SqlServer.SmoEnum.dll') clr.AddReferenceToFile('Microsoft.SqlServer.SqlEnum.dll') clr.AddReferenceToFile('Microsoft.SqlServer.ConnectionInfo.dll') import Microsoft.SqlServer.Management.Smo as SMO import Microsoft.SqlServer.Management.Common as Common

Connecting to a local database using Windows Authentication
db_server = SMO.Server print db_server.Name

Connecting to a remote database using Windows Authentication
db_server = SMO.Server("server.ironpython.info") print db_server.Name

Connecting to a remote database using SQL Authentication
db_server = SMO.Server("server.ironpython.info") db_server.ConnectionContext.LoginSecure = 0 db_server.ConnectionContext.Login = "sa" db_server.ConnectionContext.Password = "MyPassword" print db_server.Name

List all Databases
The following code will show all the non system databases. Remove the 'if not database.IsSystemObject:' line if you want to include the system databases. for database in db_server.Databases: if not database.IsSystemObject: print "Database: "+database.Name

Backup a Database
The following code will create a full backup of a database. Please note that there may be permision issues if you try to set the path to be outside the MS SQL directory tree. database_name = "MyDatabase" backup_file = "C:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDatabaseBackup.bak" bk = SMO.Backup bk.Action = SMO.BackupActionType.Database bk.Database = database_name bk_device = smo.BackupDeviceItem(backup_file,SMO.DeviceType.File) bk.Devices.Add(bk_device) bk.Incremental = False bk.LogTruncation = SMO.BackupTruncateLogType.Truncate bk.SqlBackup(db_server)

Create a new Database
The following will create a new database with its recovery model set to full. NewDB = SMO.Database(db_server,"MyTestDatabase") NewDB.DatabaseOptions.Properties["RecoveryModel"].Value = smo.RecoveryModel.Full NewDB.Create

List all Logins
This will list all the logins on the server. logins = db_server.Logins for l in logins: print l.Name

Back to Contents.