Tag Archives: Backups

Backups of a higher version cannot be restored on a lower version of SQL Server

Posted on by
2

SQL Server has always allowed a database backup of a lower version to be restored on a higher version where as the opposite is not allowed. That is, if you have backup of a higher version, that cannot be restored on a lower version. We shall see this in this blog post.

This is true even for SQL Server 2008 R2 and SQL Server 2008, even though they both are from the same Major Version SQL 10, there is a change in the minor version SQL Server 2008 is 10.0.xxxx, SQL Server 2008 R2 is 10.50.xxxx

First we create a database by using the simple command “Create Database databasename”, in this case the database name is 2008R2db. In the query I included @@version to show what SQL Instance I am creating this database on.. When the query is run, the database is created and also the SQL Server Version Information…

Next I backup the newly created database to C:\2008R2db.bak using backup database command in T-SQL. The below is the screenshot when I ran the backup command.

Next I login to a SQL Server 2008 Instance in this case, which is on the same machine, and open a new query and run the restore database T-SQL command trying to restore from the backup file that was just created on 2008 R2 instance. 

As mentioned earlier, the restore command fails, giving the error details in detail that the database versions do not match and what SQL Server version the backup was taken on..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

SQL Server Database Backup types and why are they required

Posted on by
Reply

Database Backups are very important and there is not much to say why are they important; that is the only way to restore the data back to a point of time. In this blog we shall learn the different database backup types available in SQL Server. There are 3 basic types of backups (there are other types too) in SQL Server which every aspiring SQL Server Database Admin should be aware. They are

  • Full
  • Differential  and
  • Transaction Log

Let us look at more details about these backup types.

Full Backup: Full backup is perhaps the most basic type of the backup in SQL Server and the important too. Full backup contains the complete database, including the data (in the tables) and other database objects such as Indexes, Stored Procs, Triggers etc.. Using this backup, the database can be restored (or recovered) until the point-in-time when the Full backup was completed. The advantage with Full backups is that they are completely independent of any other backups, unlike the other 2 types of backups. Since a Full backup will contain the entire database, the size of the backup will be proportionate to the actual backup size (when the backup is not compressed, a feature released in SQL Server 2008).  The same is true with the amount of time it takes to perform a Full backup, the larger the database, greater the time. Any kind of database restore should always begin with a Full backup. These backups can be taken on a database with any of the recovery model.

Differential Backup: Differential backups contain all database changes made since the last Full backup. A differential backup taken a day after the Full backup will contain all the database changes during that one day. A differential backup taken 2 days after the Full backup will contain all the changes during those 2 days. Due to this, Differential backups can be called as cumulative in nature. A recent differential backup contains the changes that were already backed up by a previous differential backup. As more and more data is modified, the Differential backups size increases. A Differential backup cannot be restored separately, instead it should always be used along with a good Full backup set (that is, the most recent Full backup preceding the Differential backup). Differential backups can be taken on a database with any of the recovery model.

Transaction Log: Transaction Log backup also called as Log backup contains all the changes that have been made since the last Full backup or a previous Log backup. Transaction log backups are opposite to Differential backups, i.e, you need each and every transaction log backup, before a specific point-in-time. Maintaining and protecting all transaction log backups is vital. A single log backup missing in a chain can break the ability to restore the database to the most recent point-in-time. Unlike Full and Differential backups, the benefit of Transaction log backups is that they can help us restore to a specific point in time. Log backups are generally scheduled to run at a meaningful short interval such as every 10 or 15 minutes. Log backups are available for only those database which have the recovery model set to Full or Bulk-Logged. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Verifying SQL Server Database Backups

Posted on by
Reply

We know that data in a database is important and also aware that database backups are required in case of a disaster in order to restore the database to a previous state (before the disaster). As a Database Admin it is crucial to perform regular database backups. One of the important tasks many people might not pay attention with respect to database backups is to verify whether or not the database backup is reliable. What I mean is “whether that particular database backup is good to be restored?“. It would be a real disaster to have a backup which is not good to be used, in case of recovery. In order to be on a safer side, we need to verify the backups that we have taken. There are 2 ways you can verify whether or not a backup is reliable; one way would be to try restoring from the backup and the other way is to try a restore verifyonly. When a restore the database from a backup (procedure 1), you actually need to have sufficient disk space to hold the database being restored, where as using restore verifyonly option, you do not need to have the free disk space for the database. It verifies whether or not the particular database backup is reliable or not (indicating that it is possible to restore the database from that particular database).

Let’s say you stored your database backup on C: Drive as dbbackup.bak, use the following command to verify if that backup is valid.

RESTORE VERIFYONLY FROM DISK = 'C:\dbbackup.bak'

The image below shows screen shot of the scripts used in this example.

It would be a good practice to verify the backup files using this option on a regular basis.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty