To kill a stubborn

In MS-SQL, on an attempt to drop a DB getting an 'in use' error, even after it switched to the singly user mode by executing

USE master
GO
ALTER DATABASE [DB]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [DB]
SET READ_ONLY;
GO

As suggested in an answer here, there is a way to find who is actually using the DB:

 

  select  d.name , convert (smallint, req_spid) As spid
      from master.dbo.syslockinfo l, 
           master.dbo.spt_values v,
           master.dbo.spt_values x, 
           master.dbo.spt_values u, 
           master.dbo.sysdatabases d
      where   l.rsc_type = v.number 
      and v.type = 'LR' 
      and l.req_status = x.number 
      and x.type = 'LS' 
      and l.req_mode + 1 = u.number
      and u.type = 'L' 
      and l.rsc_dbid = d.dbid 
      and rsc_dbid = (select top 1 dbid from 
                      master..sysdatabases 
                      where name like 'DB')

SQLXML Bulk import

Import a single table

Get the XSD schema file and add the following markups to it:

1. Mark the parent  elements which do not have a corresponded database table as sql:is-constant="1"
2. Mark the iterated element which each instance should go to a record as sql:relation="DB_table_name"
3. Mark the elements which do not have a corresponded database field as sql:mapped="false"
4. Assign the field name to the elements which database field name is different with sql:field="DB_field_name"
5. Make sure the field length are roomy enough to fit all the data
6. Automatic convertion of date fields does not work properly, so such DB field should be type of string.

Setup a relationship between tables

1. Add the sql:relationship element to xsd:annotation/xsd:appinfo
2. Add attributes which declare the name="Relationship_annotation_name" (random identifier string) and the related DB tables and PK-FK fields as described here. 
3. To the same element where you declared the child DB table name with sql:relation="DB_table_name" add the attribute which references to the declared relationship: sql:relationship="Relationship_annotation_name"

See more examples here.

Execute the prepared bulk import setup

Download and install the SQLXMLBulkload object and execute a VbScript like the following:

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "Connection String to the database"
objBL.ErrorLogFile = "error.log"
objBL.CheckConstraints=True
objBL.KeepNulls=True
objBL.Execute "annotated_schema.xsd", "input_data.xml"

If the object returns an error, check this site, may be it will give you a clue. The log file sometimes contains a bit more information than in the thrown error description.

Object properties to configure

If the tables already exist the .SchemaGen property of the SQLXMLBulkload object should remain FALSE.
If it is set to TRUE, the tables will be created automatically.
String XSD type is declared as [nvarchar](1000).

.KeepIdentity could be useful when a normalized table structure is created, in other words when an attempt to insert a reference record fails when the record already was created (with the primary key). If the property is set to True the records are inserted one by one which is a lot slower than the default bulk inserting. But the reference records duplicates will not be created.

.KeepNulls is useful to left the fields NULL when there is no actual value in the XML.


Here's the list of all properties of the SQLXMLBulkload object.

MS SQL 2008 rake

If you want to modify a table property through the UI which requires a table recreation, then you need to change the following options in Management Studio:
Tools ->Options-> Designers -> Table and Database Designers -> uncheck the option "Prevent saving changes that require table re-creation"

from here