How to retrieve all enabled users from Active Directory.

The following code will return the name and email address of all users in a specified Active Directory domain that have not been disabled.
The only tricky part is setting up the LDAP query to only return users that have not been disabled. This is accomplished by setting this bitwise filter
"(!(userAccountControl:1.2.840.113556.1.4.803:=2))"

In this line we are saying look at the "UserAccountControl" attribute in Active Directory and if it is not equal to "2" then return true.
The 1.2.840.113556.1.4.803 tells it to use the LDAP_MATCHING_RULE_BIT_AND operator to make the comparison.
There is a little more on using the bitwise operators in ActiveDirectory here .

public static DataTable GetListOfActiveUsers(string domainName)
        {
            DirectoryEntry entry = new DirectoryEntry("LDAP://DC=" + domainName + ",DC=com");
            DirectorySearcher search = new DirectorySearcher(entry);
            string query = "(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(&(mail=*)))";
            search.Filter = query;
            search.PropertiesToLoad.Add("name");
            search.PropertiesToLoad.Add("mail");

            SearchResultCollection mySearchResultColl = search.FindAll();

            DataTable results = new DataTable();
            results.Columns.Add("name");
            results.Columns.Add("mail");

            foreach (SearchResult sr in mySearchResultColl)
            {
                DataRow dr = results.NewRow();
                DirectoryEntry de = sr.GetDirectoryEntry();
                dr["name"] = de.Properties["Name"].Value;
                dr["mail"] = de.Properties["mail"].Value;
                results.Rows.Add(dr);
                de.Close();
            }
            return results;
        }
    }

How to retrive a list of all computers with Last Logon time from Active Directory

I was driving myself crazy trying to retrieve the LastLogonTimestamp from Active Directory with the System.DirectoryServices namespace.
You can read here about the joy of converting the IADSLargeInteger data type (which is what the LastLogonTimestamp is) to something usable  here.
It was pointed out to me that new in .Net 3.5 is the System.DirectoryServices.AccountManagement namespace which make this task much simpler.
Here is the code that finally did the trick for me.
 

  public static DataTable GetListOfComputers(string domainName)
        {
            DataTable results = new DataTable();
            results.Columns.Add("name");
            results.Columns.Add("lastLogonTimestamp");

            PrincipalContext pc = new PrincipalContext(ContextType.Domain, domainName);
            PrincipalSearcher ps = new PrincipalSearcher(new ComputerPrincipal(pc));
            PrincipalSearchResult psr = ps.FindAll();
            foreach (ComputerPrincipal cp in psr)
            {
                DataRow dr = results.NewRow();
                dr["name"] = cp.Name;
                dr["lastLogonTimestamp"] = cp.LastLogon;
                results.Rows.Add(dr);
            }
            return results;
        }

Truncating all tables in a database with sp_msforeachtable

I recently wrote an application that retrieves a list of all computers for a specific domain from Active Directory and proceeds to query each computer for specific info. like Make, Model, Serial Number, OS, Service Packs, Service Tags, RAM, Processor info, Installed Applications etc.
Basically it just automates the process of keeping an up to date inventory of all the computers in my domain and stores the results in a SQL Server 2005 DB.
During the development and testing phase I wound up populating the database with loads of worthless data. after each execution of the partially completed app I wanted to be able to simply Truncate each user table in the user database.
Using the sp_msforeachtable undocumented stored procedure is as simple as this:

use MyDB
exec sp_msforeachtable 'truncate table ?'
If I only want to truncate the tables that belong to a specific schema I could run the following.

Use MyDB
Exec sp_msforeachtable @command1 = '
if (Select Object_Schema_name(object_id(''?''))) = ''dbo''
Begin
TRUNCATE TABLE ?
print ''truncated '' + ''?''
End
'

How to schedule backups in SQL Server 2005 Express

I recently had a customer that needed a database to serve as the backend for a small Document Management system. SQL Server Standard or Enterprise would have been overkill for such a small application.

I decided to use the free SQL Server 2005 Express Edition. The problem is that it is free for a reason. See some of the limitations listed here.

http://www.dotnetspider.com/tutorials/SqlServer-Tutorial-158.aspx

In order to make this work I had to come up with a way to schedule backups. The following stored procedure takes as parameter the type of backup (db OR Log) and will back up all databases on the target server to the specified target folder.

Create PROCEDURE usp_Backup
@Type as varchar(10),
@Path as varchar(255)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @dbs as TABLE
(
id int identity,
name varchar(50),
recovery_model varchar(25)
)
Insert into @dbs Select name, recovery_model_desc from sys.databases where name <> 'tempdb'

Declare @dest as varchar(max)
Declare @sql as varchar(max)
Declare @dbname as varchar(50)
Declare @recovery_model as varchar(50)
Declare @retention_days as int
Declare @cnt as int
Declare @id as int
Select @cnt = count(*) from @dbs
set @id = 1
while @id < @cnt
Begin
Select @dbname = name from @dbs where id = @id
Select @recovery_model = recovery_model from @dbs where id = @id
set @dest = @path + '\' + @dbname + '_' + @type + '_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'

if @type = 'DB'
Backup Database @dbname to disk = @dest
else
If @recovery_model <> 'simple'
Backup Log @dbname to disk = @dest

set @id = @id + 1
End
END
GO

To schedule it I simply created a text file like so.

Use Master
Go
Exec usp_backup @Type='Log', @Path='C:\SQL_Backups'
Go

And scheduled the following to execute using Windows Task Scheduler.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S Server -E -i C:\SQL_Scripts\AllDBBackups.sql

Works like a charm so far.