Other articles

1. SQL Snapshot Notes

   Sometimes when testing you need to repetitively restore a database back to a certain point in time. For very large databases the regular SQL restore can take quite a while. It seemed to me that using snapshots instead of regular backups cut this restore down quite a bit.

   To Create …

   read more

   There are comments.

2. Force a SQL Restore

   Really tired of having to google this and piece it together every time I want it, so I'm posting it here.

   alter database DbName
       set single_user
       with rollback immediate
   go
   
   restore database DbName
       from disk = 'path/to/backup.bak'
       with file = 1, replace
   

   Notables:

   • Works best to run against the …

   read more

   There are comments.

3. Update a SQL table's XML value to insert a node

   Sometimes a snippet of example code is all that's needed:

   update MyTable
      set XmlColumn.modify('
         insert <Description>This is a description</Description>
         as last
         into (/xpath/to/parent)[1]
      ')
      where SomeValue = 'whatever'
   

   read more

   There are comments.

4. Drop a SQL column default constraint that has been implicitly named

   CodeMinder: to remind myself of stuff that I usually have to look up each time I do it.

   Notice the unnamed default in the following line:

   alter table test add col1 int not null default 1
   

   This creates a default constraint in the database that is named something like "DF__test__col1__182C9B23 …

   read more

   There are comments.

5. Update a SQL table's XML value to remove nodes

   CodeMinder: to remind myself of stuff that I usually have to look up each time I do it.

   To delete all PersonAlternativeName nodes from XmlColumn of the People table:

   with xmlnamespaces(
       'http://mydomain.com/person/extension/0.1' as px)
   update People
       set XmlColumn.modify('delete //px:PersonAlternateName')
   

   read more

   There are comments.