Generate Restore Database Commands
Restoring a database backup from one server to another, or from one database to another on the same machine, often requires MOVE … TO … commands. For databases with a lot of files, this can…
Using Check Constraints with Column Sets
In our previous post on how to use columns sets, we saw how to design a simple table with a set of sparse columns representing 3 different groups of columns. Each group of columns represents…
Using Column Sets for Sparsely Populated Columns
SQL Server has a little-known feature called Column Sets that is very useful when dealing with large numbers of sparse columns. Sparse columns are useful when you need a table with many columns where the…
Filesystem security for TDE Keys and Certificates
What is TDE, and why is filesystem security important? Transparent Database Encryption, or TDE, is used to encrypt an entire database. The encryption is “transparent” since once the database is loaded by SQL Server, anyone…
Cannot use Certificate because its Private Key is not Present
Introduction Transparent Database Encryption, or TDE as it’s colloquially known, is a great solution for data security “at rest”. That is, data is encrypted on-disk, preventing someone with a copy of the database files from…
Adding a New Transaction Log File when the Existing Log is Full
SQL Server Transaction Log files store details about every change made to a SQL Server Data File. This includes when you need to add a 2nd log file. The act of adding a 2nd log…