How to back up and restore SQL server database

In a typical installation scenario, SQL Server stores data in two different files, one with MDF extension and stores the data itself and the other with LDF extension and stores transaction logs. Although you can anyways configure SQL Server to have multiple data files and transaction log files the way you want them to be. But here we are going to cover issues like full database backups, database restores and simple and full recovery modes.

When SQL Server processes a transaction, it performs the steps given below:

1. Writes about the next step in the transaction log.

2. Changes data file that usually refers to the changes made in-memory copy of that portion of the data file.

3. Writes to the log that the transaction is committed.

4. CHECKPOINT process writes the portion data file related with the disk transaction. This can happen anytime after the above step.

5. Writes to the log that the transaction is ‘hardened’.

The easiest way of getting a backup is the Full Backup; right-click on the database in SSMS and select Tasks > Backup for opening the Backup Database window. Now you need to verify at-least three things on this screen, first correct database is selected, second the backup type if set to full and third choose a backup file name. On the Options tab, specify whether SQL Server should replace or add backup to the backup file. Make sure that the backup file is relative to where SQL Server is installed and not where you’re running SSMS.

If you wish to issue a backup statement on your own, then you can utilize SSMS to script it on your behalf. Click the Script button on the top of the dialog box and SSMS will generate the following statement:

BACKUP DATABASE [Systems] TO

DISK = N’\\nas\Backup\L40\SQL2005\Systems_backup_200702120215.bak’

WITH NOFORMAT, NOINIT, NAME = N’Systems-Full Database Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 10

This shows how these options map back to the dialog box. The Backup statement creates a single file with a BAK extension comprising of what is in your data file and log file. This way you can create a backup of data while still working on the SQL Server or using the database. Although, it can be a bit slow.

On the other hand, restoring a database can act a bit complicated; right-click on the database in SSMS brings up a Restore Database dialog box. Select From Device and navigate to the backup file. If you are restoring the file on the same computer and the original database is also there, then don’t select Database radio button but select database only. It will automatically select the backup file. Click the Options tab to switch to the second step.

SQL Server will never let you restore the two file names right on top of the other until you check the “Overwrite the existing database” checkbox above. You would have to edit those filenames to change the name.

One thing to be aware of in SQL Server Recovery Model; if you right-click on database and select Properties > click the Options tab, you’ll see the recovery model on the list. There are two main settings for this which are Simple and Full recovery. In Simple Recovery, SQL Server does not keep transactions in the transaction log that have already been “hardened.” These are automatically removed and the space in the file is reused.

In Full Recovery, SQL Server keeps log of every transaction in the transaction log file till the transaction logs are backed-up. Simple Recovery is ideal for developers or server administrators where data is backed-up every night. Usually, when you create a database, SQL Server copies the “model” database. If you set the Recovery Model of the “model” database to Simple, then the entire database created in future will start out in Simple Recovery mode. This is how you create backup and restore SQL Server database.

Free download demo version of Kernel for SQL Recovery Software.

Comments are closed.