SQL SERVER – How to Roll Back SQL Server Database Changes

In a perfect scenario, no unexpected and unplanned changes occur. There are no unpleasant surprises, no inadvertent changes. However, even with all precautions and testing, there is sometimes a need to revert a structure or data change.

One of the methods that can be used in this situation is to use an older database backup that has the records or database object structure you want to revert to. For this method, you have to have the adequate full database backup and a tool that will help you with comparison and synchronization is preferred.

In this article, we will focus on another method: rolling back the changes.

This can be done by using:

  • An option in SQL Server Management Studio
  • T-SQL, or
  • ApexSQL Log

The first two solutions have been described in this article

The disadvantages of these methods are that you have to know when exactly the change you want to revert happened and that all transactions on the database executed in a specific time range are rolled back – the ones you want to undo and the ones you don’t.

How to easily roll back SQL Server database changes using ApexSQL Log?

The biggest challenge is to roll back just specific changes, not all changes that happened in a specific time range.

While SQL Server Management Studio option and T-SQL read and roll forward all transactions in the transaction log files, I will show you a solution that finds and scripts only the specific changes that match your criteria. Therefore, you don’t need to worry about all other database changes that you don’t want to roll back.

ApexSQL Log is a SQL Server disaster recovery tool that reads transaction logs and provides a wide range of filters that enable you to easily rollback only specific data changes.

First, connect to the online database where you want to roll back the changes.

Once you select the database, ApexSQL Log will show its recovery model. Note that changes can be rolled back even for a database in the Simple recovery model, when no database and transaction log backups are available. However, ApexSQL Log achieves best results when the database is in the Full recovery model and you have a chain of subsequent transaction log backups, back to the moment when the change occurred.

In this example, we will use only the online transaction log.

In the next step, use filters to read only the transactions that happened in a specific time range.

To remove noise, it’s recommended to use as many filters as possible. Besides filtering by the time of the transaction, ApexSQL Log can filter by the operation type:

Table name:

As well as transaction state (committed, aborted, running, and unknown), name of the user who committed the change, specific field values, server process IDs, and transaction description.

You can select only the tables affected by the changes you want to roll back. However, if you’re not certain which tables were affected, you can leave them all selected and once the results are shown in the main grid, analyze them to find the ones you to roll back.

When you set the filters, you can select how to present the results. ApexSQL Log can automatically create undo or redo scripts, export the transactions into an XML, HTML, CSV, SQL, or SQL Bulk file, and create a batch file that you can use for unattended transaction log reading.

In this example, I will open the results in the grid, as I want to analyze them before rolling back the transactions.

The results contain information about the transaction, as well as who and when made it.

For UPDATEs, ApexSQL Log shows both old and new values, so you can easily see what has happened.

To create an UNDO script that rolls back the changes, select the transactions you want to roll back and click Create undo script in the menu.

For the DELETE statement selected in the screenshot above, the undo script is:

INSERT INTO [Sales].[PersonCreditCard] ([BusinessEntityID], [CreditCardID], [ModifiedDate])
VALUES (297, 8010, '20050901 00:00:00.000')

When it comes to rolling back database changes, ApexSQL Log has a big advantage, as it rolls back only specific transactions, while leaving all other transactions that occurred at the same time range intact. That makes ApexSQL Log a good solution for rolling back inadvertent data and schema changes on your SQL Server databases.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – How to Format and Refactor Your SQL Code Directly in SSMS and Visual Studio

This article shows how to use ApexSQL Refactor,a free SQL code formatting and refactoring tool. You can download ApexSQL Refactor, and explore it through the article.

ApexSQL Refactor is a free tool, for SQL code formatting and refactoring directly from SSMS or Visual Studio. You can qualify SQL Server object names, expand wildcards, or encapsulate SQL code. The add-in has nearly 200 formatting options and 11 code refactors. Using this tool allows you to locate and highlight unused variables and parameters. In addition, you can update all dependent database objects on renaming or changing columns and parameters. Besides SQL code in SSMS or Visual Studio, you can format SQL code from the external SQL scripts. The add-in integrates under the ApexSQL menu in SSMS or Visual Studio. To format SQL code inside SSMS or Visual Studio, select it in the query window and choose the ApexSQL default option, or other user- defined templates from the ApexSQL Refactor menu. In the same menu, you can find the Formatting options option:

Format SQL code

In the Formatting options section you can modify ApexSQL default formatting, or create your own formatting templates. Click the New button in the upper side of the window, and the new formatting template will be created. In the General tab, you can set indention, whether you are using spaces or tabs. You can set the wrapping to be applied to the lines longer than the specified number of characters, or add spacing inside/outside parenthesis, around operators (assignment, arithmetic, and comparison), and before/after commas.

Here you can manage empty lines, and set the placement for the opening and closing brackets:

In the Capitalization tab, you can enforce the capitalization for SQL keywords, data type, identifiers, system functions, and variables. For each of the mentioned, you can choose from the drop down list whether it is going to be capitalized in upper case, proper case, or lower case.

In the Comments tab you can manage comments, adding an empty line or border before/after the block comment. In addition, you can switch all comments to block/line comments, or remove all block/line comments:



Under the Expressions tab, you can set the formatting options for the operators (arithmetic, comparison logical). If you enable formatting for any operator, you will be able to set the minimum number of characters for the operator to apply formatting on. In addition, you can set the parenthesis placement, to move the operation to a new line, or to show the operations on multiple lines:

