SQL Server 2005 — it’s a wrap; but wait — it’s just the beginning!

I’m sure there are lots of coworkers blogging about this. We’re done with SQL Server 2005 and it’s a very interesting and good feeling. Having worked 5 year on the project, many long days, many weekends, having to fight many battles, and at the same time giving an incredible amount of attention to the security…

2

SMO Samples Galore

With much of the SQL Server 2005 devcelopment behind us, I am starting to have some more time to post some SMO samples. Most of these are inspired on questions in the beta newsgroups or the SMO/DMO forum. Additionally, SQL Server 2005 ships with a pretty wide variety of samples. In fact, there are more…

3

SMO Sample: Which SQL Server version is supported by the property?

Purpose of sample: List metadata information about SMO object properties SMO’s object model is different from DMO in one important way: DMO introduced the ‘2’ (like Database2) objects in order to add SQL Server 2000 support. SMO objects are hybrid in that perspective, as these objects morph or limit behavior based on the SQL Server…

1

SMO Sample: Create and Start a Job

Purpose of sample: Create simple job and start the job after creation.   Server svr = new Server(instance);JobServer agent = svr.JobServer; if (agent.Jobs.Contains(“New Smo Job”)){   agent.Jobs[“New Smo Job”].Drop();} Job j = new Job(agent, “New Smo Job”);JobStep js = new JobStep(j, “Step 1”);js.SubSystem = AgentSubSystem.TransactSql;js.Command = “select 1”;j.Create();j.ApplyToTargetServer(svr.Name); j.Start(); Disclaimer: this sample doesn’t handle exceptions and may…

5

SMO Sample: Backup database to Mirrors

Purpose of sample: Backup database to various mirrors Server svr = new Server(); Backup b = new Backup(); b.BackupSetDescription = “Set description”;b.Action = BackupActionType.Database;b.BackupSetName = “Backup set #1″;b.Database = “msdb”;b.MediaDescription = “Media description”;b.MediaName = “Media name”;b.Devices.AddDevice(@”c:\back1a.bak”, DeviceType.File);b.Devices.AddDevice(@”c:\back1b.bak”, DeviceType.File); BackupDeviceList[] la = new BackupDeviceList[2]; BackupDeviceList l1 = new BackupDeviceList();l1.AddDevice(@”c:\back2a.bak”, DeviceType.File);l1.AddDevice(@”c:\back2b.bak”, DeviceType.File); BackupDeviceList l2 = new BackupDeviceList();l2.AddDevice(@”c:\back3a.bak”,…

1

SMO Sample: Partitions

Purpose of sample: create a partition function and partition scheme on an existing database Server svr = new Server(instance); if (svr.Databases.Contains(“smotestdb”)) // change the name if needed{   svr.Databases[“smotestdb”].Drop();} Database db = new Database(svr, “smotestdb”);db.Create(); db.FileGroups.Add(new FileGroup(db, “PART1_FG”));db.FileGroups.Add(new FileGroup(db, “PART2_FG”));db.FileGroups[0].Files.Add(new DataFile(db.FileGroups[0], “datafile1”, @”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db_1.mdf”));db.FileGroups[1].Files.Add(new DataFile(db.FileGroups[1], “datafile2”, @”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db_2.mdf”));db.Alter(); PartitionFunction pf = new PartitionFunction(db,…

1

SMO Sample: Transfer

Purpose of sample: Transfer a database; all objects and data Server tgt = new Server(“.”); // Setup source connection (in this sample source is .\inst1 and target is ‘.’ (the default instance)Server svr = new Server(@”.\inst1″);Database db = svr.Databases[“testdb”]; // Setup transferTransfer t = new Transfer(db);t.CopyAllObjects = true;t.DropDestinationObjectsFirst = true;t.CopySchema = true;t.CopyData = true;t.DestinationServer =…

3

SMO Sample: Table column default constraint

Purpose of sample: show how a column constraint can be created. Table tbl = new Table (db, “MyTestTable”);Column c1 = new Column(tbl, “c1”, DataType.DateTime);c1.AddDefaultConstraint(/*name optional*/);c1.DefaultConstraint.Text = “getdate()”;tbl.Columns.Add(c1);tbl.Create(); Disclaimer: this sample doesn’t handle exceptions and may not function as expected. Use at own risk. It is good practice to test an application before using it in…

1

TechED: SMO session done! Now what?

The SMO session was on today! Wow the Euro-crowd is tough! (which I heard uttered by various speakers after being shell shocked by unexpected scores) Jet lag and lack of sleep last few days made this session a hard nut to crack. (the proverb “I can do this in my sleep” is something that really…

0

Project code named ‘Mercury’ (not "SQL Server Web Data Administrator")

Granted, I was asking for this. When we were starting the development of the follow-up version of (here comes the culprit) the new SQL Server Web Data Administrator, Euan was still my boss and asked me to change the name. Long story short, Shipping SQL Server has priority, and I couldn’t imagine myself sitting in…

7