4 techniques for recovering lost tables due to DROP Table operation

An accidentally dropped table may be recovered in several ways. The choice of technique depends on the resources available for the recovery. The first choice is usually a database backup. But, even if the one doesn’t exist, the dropped table may still be recovered (in some cases even quicker than with a backup).

August 15, 2013

How to compare and synchronize databases programmatically

ApexSQL Diff and ApexSQL Data Diff provide a user friendly GUI for SQL comparison and synchronization of database schemas and database objects. They both include the command line interface which provides the exact same options, and which you can use to schedule unattended comparisons and synchronizations

But, what can be done when the features that the GUI and CLI provide are simply not enough? In such cases a more flexible solution is needed and the good news is – there’s a programmable API

April 30, 2013

How to check whether you can safely deploy to production database

The time when source control systems were used only for code development is gone. Today, database versioning is quite common, even in small companies.

As there are many different source control systems, with different features and options, there are no strictly defined rules how to use them. Each company can create its own standards and rules for using a source control system for database versioning, following the best practices and recommendations.

Depending on which source control system is used, and what the usage schema and environment flow are, the goals of using source control systems are safety, easier team-based database development, and deployment automation.

In a multi-environment system, each environment represents a state through which a database must pass, until it reaches its final destination – production. For example, development of new features and fixing of existing issues is carried out in Development, then they are tested in Integration (or Testing) and Production is where your live databases are.

One of the common rules is that all deployments come from source control. In other words, all database changes must go into source control first and then be deployed to production.

Does it always have to be so?

It should, but unfortunately, it isn’t. Despite all the benefits that a source control system brings, the key factor is still human. If there’s no discipline, developers can skip a step or two, ignore a rule, or do something that doesn’t comply with the standards. A developer in a time crunch might have applied a change directly to production, skipping both the development and testing environments, and never adding the change to source control.

The good news is – in a versioned environment, this can be easily fixed. Furthermore, possible problems caused by this undisciplined approach can be prevented by checking the production database before deployment.

No matter how small the change is, and how small the difference between the production and the source control version is – it’s still a difference. Even just changing a column data type from int to e.g. nvarchar(50) in production, makes a difference that can cause headaches when deploying a new version from source control. Next time the new version is deployed to production, either scheduled, or on demand, deployment can fail.

If lucky, all that will happen is that the latest version cannot be deployed. In a worse scenario, even some data can be lost.

How can be made sure there were no direct modifications of your production database?

Before the deployment starts, check whether there are any differences between the production database and the source control system version that was deployed last. Another option is to compare the live database to a full database backup created immediately after the last deployment if they are available.

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects in live databases, full and differential database backups, databases versioned in source control systems, script folders and database snapshots. It generates comprehensive reports for the differences and can automate the synchronization process.

  1. Start ApexSQL Diff
  2. Once the New project window is shown set the following in the Data sources tab:

    • Select Database as a source type
    • Select the production database as the source
    • Select Backup as a destination type
    • Select the full database backup taken immediately after the last deployment as the destination

  3. Click the Compare button
  4. To switch how to view differences, on the View tab, click one of the buttons under the Grouping section. By default, the Group by difference type option is selected:

  5. All objects in the Results grid will be grouped as equal, different, in source or in destination only.
    If there are no differences, all objects will be shown as equal.

    If there are any different objects, check out their differences in the Script difference view panel:

  6. To create a synchronization script, check the objects in the Results grid that should be synchronized
  7. Click the Synchronize button from the Home tab to initiate the Synchronization wizard:

  8. In the first step of the Synchronization wizard, the synchronization direction will be shown, while in the second step, any dependent objects will be automatically checked and added in the synchronization script:

  9. Under the Output options step, leave the Create a synchronization script action and choose the Save script to a file option:

  10. In the last step, review the Summary and warnings and click the Create script button to finish the process:

    Get back to developers, investigate the differences, and decide the best course of action in this specific situation. The generated script can be used in desired sandbox to synchronize the testing database with the production one.

    To avoid deployment problems, caused by changes in production that didn’t come from source control, use SQL server comparison tool ApexSQL Diff. If there are any differences detected in production, it’s not recommended to make any further changes to the production database without testing them first.

April 11, 2013

Create a database script from a backup without restoring it

There are certain situations when creating a build script from a database is necessary – when a specific object (table, view, stored procedure, user, etc.) needs to be recreated or rolled back to a state it had earlier. If an online database doesn’t contain this object anymore or contains a newer version of it, the only places to look at are full database backups. If a database is versioned under source control, an object can be easily found there, but let’s assume that only SQL backups are available.

The most logical solution is to take a full database backup, restore it, and get what is needed and in most cases, this is the best and quickest solution.