In the Schema statements tab, you can setup formatting for the object definitions and parameters. For the object definitions, you can choose to place the body on a new line and to set the indentation. Parameters can be placed on a new line (aligned with keyword, or indented for a defined number of spaces). If there are more than one parameter, each one can be placed on a new line with a comma before/after the row:



In the Data statements tab, you can set the options for column list, data statement, nested SELECT statements, and aliases. A column list can be placed on a new line, aligned with a keyword, or with the defined indentation. Each column can be placed in a new line, with a comma before/after it. A minimum number of characters can be defined for the data statements to be formatted. SQL keywords FROM and WHERE can be placed in a new line, aligned with keyword or indented.

For each nested SELECT statement, the first SELECT can be placed on a new line at the same position, aligned with SQL keyword, or indented. In addition, subsequent nested SELECT can be aligned with the previous SELECT.

All alias names can be aligned. The AS keyword can be used in all aliases in the SELECT statements, placed on a new line (aligned with keyword or indented):

In the Joins tab, you can set the minimum number of characters for JOIN statement to be formatted. The first table can be placed on a new line (at the same position, aligned with previous keyword, or indented). Joined keyword can be placed at start/end of the line, or on a separate line with indention. ON keyword can be placed on a new line aligned with JOIN keyword, or indented. Nested join operations can be placed on separate lines, aligned with previous JOIN keyword, or indented:

In the Value lists tab you can set the value list to be placed ona new line, aligned or indented. Earch row from the list can be placed in a separate line with comma before/after each row. Row values can be placed on a new line, aligned or indented. At the end, each value can be placed in a separate line, with comma before/after each row:

In the Variables tab, you can set the variables to be placed on a new line, aligned with keyword, or indented. Each variable can be placed on a new line with a comma before/after each row:

In the Flow control tab, you can set the condition keywords (WHEN, THEN, and ELSE) to be places on a new line and indented. In addition, you can enforce BEGIN and END keywords to be used in IF statements, and in stored procedures.

To format SQL code outside SSMS or Visual Studio, click the Format SQL scripts option from the ApexSQL Refactor menu, and the Format SQL files dialog opens. Here you can browse your computer for SQL files, and apply specified formatting option (ApexSQL default, or user created template). You can overwrite an old SQL file, with a new one, or create new SQL file, and keep the old one

Split table

This option is used to split a SQL database table into two tables by copying or moving columns from the original table to a new one. It is useful when a table contains rarely used columns, they can be moved to another table, so the original table contains less frequently used columns. To split a table, right click on it in Object Explorer in SSMS or Visual Studio, and choose the Split table option from the ApexSQL Refactor menu to open
the Split table dialog. You can copy/move columns from the original table to a new one. For the new table you can define the name and schema. When you set the columns for the new table, you can preview the generated script, see the impact of changes and affected dependent objects:

Safe rename

Using this feature allows you to rename database objects without breaking the database dependencies, as it generates a SQL script for changing the object name and update all dependent objects. The Safe rename option can be applied to database tables, views, procedures, and functions. It applies to a table/view columns and function/procedure parameters. To rename any of the mentioned objects right click on it from the Object Explorer, or select it, and choose the Safe rename option from the ApexSQL Refactor menu. This will open the Safe rename dialog where you can enter a new name for the selected object, and clicking the Generate preview option, you can preview the script used to change the object name. If an error appears when renaming, it will be shown under the Warnings tab. The Sequence tab shows the process of renaming the object, listing the sequences that will be executed in order to rename the selected object:

Add surrogate key

When a primary key contains many columns, or it needs to be changed for any reason, a surrogate key is considered. Changing a primary key in database table requires updating all dependent object, in order to keep database functionality. To add surrogate key, select the table in Object Explorer and choose the Add surrogate key option from the ApexSQL Refactor menu. This will open the Add surrogate key dialog where you can choose the one of the existing keys, and specify the Surrogate column name value. The Generate preview option shows the generated SQL script in the preview section. All dependent objects, sequences, and warnings (if exist) will be shown under the appropriate tabs:

Change parameters

Stored procedure or function parameters can be changed by deleting and recreating, or using the ALTER statement. To change the parameters safely, use theChange parameters option in ApexSQL Refactor. Select the stored procedure or function parameter and choose the Safe rename option from the ApexSQL refactor menu. In the Safe rename dialog, change the parameter name, and generate the preview of a SQL script. Under the appropriate tab, you can review all dependent objects, warnings (if exist), and sequences that will be executed on renaming the parameter:

Replace on-to-many relationship

To use this option select the table from the Object Explorer and choose the Replace one-to-many relationship option from the ApexSQL Refactor menu. This will open a dialog where you can specify the name of the associative table, choose the dependent table, and a relationship. The Generate preview option generates a SQL script, which replaces a relationship. Under the appropriate tabs, you can review warnings, sequences, and dependent objects:

Copy code as

This option will convert SQL code into the supported programming languages. Supported languages are Java, VB.NET, C#, Perl, PHP, Delphi, Power Builder, Ruby, and C++. You can add additional template for other programming languages choosing the Customize languages option from the Copy code as submenu. To convert SQL code into any of the listed programming languages, point to a query window with SQL code you want to convert, and choose the language from the list. One you click the language from the list, open a new query window, and paste the created code:

