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 you 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 verify SQL database backups

When disaster strikes, the only thing more frustrating than not having an up to date and relevant database backup is having a corrupt backup. When you first create a backup file, it should be good, with ‘should’ being the operative word. Every time the file is copied to another location, there is a risk of file corruption. A foolproof way to ensure that the file is still usable is to restore the backup itself, and run DBCC CHECKDB immediately against the newly restored SQL database.

April 4, 2013

How to create a SQL build script from the source control repository

It’s quite common for developer teams to use database object versioning. The creation scripts for every table, view, stored procedure, and other objects in the database are added to a source control system. That way, everything is versioned and the team is safe.

Applying a specific version of a source control system to a database is not a problem and it can be done by using a source control client.

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 re-order scripts to avoid dependency-based errors

Having a database scripted to a script folder or to a source control, it enables to use these DDL scripts to recreate it. However, it might not go smoothly due to some missing dependencies.

When creating a database object from scratch, the needed objects are created along the way. The objects can be tied in relationships and constraints to make them dependent on each other.

April 4, 2013

Clean up SQL code from variables you don’t use

Writing and maintaining simple code for stored procedures and functions has never been a problem. However, maintaining complex code, with dozens of parameters and variables, and thousands of rows of SQL statements is. One of the things that the life make easier is to have clean code – without unnecessary and unused parameters and values.

Removing a parameter or variable from code in a production database can cause some problems. They can be easily solved by applying the original object code from a database backup, assuming it can afford the luxury of downtime.

April 4, 2013

How can I make my custom applications self auditing?

SQL developers are increasingly required to include tracking data changes into their custom applications. The information about what change was made – insert, update or delete – who made it, when, and also which computer and application was used, is very useful and can be essential for troubleshooting.

April 4, 2013

Restore only specific data from SQL backups

If there is a need to quickly revert an inadvertent change such as an Update without a Where clause, to recover missing or corrupt data, to move data across development, test, integration and production environments or even just to avoid generating a report using a busy production SQL Server database as a data source, it brings the question if there is a way just to restore specific rows instead of restoring the whole database backup.

April 4, 2013

How to change an object name without breaking your SQL database

Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.

Changing an object name is a simple operation and it could be done easily using the sp_rename system stored procedure or by utilizing the rename and alter … rename statements for indexes and triggers.

April 4, 2013

How to safely delete SQL database objects

When trying to delete a database object, which has an object that depends on it, no problems can be caused, since it will not allow to be deleted:

Drop failed for Table ‘HumanResources.Employee’. (Microsoft.SqlServer.Smo)
Could not drop object ‘HumanResources.Employee’ because it is referenced by a FOREIGN KEY constraint.

April 4, 2013

How to document SQL databases automatically

Many DBAs think of database documentation as “grunt work”. It’s one of those tasks that you never have enough time for, as you’re always busy with other higher priority tasks.

But what if it could be made automatic?

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

How to recover objects that are missing as a result of a SQL injection attack

All of a sudden, you realize that something’s wrong with your database objects. Some stored procedures are missing, functions are there but they don’t work as expected since their code seems to be altered, and some triggers are created and fired which enhances the chaos you’re in.
After the initial shock, you start investigating. Don’t rule out a SQL injection attack, as it’s one of the most common web application security issues.

April 4, 2013

How to recover data that is missing or damaged as a result of a SQL injection attack

What is a SQL injection?

A SQL injection attack is an attack in which a code that attacks the database is inserted into a web site. It’s one of the most common types of web application security vulnerabilities. So it’s better to be prepared, or at least well informed
SQL injections can have an immediate (first-order attacks) and delayed effect (second-order attacks)

April 4, 2013

Checklist for suspicious activity on SQL Server

It seems something went awry with the SQL Server. It’s sluggish, behaves erratically, produces heavy network traffic, there is a significant increase in the server processor or memory utilization, and to top it all there are reports of or database objects and data being damaged or missing.

Where to start looking?

April 4, 2013

How to recover only individual rows from backups

“I’ve mistakenly deleted/updated important records in a SQL Server table. Is there any way to get these records back?”

This is a frequently asked question, and very often a headache for DBAs. If they are careful and make regular database backups, they can recover the lost database data even where the SIMPLE recovery database model is used.

If that is the case, then database backups are the only solution to recover the lost database data. The deleted data has to be from the period prior to the database backup creation.

April 4, 2013

How to recover SQL Server data from accidental UPDATE and DELETE operations

The SQL DBA’s worst nightmare is the loss of data due to their own mistake. It’s not uncommon for someone to improperly execute or even forget a WHERE clause entirely when executing DELETE or UPDATE queries, resulting in potentially millions of rows of compromised data

So, which SQL Server data recovery options are available?

April 4, 2013