This is a perfectly viable solution for small databases where backups take several hundred of MBs. For large databases, besides significant time needed to restore a backup, the lack of space on hard drives might be encountered as well. The time needed for a database restore depends not only on the backup size, but also on the server configuration and current load. The space needed is at least equal to the size of the full database backup.

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects in live databases, backups, snapshots, script folders and source control projects. Although it is most commonly used for comparison and synchronization, its features can be used to achieve some other tasks. Here is a simple trick used for this solution: comparing a data source such as a live database, a database in a source control system, a backup, or a snapshot to a blank destination will actually create a build script that creates SQL objects that exist in the source.

This means that with ApexSQL Diff there is neither the need for additional space on hard drives, nor the need to restore a database backup; at the same time a DDL script can be created for the selected object in the database. If there are any dependent database objects, they can be scripted too and will be created in a proper order.

The process is as following:

  1. Create a new folder. This is the folder where individual build scripts for each scripted object will be saved
  2. Start ApexSQL Diff
  3. Click the New button in the Project management window:

  4. In the Source panel:

    • Select Backup from the Source drop-down list
    • Click the Add button and navigate to the folder where the backup file resides
    • Select the backup and click the Open button
  5. In the Destination panel:

    • Select Script folder from the Destination drop-down list
    • Click the Folder button to navigate to the newly created folder from the 1st step
    • Select the Default collation and SQL Server version for a DDL script that will be created:

  6. For additional object filtering prior to the comparison process, check out article on this link
  7. Click the Compare button in the bottom-right corner of the New project window
  8. The objects read from the database backup are shown in the Results grid. As the destination script folder is empty, all objects are shown as missing:

  9. Check the check-boxes to select the objects that need to be scripted:

  10. On the Home tab, click the Synchronize button:

  11. In the Synchronization direction step of the Synchronization wizard, click the Next button
  12. The Dependencies step shows the objects that the objects selected for synchronization depend on. By default, they are all selected:

    Uncheck the Include dependent database objects checkbox to avoid updating objects other than the ones selected in the Results grid.

  13. In the Output options step, select the Synchronize to script folder as an output action to have a DDL script created in the script folder for each of the selected database objects:

    To create a single deployment script that creates all objects selected in the Results grid, select the Create a synchronization script output action:

  14. In the Summary and warnings step, review the order of the actions that will be made and click the Synchronize or Create script button:

  15. Depending on which output action was chosen, two buttons can be shown in the last step and the following will be done:

    • The Synchronize button – if the Synchronize to script folder action was chosen, separate sub-folder will be created for each object type has, such as Tables, Functions, etc. The database settings are scripted in the DatabaseSettings.xml file, saved in the specified script folder:

    • The Create script button – if the Create a synchronization script action was chosen, the created script will be created and it can be executed directly from the Integrated editor, with a click on the F5 key. If dependent objects are created, there is no need to worry which object will be created first; ApexSQL Diff determines the correct object creation order and generates the script:

      A database backup doesn’t have to be a black box accessible only when it’s restored to a SQL Server. Database comparison and synchronization tool – ApexSQL Diff can read its content without restoring it and thus help out to work with SQL objects stored in it.

April 5, 2013

How to recover a specific dropped object

Even with all precautions taken, mishaps with databases are still a possibility. Although it is good to be prepared for a disaster – the database is in the full recovery model and transaction log and database backups are created regularly, there are still some situations when a problem cannot be solved using an out-of-the-box solution.

One of these issues is restoring a single object. It’s not unusual to delete a SQL database object (e.g. a stored procedure) that is still needed, or modify it when the old version might be required later.

April 4, 2013

Compare SQL Server database schemas automatically

It’s common knowledge that running database changes directly in production, without thorough testing first, should be avoided.

If there are enough resources in the environment, there would be at least one testing and one production SQL Server instance. However, that introduces another challenge. When everything is set up in the test, and runs smoothly and as expected, how can it easily be applied to the production instance?

April 4, 2013

How to migrate a SQL Server database to a newer version of SQL Server

DBAs are well aware that downgrading a SQL Server database cannot be done out of the box. Even when the compatibility level of the database that needs to be migrated to an older SQL Server version matches that version, the backup can’t be simply restored. Upgrading can also be a problem.

April 4, 2013

How can I take nightly snapshots of SQL server database schema without having to use version control?

A developer aware of the importance of managing database changes knows that database versioning is a must. Having a database versioned in source control enables to recreate the database with the same structure it had at a certain point in the past.

The most common solution for database versioning is using a source control system. But it’s not the only possible solution. Database structure versions can be created even without having a source control system.

April 4, 2013

How to restore a SQL Server database backup to an older version of SQL Server

If there was need to restore a SQL Server database backup to an older version of SQL Server there was “greeting” with error messages along the lines of:

Msg 3169, Level 16, State 1, Line 1
Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

or

Msg 3241, Level 16, State 7, Line 1
Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

and the attempted backup restore would fail.

April 4, 2013

How to recover only objects from SQL Server database backups

Every production database requires maintenance and improvements in order to meet the ever-changing demands. The easiest way to test the new requirements, such as new functionalities and improvements, is to try them on a test database.

So, creating the test base from the backup of the production database would be the easiest way… at least that’s what it looks like. But, is this really the case?

April 4, 2013