The Customize language template dialog allows you to edit templates for natively supported languages, or add new templates. Here you can enter a code that will be inserted before/after SQL code, define escape character for quotes, and preview the defined settings:

Unused variables and parameters

Parameters or variables declared or assigned a value, but never use or queried in any statements as UPDATE, EXECUTE, WHERE, INSERT or PRINT, is unused. ApexSQL Refactor can highlight unused SQL objects, and clean up SQL code. You can find unused objects inline, while typing SQL code. To find unused parameters and variables, run the Unused parameters and variables command from the ApexSQL Refactor menu:

If there is any declared, but unused variables or parameters, ApexSQL Refactor will find them. To confirm that the highlighted parameter/variable is unused, bring the mouse pointer to it, and the tooltip comes up:

Object name qualifying

The Object name qualifying feature enables you to refactor SQL code in a way to add the owner (schema/user) of objects, object name, or alias name. When object name is qualified, SQL Server will not check if the current user is the owner of the object, which means fewer resources to use. As a result, query will be executed faster. The result of using this option can be easily reverted clicking the Undo in SSMS or Visual Studio. To apply the Object name qualifying option, choose it from the ApexSQL Refactor menu.

Encapsulate code as

Encapsulate SQL code means to use selected code as a database object, and makes it easier to reuse it. ApexSQL Refactor allows you to encapsulate SQL code as stored procedure, view, scalar inline function, or a table inline function. To encapsulate SQL code as one of the mentioned objects, select it in the query window, and choose the Encapsulate code as option from the ApexSQL refactor menu. Select the appropriate object, depending on what do you want to encapsulate, and the new window opens. Give a name and assign a schema to the selected object. If you click the Generate preview button, a SQL script will be created, and the parameters will be listed in the Parameters section. When everything is set, click the Encapsulate button:

Expand wildcards

This will allow you to expand wildcards e.g. “*” used in SQL, into column names. It affects the performance of a SQL query, as SQL Server will not expand them itself, before the query execution. If you type in the query:

SELECT * FROM Person.Address

After applying the Expand wildcard option, the above query will be transformed as follows:

SELECT Address.AddressID,
Address.AddressLine1,
Address.AddressLine2,
Address.City,
Address.StateProvinceID,
Address.PostalCode,
Address.SpatialLocation,
Address.rowguid,
Address.ModifiedDate
FROM Person.Address;

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Free intellisense add-in for SSMS

This article shows how to use ApexSQL Complete, a free SQL Server intellisense add-in. You can download ApexSQL Complete, and play along through the article.

ApexSQL Complete is a free SQL Server Management Studio and Visual Studio add-in that speeds up SQL coding. In this article, we will explain ApexSQL Complete through its features. To start using ApexSQL Complete, enable it from the ApexSQL menu under the main menu in SSMS:

Hint list – complete your SQL code

This is a key feature of ApexSQL Complete. It helps you find the wanted object or a SQL keyword, and complete the SQL statement faster instead of typing the entire object name or keyword. After you start typing, for example “SE”, SQL intellisense will appear with all keywords and database objects that contains “SE”, listed by importance:

You can uncheck the box for the appropriate objects, and prevent them to appear in the SQL intellisense from the add-in options, under the Hints tab. This will decrease the number of hints in the SQL intellisense list, and speed up the coding process:

Another useful property of the SQL intellisense in ApexSQL Complete is a multiple sections, which allow you to navigate over the database schema, to a table, and select the specific columns, checking the appropriate boxes. You can also select the columns and hit the Enter key to insert them:

If a SQL script you are using is large, at some point you will need to look at SQL code before you continue. ApexSQL Complete offers you to accomplish this without a break. Press and hold the CTRL key, and the SQL intellisense will become transparent, so you can see through, and review SQL code. Releasing the CTRL key will get you back to a previous state, and you can continue typing:

Tab navigation – monitors all opened SSMS tabs

This feature allows you to track opened and recently closed tabs, or to restore previously saved session after crashing. These operations can be managed from the add-in options, under the Tab navigation tab. Here you can set the period for keeping the tabs saved, and set the interval for auto-save.

This could be useful if, for any reason SSMS crashes. The Tab navigation feature saves your time, and gets you back to a point before crashing.

The Tab navigation pane consists of two parts, Opened tabs and Recently closed tabs. In the Opened tabs section, all opened tabs from one session will be shown, and you can easily switch from one to another. You can search the content of the opened and closed queries, and open the query that contains searched results. Double-clicking the query from a list of the Recently closed tabs tab opens it in a new query window. For the opened and closed tabs, if you select a query from the list, a complete content will be shown in the preview section to the right:

At any point, you can save your workspace or opened tabs, and restore to the saved state later, if SSMS crashes.

Code structure – view and find SQL code blocks

This feature provides a tree-like form of SQL code presented in a separate SSMS window on the left side of the query window. When you enable the Code structure feature from the add-in options, it allows you to see all the important parts of SQL code used in the query. SQL code blocks from the Code structure window can be expanded so you can navigate to a specific part of the block. Selecting any item from the Code structure window highlights a SQL code block in the query window:

This way you can move through SQL code in blocks and find the part you are looking for instead of scrolling down the query.

Executed queries – track executed queries

