Shrink a SQL Server database

When we create a SQL Server database, it is created with a full recovery model. This grows the transaction log (the .ldf file) with every transaction we make on the database. Usually the size of the transaction file is many times more than the .mdf file.

If not maintained, the transaction log would eventually fill all the disk space that is available to the physical log files.

In a development/ testing environment, when the data is not important, the transaction log is not important. It is a good idea to maintain the database with a simple recovery model.

Run this command in the Query Analyser to change the database to a Simple Recovery model:
ALTER DATABASE [<DatabaseName>] SET RECOVERY SIMPLE

Run this command to reduce the size of the .ldf file:
DBCC SHRINKDATABASE ([<DatabaseName>], 20)

When deploying the database to production, turn it back to the Full Recovery model:
ALTER DATABASE [<DatabaseName>] SET RECOVERY FULL

Replace <DatabaseName> with the actual name of the database.

Read more:
MSDN Books Online: Transaction Log Management

Copy database diagrams in SQL Server 2005/2008/2012

To copy database diagrams from database dbA to database dbB, run the following in the Query Analyser:

INSERT INTO dbB.dbo.sysdiagrams
SELECT[name],[principal_id],[version],[definition]
FROM dbA.dbo.sysdiagrams

--SELECT * FROM dbB.dbo.sysdiagrams

[Thanks to Pankaj Saha, a colleague at work, for this code snippet.]

Edit: This also works for SQL Server 2008.

Edit: This also works for SQL Server 2012, Service Pack 4.

Edit: Some other links that could be useful:
http://stackoverflow.com/questions/3310137/sql-server-2005-how-to-copy-a-database-diagram-to-another-server
If you get an error "Cannot insert the value null into column "diagram_id"", check this out: http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/767de035-5509-4150-af21-8b6752653f05

Hierarchical Data

A lot of real life data is hierarchical. Some are naturally hierarchical, like a family; others are classifications, like the folders in windows explorer. Either way, this data can be quite complex. Hierarchical data is best represented as a tree because of its ability to present the complexity in an easy to understand manner.

In data driven software, hierarchical data is often stored in a database, and presented in a tree control. Most tree controls will accept XML as data. You can retrieve hierarchical data from most databases in XML format. However the shape of this XML is hardly ever compatible with that required by the tree control, requiring transformation of the data XML to a shape acceptable by the tree control. One approach is to use XSLT to effect this transformation.

This requires the developer to develop an XSLT file. However, many developers find it hard to develop in a declarative language such as XSLT.

In this article, we discuss how to:
1. retrieve hierarchical data from the SQL Server 2005 database as XML data, using Common Table Expressions or CTE.
2. develop an XSLT to transform this XML into a shape acceptable to the tree controls. We will develop XLSTs for the ASP.NET 3.5 tree control and a custom tree based on the article Advanced UI Design Using XML and XSL. The code download also has an example of an ExtJs tree.

Download the article and the code here. [Click on the file name and click the Save As button.]

Or, download the article [http://www.mediafire.com/?jmhcjz9bygm].
Download the code [http://www.mediafire.com/?xmm2xjzmxjv].