SQL Server 2005 Disaster Recovery

First, screaming and running around in a circle might help alleviate the built up tension. Especially if you don’t have a good and tested plan.

If we’re dealing with a Simple Recovery Model database, we can restore the full backup followed by the latest differential. Here’s an example of that:

restore database [my_db]
from disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db\my_db_backup_200904262300.bak-f'
with file = 1, nounload, replace, norecovery
--to restore to the 4PM backup
restore database [my_db]
from disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db\my_db_backup_200904271100.bak-d'
with file = 1, nounload, recovery

If we’re dealing with the demo db as it exists now (Full Recovery Model). We want to do a tail-log backup first followed by a full backup restore, then a log restore (if that applies and end with the tail-log restore. Here’s an example of that:

backup log demo
to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\demo\demo_backup_tail.bak'
with no_truncate
restore database demo
from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\demo\demo_backup_200904252350.bakf'
with file = 1, nounload, stats = 10, norecovery
restore log demo
from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\demo\demo_backup_200904261600.trn'
with file=1, norecovery
restore log demo
from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\demo\demo_backup_tail.bak'
with file=1, recovery

Of course no disaster recovery plan is worth anything unless it has been tested. This is one of the major flaws with our installation. The other big one, is that we don’t have a fully workable plan yet.
Recovering from a dead Master db
A dead Master db is a very nasty thing to have happen and can be difficult to restore. You’ll be able to diagnose this problem because the SQL Server service cannot be started. If the corruption had happened in other system db’s and not master then you could still get on and restore the others (msdb, model, etc.) but master is a special case. It contains meta data on all the db’s and SQL Server only knows the other db’s exist through it.
If you have a corrupt master then you may need to re-install SQL Server. Fortunately there’s a way to do this that disrupts the other db’s in a minimal way:

start /wait e:\setup.exe
SAPWD = 'Pa$$w0rd'

Note that the password doesn’t really matter since it is only temporary. Then you can restore the old Master if you have a backup (you should backup your master db once a day):

restore database master
from disk = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master\master_backup_200904262245.bak'

An alternative is to have a recent copy of Master.mdf and .ldf (you have to shut down SQL Server to get it) and then simply attach it. But this has to be ‘recent’ enough to have most of your db information intact. If only a few db’s are ‘new’ or ‘modified’ then this may be an advisable way to restore. Attaching a db is much more efficient and quicker than restoring one from backup. So you’d only have to attach the new db’s and set up any Management admin again for them.

Managing SQL Server 2005 Security

The term ‘Principals’ in the following table refers to authenticated identities in SQL Server; ‘Securables’ refers to objects that SQL Server regulates access to (databases, keys, users, logins, etc.)

Level Principals Notes
Windows Windows Group Securables include files and registry keys; Windows uses ACLs (Access Control Lists) to define which principal has access
User Account
Local User Account
SQL Server SQL Server Login -instance access but not db except, perhaps, master
Server Role -a role here is a group of logins
Database User -what is normally used to access a db
Database Role -a role here is a group of users
Application Role -enables an application to run with its own, user-like permissions; has no members except when used by an application that knows the name and the password

You can Grant, Revoke or Deny permissions to SQL Server Securables by Principal for any of the following actions:

  • create
  • alter
  • drop
  • control
  • connect
  • select
  • execute
  • update
  • delete
  • insert
  • take ownership
  • view definition

But the principal can only perform the action if both of the following are true:

  1. permission has been granted explicitly to the principal or a collection that includes the principal
  2. permission has not been explicitly denied to the principal or a collection that includes the principal

For example: Ben is given ‘select’ permission on a table but he is a member of the Windows Group HRAdmin. If HRAdmin doesn’t have an explicit ‘grant’ (or it was granted but was revoked at some time) then he won’t be allowed to use select on the table.

Example scenarios for differing level permissions.

Principal Permission for Select
Public Deny Grant Grant Deny Grant Revoke
HRAdmin Deny Grant Grant Revoke Revoke
Ben Grant Grant Deny Revoke Revoke
for select
Can’t Can’t Can Can’t Can Can’t
  • In other words, ‘Deny’ always wins the battle.
  • Revoke is an indeterminate state. When permission is revoked it means ‘maybe’ and will look at a higher level (in the collection containing the principal involved) the resolve the question.
  • Server Roles are higher level groups within SQL Server for logins. The admin server roles are outside of the above rules in their particular type of securables and look straight through permissions. See the following table for the list.
Fixed Server Roles
Role Description
sysadmin System administrators: perform any activity i.e.: sa
securityadmin Security administrators: manage and audit server logins
serveradmin Server administrators: configure server wide settings
setupadmin Setup administrators: configure replication and linked servers
processadmin Process administrators: manage SQL Server processes
diskadmin Disk administrators: manage disk files
dbcreator Db creators: create, alter and drop dbs
bulkadmin Can execute BULK INSERT statements
public Automatic role of which all logins are a member; can view any db
  • dbo and guest are special users that are created in all dbs by default.
  • dbo cannot be deleted and has no login.
  • guest is usually denied rights to log in by default and so is only a security risk if someone has granted it extra permissions. Usually you’d want to provide users with a ‘real’ user which you could tailor for their specific requirements.
  • Unlike server roles, you can add your own database roles but usually the roles provided by MS are sufficient (don’t want to reinvent the wheel because then the DBA will have to maintain it).
Fixed Database Roles
Role Description
db_owner Can perform all configuration and maintenance activities on the db, and can also drop the db, really is the god of the db and can perform any db role activity
db_securityadmin Can modify role membership, manage permissions and create schemas. Adding principals to this role could enable unintended privilege escalation
db_accessadmin Can add or remove access to the db for Windows logins, Windows groups, and SQL Server logins, roles and users
db_backupoperator Can back up the db
db_ddladmin Can run any Data Definition Language (DDL) command in a db (add, modify and drop db objects)
db_datawriter Can add, delete, or change data in all user tables
db_datareader Can read all data from all user tables
db_denydatawriter Cannot add, modify, or delete any data in the user tables within a db
db_denydatareader Cannot read any data in the user tables within a db
public Special role which maintains default permissions and cannot be deleted

SQL Server supports the use of keys and digital certificates if that level of data security is required for columns or tables. Built in functions can be used to encrypt and decrypt data.  I’ve seen demonstrations where encryption and decryption are fairly quick.  Personally I don’t think this level of complication is better than a good strategy to keep your network and entire db secure.

Utilities that can explore Security
1)–Provides information about logins and the users associated with them in each database
exec sp_helplogins
2) –Returns information about the roles in the current database
exec sp_helprole
3) –Returns information about the members of a specific SQL Server fixed server role
exec sp_helpsrvrolemember ‘sysadmin’
4) –Reports information about Windows groups with accounts in the current database
exec sp_helpntgroup
5) –Returns a report that has information about user permissions for an object, or statement permissions, in the current database
exec sp_helprotect

