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
go
--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
go

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
go
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
go
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
go
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
go

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
/qn
INSTANCENAME = MSSQLServer
REINSTALL = SQL_Engine
REBUILDDATABASE = 1
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
	Domain 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
Result 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

Cryptography
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
T-SQL
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

Full
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
go

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'
go

Tail-log
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
go

Differential
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'
go

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.

Partial
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.

Copy-only
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'
go

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:

• MSDN details on Backup command.
• Another overview of SQL Server backups.
• Incorporating backups in your Maintenance Plans (more technical).

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
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
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
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.

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. 0×01
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];
go;
alter database [my_database] set recovery simple with no_wait;
go;

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):

DBCC LOGINFO;

And you can shrink your logs using this:

exec sp_helpfile;
DBCC SHRINKFILE(name_of_my_log_file, TRUNCATEONLY);
exec sp_helpfile;