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 you want to migrate to an older SQL Server version matches that version, you can’t simply restore the backup. What you probably didn’t expect is that 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 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 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