SQL Server 2005 Backups

The Types
There are 7 different types of backup available in SQL Server 2005:
1) Full (which is not the same as the Full Recovery Model)
2) Transaction Log
3) Tail-log
4) Differential
5) File / Filegroup
6) Partial
7) Copy-only

This backs up all data, any changes during the backup process and any uncommitted transactions in the log (why uncommitted? because you might do a log backup after doing the Full backup). The init option overwrites the existing backup whereas noinit appends. The format option allows initializing of a tape if you’re using it for media. Here’s an example:
backup database [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_sched.bak'
with noformat, init, name = N'my_db-Full Database Backup', skip,
norewind, nounload, stats = 10

Transaction Log
This backs up the log from the last successful ‘backup log’ to the end of the current transaction log. It also truncates or discards the log information up to the beginning of the active portion of the log (as with truncate is the default). Here’s an example:
backup log [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_log_sched.bak'

Only done in emergencies when the data files are inaccessible but the log file is not damaged. Here’s an example:
backup log [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_tail-log.bak'
with no_truncate

Backs up the extents in the database that have changed since the last full database backup including changes during the backup process and any uncommitted transactions in the log. Here’s an example which appends the differential to the previous full backup:
backup database [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_sched.bak'
with differential, name = N'my_db-differential Database Backup'

File / Filegroup
For very large databases that distributed into various files and filegroups, it is possible to backup specific files and filegroups. See more detailed information here.

Allows full or differential backups of only part of the db. You may, for example, have two filegroups in your db and one is read-only. You only need one backup of the read-only and then you can do partial backups on the rest. See the above link for more details.

This is only available through T-SQL and is commonly used in Development situations. It doesn’t effect normal backup and restore files. For example, if you regularly do a full backup at 11pm and then differentials every four hours, doing a full backup will effect the backup set since the next differential will now rely on the new full backup. Using copy-only avoids this limitation. A great place to use this would be for production db’s to test on development servers. Here’s an example:
backup database [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_copy_only.bak'
with init, copy_only, name = N'my_db-Full Copy_Only Backup'

Backup Integrity
There are three ways to ensure your backups aren’t bad or compromised. Backup using the ‘checksum’ with option which would be verified during the restore (the time added is not noticeable on most machines). Also you could test your backup immediately after doing it using the ‘restore verifyonly … with checksum’ command. This verification is quickly and easily done and should be integrated into any current backup strategy. The last method is ‘Backup Mirroring’ and is only available with the Enterprise edition and only if you have identical hardware to do both backups on.

Other Links:

Recovery Models in SQL Server 2005 – Part 2

I posted about recovery models back in January but I wanted to do another short post that explained them a little better now that I understand them better! There are 3 recovery models used in SQL Server 2005:
1) Simple
2) Full
3) Bulk-logged

Simple is the default model for SQL Server Express db’s and for some of the system databases. Typically used for small db’s or db’s where data doesn’t change frequently. Log file is automatically truncated at every checkpoint and so recovery is limited to the point of the last backup and the .ldf file uses quite a bit less storage space.

Full provides for the best data recovery of the three but does it at the expense of disk space, performance and more administration required (log backups must be scheduled and a different process for disaster recovery must be planned). Recovery can be done to any point in time and, as long as the logs are undamaged, no data is lost except for the transactions actually in process at failure.
Moving from Simple to Full or back again is allowed at any time but it is wise to do full backups after the process. The same applies to Bulk-logged.

Bulk-logged is a version of Full that is designed to decrease the size and running time of the transaction log when doing bulk operations like create index, bulk loads, select into, writetext and updatetext. Instead of storing all the details for these, it simply stores the page-level data of the outcome.

More info
Click here for more information.

SQL Server Data Storage

All db’s have a primary data file: the .mdf file. But secondary data files are (.ndf) are also allowed. The default location for all of these is:
C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data
where ‘n’ is a unique number identifying an installed instance (I’ve got \MSSQL.1\MSSQL\Data).
This changes in SQL Server 2008 (Version 10):  here’s where an install on Vista is:
c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\
Secondary data files are used to split up extremely large databases or to spread data across multiple disks (usually for performance reasons).
Data is written in 8K pages and pages are usually organized into 8 contiguous page ‘extents’ which make up the .mdf or .ndf file (16 extents per Mb). However it is possible for small tables to share a single extent (a mixed extent).

Transaction Log and how it works
At least one transaction log (.ldf) is necessary to run SQL Server. For small databases there is only one LDF. Only one can be used at a time.
Moving transaction logs to a separate drive from the data files is considered a ‘best practice’ since it allows for faster throughput (allows disk heads to remain in place for the serial writing of the transactions and not interrupted by data writes). Typical size for an .ldf file is 25% of the entire database size.
The log is recording more than just the SQL code we put in explicit ‘transactions’ (with a begin tran and commit tran). Implicit transactions are also carried out whenever any of the following occur: alter table, create, delete, drop, fetch, grant, insert, open, revoke, select, truncate table and update. In other words, nearly everything is logged. This may seem excessive but bear in mind that the .ldf is not only there to record your explicit transactions, it is also there so you can recover from a disaster.
Here’s what happens behind the scenes (pay no attention to that curtain, Dorothy) when data modifications occur:

  1. data modification is sent by an app
  2. affected data pages are found in or read into the buffer cache and modified (dirty bit is set for page)
  3. before and after image of the changes recorded in the .ldf
  4. acknowledgement is sent to app that change is committed
  5. checkpoint writes the ‘dirty’ pages to disk, causing the dirty bit to flip. Now the page in cache is ‘clean’.

So the .mdf or .ndf file is only changed after a checkpoint. So that begs the question, ‘When does a checkpoint happen?’ It depends. Usually this process is controlled by SQL Server (usually better off just letting it handle this) but it is possible to force if necessary.
Another way of thinking about a Transaction Log is to visualize a snake eating its own tail example (don’t try this at home with a pet). Assuming a Full Recovery Model, if the head reaches the tail then the log is full and the db becomes read-only (selects work but changes don’t). A log backup is required to clean up (truncate) the log. It will work as long as transactions are committed, checkpointed and backed up. The log backup frees up space by deleting committed, checkpointed and backed up log entries and leaving the rest behind.

Recovery Models in SQL Server 2005

Knowing what recovery model your database is using has important ramifications for database administration and management, especially during recovery. And even more especially, Disaster Recovery! The following code will show the recovery model for all your databases that aren’t system db’s:

select name, recovery_model, recovery_model_desc, state_desc
from sys.databases
where owner_sid .isnotequal. 0x01
order by name;

You need to replace the .isnotequal. with a less than and a greater than key. You can, of course, remove the where clause if you want the system databases included. In my experience casual databases (those which aren’t critical, production db’s for which you need up-to-the-second backups) and more easily managed with the simple recovery model. You do need Full for database mirroring and business dbs, however.
If you want to change a database to the simple recovery model you can use a script like the following:

use [master];
alter database [my_database] set recovery simple with no_wait;

The trouble with the Full Recovery model is that you need to keep on top of it. You’re in charge of shrinking it down when it gets full of old transactions.
You can issue the following command when using a particular database to see how many virtual log files are contained in your current log (there’ll be one row for each and anything beyond 20 rows is probably too much):


And you can shrink your logs using this:

exec sp_helpfile;
exec sp_helpfile;

SQL Server 2005 SP3

Microsoft quietly released a new service pack for SQL Server 2005 (Development release date November 24, 2008 ) on December 15. You can learn how to find it here or here but that’s not the whole story. Upgrading non-Express editions is relatively straightforward but there is a gotcha for Express. You need to get the full release of SQL Server Express and then run it with a command line parameter. If you really think about this, it’s really weird. Microsoft has been promoting Windows operating systems forever now and it forces you to use a command line parameter for the edition of their database that is supposed to be to their low-end and most user friendly and a separate SP3 executable for their other, more advanced editions. This is just making it difficult to do recommended upgrades.

Get with your own program, Microsoft!

Here’s what you need to do for the upgrade:
Download for the regular (32 or 64 bit) version of SSE here or, if you want it with Advanced Services (which I usually do) then use this link.
Run either film with the paramater (don’t forget to keep the capital letters – bother!) SKUUPGRADE=1. So for normal SSE you’d use:


and for SSE with Advanced Services you’d use:


This will change the version from:
9.00.3042 (SP2 – February 9, 2007)

First and Last Date of Month in T-SQL

Here are two functions which my esteemed colleague Brandon Turgeon came up with:

if exists(select object_id from sys.objects where object_id=object_id(N'[dbo].[ufn_getfirstdayofmonth]’))
drop function ufn_getfirstdayofmonth
create function ufn_getfirstdayofmonth
@dateinmonth datetime
returns datetime
declare @first_current_month datetime
declare @first_current_month_hour datetime
declare @first_current_month_min datetime
declare @first_current_month_sec datetime
declare @firstdayofmonth datetime
SELECT @first_current_month = DATEADD(dd, -(DAY(DATEADD(m,0,@dateinmonth))-1), @dateinmonth)
SELECT @first_current_month_hour = DATEADD(hh, -(DATEPART(hour, @first_current_month)), @first_current_month)
SELECT @first_current_month_min = DATEADD(mi, -(DATEPART(minute, @first_current_month_hour)), @first_current_month_hour)
SELECT @first_current_month_sec = DATEADD(ss, -DATEPART(second, @first_current_month_min), @first_current_month_min)
SELECT @firstdayofmonth = DATEADD(millisecond, -DATEPART(millisecond, @first_current_month_sec), @first_current_month_sec)
return @firstdayofmonth

if exists(select object_id from sys.objects where object_id=object_id(N'[dbo].[ufn_getlastdayofmonth]’))
drop function ufn_getlastdayofmonth
create function ufn_getlastdayofmonth
@dateinmonth datetime
returns datetime
declare @first_next_month datetime
declare @first_next_month_hour datetime
declare @first_next_month_min datetime
declare @last_this_month_sec datetime
declare @lastdayofmonth datetime
SELECT @first_next_month = DATEADD(dd, -(DAY(DATEADD(m,1,@dateinmonth))-1), DATEADD(m,1,@dateinmonth))
SELECT @first_next_month_hour = DATEADD(hh, -(DATEPART(hour, @first_next_month)), @first_next_month)
SELECT @first_next_month_min = DATEADD(mi, -(DATEPART(minute, @first_next_month_hour)), @first_next_month_hour)
SELECT @last_this_month_sec = DATEADD(ss, -(DATEPART(ss, @first_next_month_min)+1), @first_next_month_min)
SELECT @lastdayofmonth = DATEADD(millisecond, -(DATEPART(millisecond, @last_this_month_sec)), @last_this_month_sec)
return @lastdayofmonth

And look here’s the test script:

declare @dateinmonth datetime;
set @dateinmonth = getdate();
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date
set @dateinmonth = ‘2008-02-29 12:04:02.113’;
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date
set @dateinmonth = ‘2008-03-01 12:04:02.113’;
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date
set @dateinmonth = ‘2007-02-21 00:00:00.003’;
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date

SQL Server 2005 file sizes and filegrowth

SQL Server’s physical files are set, by default, to auto grow at 1 Mb for data files and 10% for log files. I find 1 Mb number quite low for a normal data file: so I set it to some value between 10 and 100 Mb instead. This is done either in when creating the database (as I’ve shown in a previous entry) or by altering an existing database:

alter database turdus
modify file
(name = ‘AdventureWorks_Data’,
filegrowth = 100mb);
alter database turdus
modify file
(name = ‘AdventureWorks_Log’,
filegrowth = 100mb);

But how do you find out where an existing database stands? Run this script while using AdventureWorks as an example:

select file_id, name, physical_name, size*1.0/128 as [size in mbs], max_size, max_size*1.0/128 as [max_size in mbs],
growth, growth*1.0/128 as [growth in mbs], is_percent_growth
from sys.database_files


  • you can set the database to grow until the disk is full, for this max_size is set to -1.
  • you can also set growth to a percentage, as indicated in the log file default above, the field ‘is_percent_growth’ indicates whether this is the case with your file

Setting your databases to grow automatically can have a performance hit. It usually doesn’t take very long for the autogrowth event to take place but if a user is in the database (why do they insist on changing things?) there could be a error. You can, of course, do the update manually on off hours and use the scheduling system to warn you when this is needed but this isn’t as easy to do on SQL Server Express. I prefer to take the performance hit and use the automatic upgrade but if your server holds sensitive transactions you’ll want to do it differently.
In any case it is useful to keep track of the db growth events. Tim Chapman uses a table to capture this information for logging (as SQL Server doesn’t hold on to the historical data for long). See his article here.

How to determine where a SQL Server 2005 Index is needed

The right index in the right place can really up your database performance.  But applying too many indices can lead to a very nasty overhead problem. So how do you learn where to spend your index resources where they’re most needed?
Knowing your database schema is crucial but a complex database can make deciding where to put your index a very difficult task.
An article by Ian Stirk in January’s MSDN magazine really helped me. The brilliant code and my knowledge of the schema helped pinpoint places where I should put indices. Look, especially, at STEP04 [Identify the missing (or incomplete indexes) (TOP 20), for ALL databases].
Highly recommended.