Using this feature allows you to track all executed queries in a defined period. To enable the feature, select the Log execute queries option under the Executed queries tab in the add-in options. You can set the folder for storing the executed queries. The queries are saved as an .xml files. You can also define the maximum number of lines in SQL code, which will be stored. The Default period option allows you to show the queries executed in a defined period.

When activated, the Executed queries form will show all the queries executed in a defined time range. If you select a query from the list, its content appears in the preview section. You can search through the queries, executed in a defined period. Double-clicking any of the executed queries from the list opens it in a new query window in SSMS, so you can additionally change SQL code.

Snippets library – insert often SQL statements

With this feature you can insert often used SQL statements, even a whole procedure, or blocks of SQL code. You can create a snippet from the ApexSQL Complete options, or from the SSMS query window:

1) To create a snippet from the ApexSQL Complete options, navigate to the add-in options, and click Add new snippet option, under the Snippets tab:

Here you can edit any of the predefined snippets from the library, export/import them to use it on another machine.

2) To create a snippet from the SSMS query window, type SQL code you want to be defined as a snippet, select it, and right click on it. From the context menu, choose the New snippet option:

This will open the Create a new snippet window, with the selected code already inserted in the Code section. You just need to define a name for the new snippet, and optionally a description:

To use already created snippet from the Snippet library, click the Insert snippet option from the context menu in the SSMS query window, and double click on a snippet from the list to use it in the query.

Navigate to object – locate an object in the Object Explorer

This allows you to locate the selected object in the Object Explorer pane. In the query window, select the object you want to locate, and right-click on it. From the context menu, choose the Navigate to object option, and the selected object will be located and highlighted in the Object Explorer pane to the left.

Test mode – execute queries without impact to the database

The Test mode feature allows you to execute a query in a test environment, without impact and consequences to the database. To use the Test mode feature, select the Test mode option from the toolbar, and highlight SQL code in the query window you want to execute.

The Test mode feature will add BEGIN TRANSACTION and the ROLLBACK TRANSACTION statements. After the execution, it rolls back the transactions at the beginning:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Documentation Tool

This article explains how to use ApexSQL Doc, a SQL documentation tool. You can download ApexSQL Doc, and follow along with this article.

ApexSQL Doc is a SQL Server database development tool, for generating database and SSIS package documentation. It generates documentation in various output formats such as chm, html, doc, and docx. You can include relationship diagrams in a documenting process and customize the preview settings. ApexSQL Doc shows extended properties for all database objects created at the SQL Server level. Using the Extended property editor feature you can manipulate with previously created description or add a new one.

You can choose database objects to include in the documentation, along with the appropriate attributes. Documentation process can be scheduled and automated using the Command Line Interface (CLI). Available output styles can be applied to the output documentation file, or edited additionally.

A new version of ApexSQL Doc 2014 supports SQL Server 2014 and Integration Services 2014. It has a new UI style. Starting from ApexSQL Doc 2014 R2, Windows Azure will be supported, and PDF will be added as a new output format.

Starting the application will open main window where you can start a new project. Clicking the New buttonallows you to add an SQL Server instance, or Integration Services package. You can add multiple SQL Servers as well. To add a SQL Server instance or SSIS package, click the appropriate option marked in the image below:

SQL database documenting

If you choose to connect to SQL Server, the Add SQL Server dialog opens. You can specify local server, or type the address for a remote one, and choose the authentication method. In the Connection options dialog you can increase the values for the Connection timeout and Execution timeout options if you have a slow connection with a remote server.

When a SQL Server is connected, a list of the databases will be shown in the Databases section, where you can select the databases to include in the processing. Check the box for the database you want to add, or select multiple databases from a multiple SQL Servers, to document them at once:

ApexSQL Doc allows you to document server objects. For each combination of the selected server objects, the bitwise set number is generated, and can be used in the CLI, as this number represents a specific object combination, so you don’t have to include the objects manually. For the highlighted object type, a list of the object instances is shown in the object filter section, where you can select a particular instance, and include it in the documenting process. The selected combination of the server objects can be saved as an external file, clicking the Export button, and used later in another project:

In the Database options section, you can set additional options related to database documentation. ApexSQL Doc allows you to document system objects, DDL scripts, and highlight SQL syntax in the output document. In this section, you can set the object grouping (by schema or filegroup), sort objects by the owner/schema section, or include the extended properties. In the Table of Contents section, you can include a Dictionary section for a parameter/column names. Empty sections, or nodes with no relevant metadata existing can be included/excluded from the documentation. Including system objects, DDL scripts, or T-SQL highlighting can slow down the process, as this is performance intensive options:

The next step is to select the database objects to be documented. Checking the box for the appropriate object will include it in the documentation and at the same time will show the list of the object attributes, which you can choose to document. The same as for server objects, the bitwise set number is generated for each combination of the selected database objects, and can be used in the CLI, or the selection can be saved as an external file, and used later in another project:

Server and database objects can be filtered using the Filter editor feature. To enable filtering for the specific object, select the appropriate Use filter checkbox, and the list of the objects will come up under the Object filter tab. Here you can choose the specific objects to document, selecting the related checkbox.

For advanced filtering, click the “…” button, and the Filter editor dialog will open. Here you can specify the filtering conditions to apply to a selected object. Depending on the selected criteria, the objects that satisfy the logical conditions will be included or excluded from the documenting:

ApexSQL Doc allows you to include graphical dependencies in the documentation, and to customize the dependency type and layout settings. Documenting graphical dependencies requires more hardware resources, and can slow down the documenting process. Changing the value of the Scale option will increase/decrease a scale of the graphical dependencies in the output document. This will allow you to improve a visibility of the graphical dependencies in the output document. Object dependencies can be presented as tables, checking the Include dependency list/tables option:

Under the Styles tab, you can select the style to apply to the output document. Choose one of the predefined styles, or edit any of them manually to suit your needs. For a full preview of the output styles, the JavaScript option needs to be enabled for the current Internet zone. Any changes you made will be shown in the Preview section:

Before you generate the documentation, you can edit database extended properties using the Extended property editor. ApexSQL Doc allows you to review any object description added at the SQL Server level, or to add a new description. To add a description click the “…” button, and the popup window will open. Adding the description in the Extended property editor saves it automatically in the database.

Once you have everything set, you can generate the documentation, clicking the Generate button from the Home tab:

SSIS package documenting

ApexSQL Doc allows you to document online (from SQL Server, or Integration Services) and offline (from the file system) SSIS packages. To add a package, under the Integration services tab, click the Add button. When the dialog opens, choose the appropriate package source. New version of ApexSQL Doc supports SSIS 2014. You can include multiple packages from multiple sources, and document them at once:

When the SSIS packages are added, from the Package details tab choose the objects you want to document. You can include package source (*.dtsx) file as well:

All output options (format, style) apply to SSIS package documenting as for the databases. When the SSIS packages documenting is set, click the Generate documentation button from the Home tab.

ApexSQL Doc allows you to automate the documentation process, using the Command Line Interface, and schedule it using SQL Server Job, or Windows Task Scheduler.

Output files

Using the Save as option in MS Word, you can save the documentation file as a PDF file. Currently available output formats are chm, html, doc, docx (pdf through docx).

Here are the examples of the generated documentation:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Server Data Compare Tool

This article shows how to use ApexSQL Data Diff, a SQL data compare tool. You can download it here, install, and play along.

At the beginning, let me say something about this database software.

ApexSQL Data Diff is a SQL Server development tool that is used to compare and analyze data stored in tables, or views, between SQL databases or their backups, and for simplifying data migrations between databases. It can present a detailed view of differences between objects and encrypted objects, and also a row level difference for each object.

When SQL Server data comparison is complete, you can automatically synchronize specified objects between databases, or select a specific row for synchronization. You can isolate the differences that are important with advanced filtering of objects and rows.

The new ApexSQL Data Diff 2014 comes along with SQL Server 2014 support and the new UI style. In the ApexSQL Data Diff Community edition (free), the user can compare and synchronize data in SQL Server Express and Windows Azure SQL databases.

Right after the program is started, a Project management dialog shows up. You can use it to create a new project, or open the previously saved one. If you select New, the New Project dialog opens.

The first option in the Project dialog window is for selecting a source and a destination data source type. You can select two data types from a list: Backup and Database. Also, this dialog offers an option to revert a source and a destination, and to clone information between them.

If you select Database as a data source type, the details you need to provide are login information, server name, and select a database from the drop down list of all available databases on the specified server. If you select Backup as a data source type, you need to provide a path to a backup file.

Beside the regular options, if you click on the Show advanced tabs button, three more tabs will appear: Object filter, Object mapping, and Additional scripts.

In the Object filter tab, there are various options to filter objects and views.

The first option is to edit the Comparison key, which uniquely identifies each single row in a table, or a view, for both the source and destination data sources. Click on the arrow in the Comparison key cell of the selected object, the drop-down menu will open, and you can select the Edit custom comparison key option.

The new window will appear with a list of the selectable comparison keys. By clicking on the check box on the left side of the Column name, you can check/uncheck all of the keys, or to select only the keys which are significant for SQL Server data compare.

This option can help you in a situation when the comparison process shows incomparable objects, because there are no unique indexes or primary keys, and when additional information is required.

You can also select which columns will be included for comparison process. With a click on the arrow in the Columns cell, for the selected object, a drop-down menu will appear.

There, you can select which columns you want to include in the comparison process. In this example, only one column is unchecked, and this will automatically change the number in the Columns cell by reducing the number to one.

You can use the Row filter option to enter the conditions for SQL Server data compare to filter the rows that meet given condition. In the following example, we’ll select the row with the object SalesTerritory, then select the Row filter cell, and click on the three dots button. This will open the Row filter dialog for SallesTerritory, in which you can enter any T-SQL condition.

Click on the Refresh row count button, at the bottom of this dialog, to get the Row count, which is the number of the rows thatmatch previously mentionedconditions.

The last option in object filter is the Filtereditor for the listed objects. By clicking on the Edit filter button, in the top right corner of the object list, a new dialog opens, and you can select custom filter criteria for the tables, or the views. You can include/exclude some of the objects for comparison. With the Edit filter option, you have the ability to specify custom conditions to exclude the irrelevant tables from SQL Server data compare.

Object mapping is used for a custom mapping of tables and indexed views with different names and schemas. Tables and indexed views with the same names and schemas are automatically mapped. You can perform custom table/column mapping. There are two cases for performing the custom table/column mapping:

1) To exclude automatically mapped tables/columns from the comparison

2) To include differently named SQL Server tables/columns with the same schema names

Finally, the Additional scripts tab allows you to use scripts before and/or after the synchronization script is run. You can choose not to use script at all, to use an embedded script, or to use a saved script.

