Generate RESTORE DATABASE command from an existing backup

Posted on May 19, 2012 by Reuben Sultana
Restoring databases is quite simple, especially if you're using the GUI.  Just right-click on the Databases "folder" in SSMS, Choose the "Restore Database" option and the interface will prompt you for the database name, from where you weant to restore, and other parameters.  I prefer scripting though.  One reason is that more options are exposed when using scripting.  The RESTORE DATABASE commnad can be generated by following the steps I just mentioned and clicking the Script button at the top

Read more...
Posted in Backup and Recovery, Database Administration, T-SQL Programming | Tagged , , , , , , , , , , | Leave a comment

Retrieve a Table Schema Only, in XML Format

Posted on May 12, 2012 by Reuben Sultana
This week I had an interesting question: how to retrieve the structure of a table in XML format. SQL Server, or to be more exact T-SQL, has provided support for XML since the 2000 version.  The syntax is very simple, just add FOR XML to any SELECT query and you're done.  Actually you will probably have to add other parts to the clause in order to have the output meet your requirements. The question was how to retrieve the schema.  This can be achieved by adding "FOR XML AUTO, XMLDATA" when

Read more...
Posted in T-SQL Programming | Tagged , , , , , , , , , | Leave a comment

Script to generate CHECK Constraints

Posted on May 5, 2012 by Reuben Sultana
This is another in a series of "scripts that generate scripts"; in this post I will be covering CHECK constraints. A CHECK constraint basically verifies that the value of the column it is checking evaluates to TRUE when the logical statement defining up the constraint is executed.  The definition of the check constraint can for example be a formula that: verifies that the value is within a certain range; e.g. (upper([Gender])='F' OR upper([Gender])='M') e.g. ([HireDate]>='1996-07-01'

Read more...
Posted in Database Administration, Database Documentation, T-SQL Programming | Tagged , , , , , , , | Leave a comment

Database Mail failed to initialize – Unable to start mail session

Posted on April 28, 2012 by Reuben Sultana
After configuring Database Mail on a clustered SQL Server 2008 R2 instance using a script based on the Create a Database Mail profile in 4 Steps (or less) article, the test email was not delivered.  Some investigation showed that the email was actually not sent.  I also checked the Database Mail profile, the account settings and other configurations.  The queries I used to retrieve this information are shown below. USE [msdb] GO -- configuration settings SELECT * FROM dbo.sysmail_profile; SELECT

Read more...
Posted in Database Administration, Security | Tagged , , , , , , , , , | Leave a comment

“Error setting private property ‘VirtualServerName’” when installing an SQL Server 2008 R2 cluster

Posted on April 21, 2012 by Reuben Sultana
I was recently installing SQL Server 2008 R2 Standard Edition on a two-node failover cluster (see Features Supported by the Editions of SQL Server 2008 R2 on MSDN).  The cluster was working perfectly and the Validate a Cluster Configuration Wizard reported all components in green. I had prepared a slipstreamed installation which included SP1 according to the steps explained here.  Once launched, the installation wizard showed me that all SQL Server Failover Cluster installation rules and other

Read more...
Posted in Database Administration | Tagged , , , | Leave a comment