We have noticed that users sometimes have different questions while working with SQL Server which on the one hand are easy and simple and on the other, they try to avoid asking them. In this post, we are going to answer some of the most common questions.

Is it possible to restore a database backup on another version of SQL Server?

The short answer is yes. You can restore your database backup on a different version of SQL Server, but there is a tiny restriction. SQL Server allows you to restore only upwards, for example, from SQL Server 2000 to 2005 or from 2008 to 2012. Please note, there is no way of restoring a database backup from SQL Server 2008 to 2005. SQL Server works in such a way because every new version of SQL Server has modifications in the binary of the database and its storage. Also, there is no way to jump forward two versions, for example, you cannot restore a database from SQL Server version 2000 to SQL Server version 2012.

Before beginning the restoration process run the “RESTORE HEADERONLY” option to identify the version of SQL Server with which database backup was made.

RESTORE HEADERONLY FROM DISK = 'D:\Full.bak'

You will receive a summary of your database backup where you can find details of the Minor, Major and Build versions of the SQL Server instance.

To avoid incompatibility within the database run “Upgrade Advisor”, a free tool from Microsoft, on the database you are going to move, then make a backup and finally restore it to the new server.

After you restore your database to the new version of SQL Server note that the database will be running in compatibility mode. This means that the database will support only those functions which are available in the previous version of SQL Server. To solve this issue you need to modify the compatibility level of your database with the help of T-SQL. Each version of SQL Server has its own number:

• 90 for SQL Server 2005
• 100 for SQL Server 2008 and SQL Server 2008R2
• 110 for SQL Server 2012

ALTER DATABASE Adventureworks SET COMPATIBILITY_LEVEL = 110

Is it possible to use the “RESTORE” option to copy a database?

Sure, you can use the “RESTORE DATABASE” option to copy your database to another server. Note that you need to specify the correct way for .mdf and .ldf files with the help of the “WITH MOVE” option:

RESTORE DATABASE Adventureworks FROM DISK = 'D:\full.bak' WITH 
MOVE 'Adventureworks' TO 'C:\SQL Server\DATA\Adventureworks.mdf',
MOVE 'Adventureworks_log' TO 'C:\SQL Server\DATA\Adventureworks_log.ldf';

Why should I purchase a tool for making backups when SQL Server has its own backup?

You can make database backups with the help of T-SQL Commands or SSMS and they work extremely well. But you have to bear in mind and try not to forget to do them according to your schedule. This is the first reason to use a third-party tool like SQLBackupAndFTP which will make a backup process automatic and very simple.

Other privileges of using third-party tools are their features. For example, you can add to your automated backups such options as backup compression or encryption. Also, you can check your backups by adding the “CHECKSUM” option to your backups and running “RESTORE VERIFYONLY” after the backup is completed.

Who can change my database backups?

Firstly, you need to know what is it a backup file? A backup file is a page-by-page copy of your database that existed at the moment when the backup was made. It means that there is no direct way to make any changes in the contents of a database backup file.

We recommend using the “CHECKSUM” option when you make database backups. It will make your life easier in the following way: during the restore process SQL Server will read page-by-page your database backup and calculate a checksum. If some values do not match SQL Server will mark such page as corrupted.

I accidentally deleted a table, but I know the log file contains it, is it possible to restore it?

Yes, you can restore your table until the moment the transaction has been committed. Considering this section we have to admit that “TRUNCATE” and “DELETE” operations delete data in different ways. The “TRUNCATE” operation simply deallocates the page storage of the data. The “DELETE” operation removes data through a transaction that removes each row. So, to restore a table you need to apply a point in the time recovery operation. The very first thing that you have to do after you accidentally deleted a table is to immediately make a transaction log backup. Then you need to restore your database to a particular point in time.

What information does database backup contain?

It is well known that every database backup contains data and the entire database’s structure, but that is not an exhaustive list. Each database backup contains the database’s definitions, settings, and users. At this point you may wonder “What about the user’s password, would it be included in the backup?” The answer is “No”. The password is stored with the login but not with users.