Using SMO to manage a MS SQL Database

From IronPython Cookbook

Contents 1 Using SMO 2 Connecting to the Database Server 2.1 Connecting to a local database using Windows Authentication 2.2 Connecting to a remote database using Windows Authentication 2.3 Connecting to a remote database using SQL Authentication 3 List all Databases 4 Backup a Database 5 Create a new Database 6 List all Logins

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 the Database Server

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.