At the end, you can save your selections to a project and use it again. To run this saved project, select it from the Project management dialog and it will load all previously saved settings.

When all options are set, you can click the Compare button. After comparing is done, compared objects are shown in the Main grid. There are several columns in the Main grid: Schema, Object type, Total rows, and difference information which contains: Missing, Identical, Different, and Additional records. Any of those columns can be used for sorting the results, just by clicking on them.

All compared objects are shown, along with basic information and a number of differences between compared objects. Furthermore, the row level differences are shown in the difference details pane, from which you can choose specific rows for the synchronization.

To display certain objects in the Main grid you can use the inline Filter row option. To enable it, go to the View tab, and select the Filter row option. This option is used for filtering compared objects based on different information, object name, total rows, or schema, by entering appropriate condition.

Working with the results grid can be easier by using some of the options, which are located in the View tab, under the Grid group.

There are buttons for grouping the comparison results. The results can be grouped by an object type (tables, views, procedures, etc.). The Collapse group and the Expand group options allow you to automatically collapse, or expand, all selected groups in the Main grid. The Multi column sort option provides more sorting options and you may select up to four columns by which the results are going to be sorted. The last button for adjusting the way the results are shown is Auto size columns, which is used to auto adjust the width of the columns.

The Difference details pane provides viewing and selecting row level differences for synchronization. It’s located beneath the Main grid. If you want to choose some specific rows for synchronization, you have to select the checkbox for those rows.

One of the options that difference details pane provides for users is to view only the rows with specific difference type, by selecting the tabs at the bottom of difference details pane. Those different types can be: Missing types – when records are in the source, but not in the destination database; Identical types – when records are in both the source and the destination database; Different types – when records are in both the source and the destination database, but are not identical; Additional types – when records are not in the source, but are in the destination database.

You can select desired objects, from the Main grid, or you can select all objects by going to the Home tab, and click the Check all button.

After all desired objects are selected, the synchronization process can be started with a click on the Synchronize button, from the Home tab. The Synchronization wizard will appear with the first step, where the source and destination data source are shown, along with the synchronization direction, which can also be reversed.

In the next step, you will be prompted to select the output options. You may choose to Create a synchronization script, or Synchronize a database.

One of the options is to save the synchronization script. The other option is to open the script in an editor and you may choose to open it with the Internal editor, or to assign the other one.

The final step is the Summary and warnings window, where you can see all actions that are going to be performed, along with the possible problems that might occur during the synchronization. You may group the actions by the execution order, by an object, or by the action type.

Finally, select the Create script button, and after you reviewed it, you may execute it.

In ApexSQL Data Diff, you can create reports based on obtained differences, by selecting the Export option from the Home tab. You can export results in three ways: as an HTML plain report, an HTML report, and a CSV data export.

An HTML plain report, which has a simple structure, without user interaction, except when SQL scripts are included in the report. In this report you can see which data comparison options were checked/unchecked, which report options are selected, and data comparison results with all needed information. An HTML plain report is preferred for printing.

An HTML report is similar to an HTML plain report, by its look and presented information, but it also has expand/collapse region with data comparison results. In this way you can easily expand, or collapse, information that you want to see. This report is preferred for browsing.

The last of them is CSV data export, which can generate results into an XML file, for further detailed analysis. An XML file allows using the same document for different systems and platforms, where results can be further analyzed/used in different programs to inspect differences or creating some new report for objects.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL Server Schema Compare Tool

In this article, I’ll show how to use ApexSQL Diff, a SQL compare tool. You can download it here, install, and play along while reading this blog post.

But first, let’s say something about this database software.

ApexSQL Diff is a SQL Server tool that is used to analyze and compare object schemas between two SQL databases, and also to synchronize particular, or all differences between them. It is used for SQL Server schema compare and synchronize the following data types:

  • Databases
  • Backups
  • Script folders
  • Snapshots
  • Source control

It can also be used to repair or roll back changes from backup files, which can be very useful if you made some unwanted changes.

The new ApexSQL Diff 2014 comes along with SQL Server 2014 support and the new user interface style. In the free, Community edition, the user can compare and synchronize SQL Server Express and Windows Azure SQL databases.

Right after the program is started, there is a Project management dialog, where you create a new project, or open the previously saved one. If you select New, the New Project dialog opens.

The first option in this dialog window is for selecting source and destination data source types. You can select data types from a list: Backup, Database, Script Folder, Snapshot, and Source Control. The Project dialog offers an option to revert a source and a destination, and to clone information between them. There is also an option to export data source to a snapshot file, a script folder, or to source control.

When you select Database as data source type, the information you need to provide are server name, login information, and select a database from the drop down list of all available databases on the specified server.

If you select Backup or Snapshot as a data source type, then you need to provide a path to a file.

By choosing the Script folder option, apart from the script folder location, you have an option to Configure mappings, to set a default Collation, and a SQL Server version.

When you select the Source control option as a data source type, there are Source control settings which are in wizard form, and will guide you through setting up Source control type, login information, and configuring mapping. There are also options to set the Default collation and the SQL Server version

Apart from the regular options, if you click on the Show advanced tabs button, three more tabs will appear: Schema mapping, Object filter, and Additional scripts.

