Recovering data from SQL Server backups

30 08 2011

When disaster strikes your backup process what can you do to recover? Recently a customer called in dire straits. They had not noticed that over the last month their backups had been failing due to a lack of disk space. Left with a couple of random, incomplete full backups and an orphaned differential, they needed some help to recover their invaluable data.

So what can you do? Well if you understand the structure of a SQL Server backup you can get in there and get that data. A SQL Server backup file stores the pages of the data file in Microsoft Tape Format with some SQL Server extensions. That means that somewhere in the incomplete backups you have there are data pages with data rows waiting to be extracted.

There are a couple of approaches that can work. If you are lucky then the object id of the table you are interested in has not changed in recent times. You can walk through every 8kb data page looking for those that apply to that object id and then extract the data rows from those pages.

If you are unlucky the object id has changed and you have no idea what it was, in that case you must rely on the schema to help you out. Each data row contains a little information about itself i.e. the number of columns it contains and the length of the fixed length columns. By going through each row and testing if it has the same number of columns and same fixed length size as the schema of the table you are interested in you can guess that this row belongs to the table you are interested in and extract it. This approach worked in our customers case but there is always a chance that you will have false positives when two tables have the same schema.

I’ve started a github project to create a tool to help in this sort of situation. Check it out here.


Disabling Foreign Key and Unique Checks in MySQL

30 06 2011

MySQL has a couple of Server System Variables which control your ability to violate constraints in DML or DDL for InnoDB tables. You can alter these variables dynamically by using the SET statement like so:


Where 0 means “turn these checks off”. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships. Be warned that setting variables back to 1 does not trigger a scan of the tables data, anything inserted while it was turned off will not be checked for consistency.

Enabling the Dedicated Administrator Connection in SQL Server

24 06 2011

SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. The DAC also allows you to query the system base tables you don’t normally have access to. To connect using the DAC, prefix the instance name with “admin:”.

Here are the runes to enable it:

sp_configure 'remote admin connections', 1;

Oracle Globalization

22 06 2011

Globalization Parameters:

Globalization used to be called National Language Support, or NLS. That is why you see the NLS acronym used in views, parameters, environment variables etc.

The view V$NLS_VALID_VALUES provides the range of values for LANGUAGE, TERRITORY, CHARACTERSET and SORT parameters.

NLS_LANGUAGE – determines language for error messages, sets defaults for date language and sort orders. NLS_TERRITORY – sets defaults for day and week numbering, credit/debit symbols, date formats, seperators and currency symbols. NLS_SORT – determines sort order / collation. Default sort order is BINARY, can be changed to be more appropriate to the language used.

To change a (session) parameter: ALTER SESSION set parameter_name=’VALUE’; ALTER SESSION SET NLS_LANGUAGE=’GERMAN’;

Globalization parameters can be set at any and all of five levels:

Priority Level Method
1(Highest) The statement Explicitly set in SQL functions
2 The session Set by an ALTER SESSION statement
3 The client environment Set as an environment variable
4 The instance Specified in the initialization parameter file
5 The database Default (On creation)

On server side, instance settings take precedence over database settings. All server side settings can be overriden on the client side.

The view NLS_DATABASE_PARAMETERS provides the parameter settings at the database (creation) level. The view NLS_INSTANCE_PARAMETERS provides the parameter settings at the instance level. V$NLS_PARAMETERS & NLS_SESSION_PARAMETERS provide the parameter setttings for your session.

Client-Side Environment Settings:

Oracle user process can pick up globalization parameters by checking the NLS_LANG environment variable. This is specified as LANGUAGE_TERRITORY.CHARACTERSET eg. NLS_LANG=FRENCH_CANADA.WEISO8859P1 The user process will issue a series of ALTER SESSION statements to implement the NLS_LANG settings. Conversion between server-side and client-side globalization settings is done by Oracle Net.

Statement Globalization Settings:

You can use NLS parameters in SQL functions to override all other settings eg. SELECT * FROM test ORDER BY NLSSORT(name, ‘NLS_SORT = BINARY_CI’);

Character Sets:

Oracle products ship with their own character sets, independent of the host operating system. Oracle’s default character sets are seven-bit ASCII or seven-bit EBCDIC, dependent on the platform. If you used “Database Creation Assistant” it will pick a default from the OS.

The naming convention for Oracle Database Character Sets is: <region><number of bits used to represent a character><standard character set name>[S|C] [S|C] indicates character sets that can be used only on the server or only on client. eg: WE8ISO8859P1 means “Western European 8-bit ISO 8859 Part 1”

Characters sets are chosen at DB creation time. Character sets can be changed (no guarantee of data preservation) since 9i.

The database character set (NLS_CHARACTERSET) is used to store all the data in columns of type VARCHAR2, CLOB, CHAR, and LONG. This can be any character set as long as it has either US7ASCII or EBCDIC as a subset. The supported Unicode options for NLS_CHARACTERSET are UTF8 or AL32UTF8. The National Character Set (NLS_NCHAR_CHARACTERSET) is used is used for NVARCHAR2, NCLOB and NCHAR. From 9i this can only be Unicode, either AL16UTF16 or UTF8.

There are two tools that assist with changing character sets: Database Character Set Scanner, csscan.exe, will check datafiles and generate a report of possible conversion problems. Language and Character Set File Scanner, lcsscan.exe, will identify the language and character set of a plain text file.

Change the character set with: ALTER DATABASE CHARACTER SET or ALTER DATABASE NATIONAL CHARACTER SET. The target character set must be a superset of the original character set.

Linguistic Sorting and Selection:

The default sort order is binary. This is usually suitable for English, but it can be incorrect for other languages. Linguistic sorting means Oracle will replace each character with a numeric value that reflects its correct position in the sequence appropriate to the language in use. Set NLS_SORT to change the sort order. Each sort order can be appended with _CI for the case-insensitive or _AI for accent-insensitive versions. To avoid specifying NLSSORT in a lot of SQL, UNION, INTERSECT, MINUS for example, you can set NLS_COMP to LINGUISTIC to specify that it should use the NLS_SORT setting for comparisons.