In the Schema mapping tab you can specify how owners/schemas are handled during the SQL Server schema compareprocess, by custom mapping schemas in a source and a destination database. You can also export/import mappings to save/load previously defined/exported mappings. Owners/schemas with the same name will be automatically mapped to each other.

To select specific objects for a database comparison, you can use the Object filter option. Here, all object types are shown in a detailed list, so you can choose the ones you want to include in the SQL Server schema compare process.

By choosing each object, it will appear in the first column of the Object filter tab, and clicking on it, a new window will show all objects of that type, which you may choose to include/exclude from the SQL Server schema compare process.

Lastbutnot least, the Additional scripts tab allows you to use scripts before and/or after the synchronization script is run. You can choose not to use it, to use an embedded script, or to use a saved script.

At the end, you can save the selections to a project and use it again, and save time to make the selections again. To run a saved project, select it from the Project management dialog and it will load all previously saved settings.

When everything is set up, click the Compare button. The comparison results will be shown in the Main grid. There are several columns in the Main grid: Object type, Source schema, Source object, Destination schema, and Destination object. Any of those columns can be used for sorting the results, just by clicking on them.

In the View menu, there is the Multi column sort option for more advanced sorting. It allows you more sorting options and you may select up to four columns by which the results are going to be sorted.

There are several other buttons, in the View menu, which can be used to adjust the way the results are shown. You can set an alignment to left, or centered. There are buttons for grouping the comparison results. The results can be grouped by a different type (not equal, missing, added, or equal), by an object type (tables, views, procedures, etc.), or no grouping at all.  There are two more buttons, Collapse group and Expand group, which allow the user to automatically collapse of expand all selected groups in the Result grid. The last button for adjusting the way the results are shown is Auto size columns, which is used to auto adjust the width of the columns.

When you select an object from the Main grid, differences between the object scripts can be seen in the Script difference pane. This pane is divided on two sides. The source script is shown on the left side, and the destination script is shown on the right side. Both scripts are scrolled together, regardless on which side you’re scrolling, so you can easily see the differences in code between them. Different lines of the scripts are highlighted, so they stand out from the rest of code, which makes them easier to find.

You can access to several options in the Script difference pane from the context menu. By default, the Enable syntax option is selected. It shows all SQL reserved words in a different color. The View whitespace option shows all formatting symbols used in the code, a new line, tab, etc. The Line numbers option is used to quickly find a certain line of code. Finally, there is an option to select different font sizes for displaying code in this pane. The default setting is Medium, but it can be changed to Large or Small.

On the far left side of the pane, there is a bar (marked red in the picture) with blue lines that represent positions in the script where the differences are.

If you want to limit the SQL Server schema compare to the specific objects in a database, you can use the Object filter feature. Select the wanted SQL object, and an icon of the Object filter will appear. By clicking on the icon, a new dialog opens, and you can select a custom filter criteria for that object. You can include/exclude some of the objects in the SQL Server schema compare. With the Filter editor option, you have the ability to specify custom conditions to include the relevant tables:

After you have selected all desired objects, you may start the synchronization process with the click on the Synchronize button. The Synchronization wizard will appear with the first step of synchronization direction, where the source and destination data sources are presented, with an option to reverse the direction of the synchronization.

The next step will show you all dependencies for the selected objects, with an option to include or exclude them from the synchronization.

In the Summary and warnings window you’ll see all actions that are going to be performed. You may group them by the execution order, by an object, or by the action type.

In the final step, you are asked to select the output options. You may choose to Create synchronization script, or to Synchronize now.

If you select the Create synchronization script option, there are options to save the script to a file, where you can specify a folder and a file name, and to open the script in an editor. You can use the internal script editor, which is a default option, or assign another one.

Choosing the Synchronize now option, will also give you the option to save the synchronization script to a file, and to create a backup of a database before the synchronization. For creating a backup, you can choose the default directory, or select some other folder.

After the script is created and reviewed, you may execute it.

ApexSQL Diff provides several options for creating reports.

Under the Home tab, there is an Export button, where you will find all options for creating reports. You can export comparison results as an HTML plain report, an HTML report, and an XML structure report.

An HTML plain report is ideal for printing, while a HTML report is designed for electronic browsing since it contains toggles that expand and collapse groups.

After you select one of these reports, it will be generated and presented.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Recover SQL Database Data Deleted by Accident

In Repair a SQL Server database using a transaction log explorer, I showed how to use ApexSQL Log, a SQL Server transaction log viewer, to recover a SQL Server database after a disaster. In this blog, I’ll show you how to use another SQL Server disaster recovery tool from ApexSQL in a situation when data is accidentally deleted. You can download ApexSQL Recover here, install, and play along.

With a good SQL Server disaster recovery strategy, data recovery is not a problem. You have a reliable full database backup with valid data, a full database backup and subsequent differential database backups, or a full database backup and a chain of transaction log backups. But not all situations are ideal. Here we’ll address some sub-optimal scenarios, where you can still successfully recover data.

If you have only a full database backup

This is the least optimal SQL Server disaster recovery strategy, as it doesn’t ensure minimal data loss.

For example, data was deleted on Wednesday. Your last full database backup was created on Sunday, three days before the records were deleted. By using the full database backup created on Sunday, you will be able to recover SQL database records that existed in the table on Sunday. If there were any records inserted into the table on Monday or Tuesday, they will be lost forever. The same goes for records modified in this period. This method will not bring back modified records, only the old records that existed on Sunday.

If you restore this full database backup, all your changes (intentional and accidental) will be lost and the database will be reverted to the state it had on Sunday. What you have to do is compare the records that were in the table on Sunday to the records on Wednesday, create a synchronization script, and execute it against the Wednesday database.

If you have a full database backup followed by differential database backups

Let’s say the situation is the same as in the example above, only you create a differential database backup every night.

Use the full database backup created on Sunday, and the last differential database backup (created on Tuesday). In this scenario, you will lose only the data inserted and updated after the differential backup created on Tuesday.

If you have a full database backup and a chain of transaction log backups

This is the SQL Server disaster recovery strategy that provides minimal data loss. With a full chain of transaction logs, you can recover the SQL database to an exact point in time.

To provide optimal results, you have to know exactly when the records were deleted, because restoring to a later point will not bring back the records.

This method requires restoring the full database backup first. If you have any differential log backup created after the last full database backup, restore the most recent one. Then, restore transaction log backups, one by one, it the order they were created starting with the first created after the restored differential database backup.

Now, the table will be in the state before the records were deleted. You have to identify the deleted records, script them and run the script against the original database.

Although this method is reliable, it is time-consuming and requires a lot of space on disk.

How to easily recover deleted records?

The following solution enables you to recover SQL database records even if you have no full or differential database backups and no transaction log backups.

To understand how ApexSQL Recover works, I’ll explain what happens when table data is deleted.

Table data is stored in data pages. When you delete table records, they are not immediately deleted from the data pages, but marked to be overwritten by new records. Such records are not shown as existing anymore, but ApexSQL Recover can read them and create undo script for them.

How long will deleted records stay in the MDF file? It depends on many factors, as time passes it’s less likely that the records will not be overwritten. The more transactions occur after the deletion, the more chances the records will be overwritten and permanently lost.

Therefore, it’s recommended to create a copy of the database MDF and LDF files immediately (if you cannot take your database offline until the issue is solved) and run ApexSQL Recover on them. Note that a full database backup will not help here, as the records marked for overwriting are not included in the backup.

First, I’ll delete some records from the Person.EmailAddress table in the AdventureWorks database.

 

I can delete these records in SQL Server Management Studio, or execute a script such as

DELETE FROM Person.EmailAddress
WHERE BusinessEntityID BETWEEN 70 AND 80

Then, I’ll start ApexSQL Recover and select From DELETE operation in the Recovery tab.

 

In the Select the database to recover step, first select the SQL Server instance. If it’s not shown in the drop-down list, click the Server icon right to the Server drop-down list and browse for the SQL Server instance, or type the instance name manually.

Specify the authentication type and select the database in the Database drop-down list.

 

In the next step, you’re prompted to add additional data sources. As this can be a tricky step, especially for new users, ApexSQL Recover offers help via the Help me decide option.

 

The Help me decide option guides you through a series of questions about the database transaction log and advises what files to add.

If you know that you have no transaction log backups or detached transaction logs, or the online transaction log file has been truncated after the data was deleted, select No additional transaction logs are available.

If you know that you have transaction log backups that contain the delete transactions you want to recover, click Add transaction logs. The online transaction log is listed and selected automatically.

 

Click Add if to add transaction log backups. It would be best if you have a full transaction log chain, as explained above.

The next step for this option is to specify the time range.

 

Selecting a small time range for the time of deletion will create the recovery script just for the accidentally deleted records. A wide time range might script the records deleted on purpose, and you don’t want that. If needed, you can check the script generated and manually remove such records.

After that, for all data sources options, the next step is to select the tables. Be careful here, if you deleted some data from other tables on purpose, and don’t want to recover them, don’t select all tables, as ApexSQL Recover will create the INSERT script for them too.

 

The next step offers two options: to create a recovery script that will insert the deleted records back into the Person.EmailAddress table, or to create a new database, create the Person.EmailAddress table in it, and insert the deleted records. I’ll select the first one.

 

The recovery process is completed and 11 records are found and scripted, as expected.

 

To see the script, click View script. ApexSQL Recover has its own script editor, where you can review, modify, and execute the recovery script.

The insert into statements look like:

INSERT INTO Person.EmailAddress( BusinessEntityID,
EmailAddressID,
EmailAddress,
rowguid,
ModifiedDate)
VALUES( 70,
70,
N'david2@adventure-works.com' COLLATE SQL_Latin1_General_CP1_CI_AS,
'd62c5b4e-c91f-403f-b630-7b7e0fda70ce',
'20030109 00:00:00.000' );

To execute the script, click Execute in the menu.

 

If you want to check whether the records are really back, execute

SELECT * FROM Person.EmailAddress
WHERE BusinessEntityID BETWEEN 70 AND 80

As shown, ApexSQL Recover recovers SQL database data after accidental deletes even without the database backup that contains the deleted data and relevant transaction log backups. ApexSQL Recover reads the deleted data from the database data file, so this method can be used even for databases in the Simple recovery model.

Besides recovering SQL database records from a DELETE statement, ApexSQL Recover can help when the records are lost due to a DROP TABLE, or TRUNCATE statement, as well as repair a corrupted MDF file that cannot be attached to as SQL Server instance.

You can find more information about how to recover SQL database lost data and repair a SQL Server database on ApexSQL Solution center. There are solutions for various situations when data needs to be recovered.

Reference: Pinal Dave (http://blog.sqlauthority.com)