Using Backup and Restore to Transfer Your Data | Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)

What? Use backup and restore as a transfer method? Sure! One of the key advantages of using backups is that they have minimal impact on the operation of your system. A backup is a live operation that allows you to continue conducting business while it is running. There are three types of backups that can be used, each of which is appropriate in different situations. The three types are full database backups, differential backups, and transaction log backups. Detailed information about these backup types and their appropriate uses is given in Chapter 3.

All of the backup strategies use a full backup as the starting point, so you will need to plan for it first. The backup strategy you employ for transferring data will depend on the volatility of your data and the requirements for the target system. If you need the same data in both locations but the data changes frequently, simply using a full database backup will not be adequate. However, if you need to move data that is less volatile and does not change on the target system, then a full database backup may be a great option for you.

When you restore a database, there are a few options you should be aware of. First, you need to know what kind of recovery you are trying to perform. If you are using differential and transaction log backups, you will need to restore the pieces in order. You will use the NO RECOVERY option to let SQL Server know there are more pieces to be restored. Use the RECOVERY option to let SQL Server 2005 know that you have finished restoring the data to the desired point.

Let's look at two of the available backup/restore transfer options. The first option uses only a full database backup. The second option uses a full database backup together with both differential and transaction log backups.

Tip

Keep in mind that using a full database backup with both differential and transaction log backups is similar to using only full backups and transaction log backups with no differential backups. To perform a transfer that uses only full and transaction log backups, follow the steps found in the section titled "Full Backup with Differential and Transaction Log Backups," omitting all parts pertaining to differential backups.

Full Backup Only

There are a couple of ways to set up backups. You can script them yourself or use SQL Server Management Studio. We will show you both techniques.

Note

Keep in mind that if you use SQL Server Management Studio, you can use the Script button to generate the script for later use.

Creating a Backup with SQL Server Management Studio

From the Start Menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.

In the Object Explorer pane, right-click the database you want to back up. In the context menu, select Tasks | Back Up. This will open the following dialog box:

In this dialog box, select the Backup Type of Full. You can also set the Backup Set details here. Refer to SQL Server Books Online for more information on these settings and their use.

Set the Destination properties by clicking the Add...button. This will open the next dialog box:

For transferring data, you typically use a file name destination. You do have the option to set up a Backup Device, but this is most commonly used for tape devices, which aren't typically used when transferring data.

Warning

Be cautious if you choose a UNC path for the backup. This can hurt the performance of the backup and, in SQL Server 2000, it will reduce the overall performance of the server. Be sure to test the performance impact of backing up to the network before implementing it in your production environment.

Note

At this point, you can click the Script button in the Back Up Database dialog box and get the generated backup results in a New Query window. The following script was created in our example and is included in the sample files as FullBackupScript.sql:

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\Backup\AdvWorks20060301.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

Once you have set the path, click the OK button. Then click the OK button in the Backup dialog box. This will create a backup of the database in the specified location.

The next step is to restore the database. In the target server's Object Explorer, right-click the Databases folder and select Restore Database from the context menu. This will open the following dialog box:

Set the Database name in the Restore Database dialog box.

Next, specify the source, as shown below. For this example, select From Device:. Set the path of the backup by clicking the expression builder button and setting the properties in the dialog box. From the Backup Media dropdown list, select File. Click the Add button to specify the path where you created the backup. Click the OK button when you are finished.

Next, select the backup set to restore by checking the box in the Restore column next to the set you created (the file in this case).

10.

Now select the Options page from the Select A Page pane of the Restore Database dialog box, shown below. You will need to set the Restore options. If you are planning to overwrite the database on a regular basis on the target server, then you should select the Overwrite The Existing Database option.

11.

You will need to specify the file paths for the target server database files. If you do not select the Overwrite The Existing Database option, you will need to create a new file name in the Restore The Database Files As: section by clicking the expression builder button. (If you are making a copy of the database, be sure to give the database a different name on the General page of the Restore Database dialog box.)

12.

Finally, select the first option, RESTORE WITH RECOVERY, in the Recovery State section. This option is used with full database restores as it makes the database available for use right away. The other options are used when you will be restoring additional backup files.

Note

At this point, you can click the Script button to script the restore process, which returns SQL similar to the following. This code is included in the sample files as FullRestoreScript.sql.

RESTORE DATABASE [AdventureWorks2] FROM DISK = N'C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\Backup\AdvWorks20060301.bak' WITH FILE = 1, MOVE N'AdventureWorks_Data' TO N'C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\Data\AdventureWorks_Data2.mdf', MOVE N'AdventureWorks_Log' TO N'C:\Program Files\Microsoft SQL Server\ MSSQL.1\MSSQL\Data\AdventureWorks_Log2.ldf', NOUNLOAD, REPLACE, STATS = 10

13.

Click the OK button to finish restoring the database.

Backing Up All User Databases Using T-SQL

While the steps presented for creating a full backup allow you to do this process once, it is not an easily automated process. The following code, included in the sample files as BackupAllUserDBs.sql, will allow you more flexibility when scheduling backups and restores. (At the end of the chapter, we will cover automation and scheduling in greater detail.)

This code will back up all the user databases. Modify the script to best meet your needs. Note that some lines are split due to their length.

[View full width]

declare @DatabaseName varchar(300) ,@BackupSQL varchar(8000) ,@Timestamp varchar(30) ,@DirectoryPath varchar(2000) ,@FullPath varchar(2500) ,@RecoveryModel int set @DirectoryPath = 'D:\MSSQL\BACKUP\' -- create a timestamp for the backup file name set @TimeStamp = convert(varchar, getdate(),112) + replace(convert(varchar, getdate(),108),':','') -- get user databases only declare Database_Cursor cursor for select d.name from sys.databases d where d.name not in('master','tempdb','model','msdb') open Database_Cursor fetch next from Database_Cursor into @DatabaseName while @@fetch_status = 0 begin set @FullPath = '' set @FullPath = @DirectoryPath + @DatabaseName exec sys.xp_create_subdir @FullPath set @BackupSQL = '' set @BackupSQL = @BackupSQL + 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = N''' + @FullPath + '\' + @DatabaseName + '_' + @TimeStamp + '.bak'' WITH NOFORMAT, NOINIT, SKIP' exec (@BackupSQL) -- backups tlogs select @RecoveryModel = d.recovery_model from sys.databases as d where d.name = @DatabaseName -- only backup transaction logs for databases set for

Full Recovery if @RecoveryModel = 1 begin set @BackupSQL = '' set @BackupSQL = @BackupSQL + 'BACKUP LOG ' + @DatabaseName + ' TO DISK = N''' + @FullPath

+ '\' + @DatabaseName + '_' + @TimeStamp + '.trn'' WITH NOFORMAT, NOINIT, SKIP' exec(@BackupSQL) end fetch next from Database_Cursor into @DatabaseName end close Database_Cursor deallocate Database_Cursor

Full Backup with Differential and Transaction Log Backups

When using differential backups or transaction log backups, you need to start with a full backup. Follow the first five steps of the procedure above to create the initial full backup. Do not continue on to the restore process. After creating a full backup, do the following:

Creating a Differential Backup

Repeat steps 1 through 5 above for creating a full backup, but in step 3 set the Backup Type to Differential. A Differential Backup will back up all the changes from the last full database backup.

The next step is to restore the full database backup as described above (steps 6 13), the only difference being that you need to set the Recovery State to RESTORE WITH NORECOVERY (step 12). This will leave the database in an unusable state. In the Object Explorer, you will see that the database is marked with a green arrow and the phrase "(Restoring...)." The database cannot be used until the restoration process is complete.

Next, restore the differential backup to the database by right-clicking the database you are restoring and selecting Tasks | Restore | Database from the context menu. This will open the Restore Database dialog box. Select the differential backup you created and choose RESTORE WITH RECOVERY. Click the OK button and the database will be ready for use once the restore is complete.

Creating a Transaction Log Backup

Once again you will need to start with a full database backup as described above. You can also use differential backups if you choose. Be sure that your database recovery model is set to FULL. For more information on recovery models, see Chapter 3.

Tip

You can only use transaction backups if your database is using the full or bulk-logged recovery models. The simple recovery model regularly truncates the transaction log and therefore the transaction log cannot be backed up.

Once again, you will open the Backup Database dialog box, as in step 2 under Creating a Backup with SQL Server Management Studio earlier in the chapter. This time, change the Backup Type to Transaction Log. The rest of the settings are as before.

Tip

It is common practice to use the .bak extension for full and differential backups and the .trn extension for the transaction log backups.

Normally, you will have more than one transaction log to restore. These logs are referred to collectively as a "log chain." So for this example, we will create a second transaction log backup. Follow the procedures above, but make sure to name the second backup clearly so that you know it is the second transaction log backup in the log chain.

Next, restore the full backup. As with the differential backup restore, you will need to set the recovery state to RESTORE WITH NORECOVERY.

Now restore the first of the two transaction log backups. This can be done by right-clicking the database that is in the process of restoring and selecting Tasks | Restore | Transaction Log from the context menus. This will open the Restore Transaction Log dialog box, shown here:

As you can see, this dialog box is similar to the normal Restore dialog box. The Restore To setting is an additional option at the bottom of the dialog box. For all of these examples, use the Point In Time option. Select the first transaction log backup in the log chain, setting the Recovery State to RESTORE WITH NORECOVERY. Click the OK button to restore this backup.

Tip

If you have more transaction log backups, continue to restore the logs in the order they were backed up until you reach the last log backup in the log chain. You can also stop restoring at any point in the chain. Once you have restored all the files you want, continue on to the next step.

Now restore the second transaction log backup. This time, set the Recovery State to RESTORE WITH RECOVERY, which will make the database available for use.

Warning

You will not necessarily know what data is stored in a particular backup. You will need to estimate the data coverage based on the time of the backup. Even when using the transaction log backup, you only back up completed transactions.

Tip

There are a number of third-party tools that will compress your backups. This is a great option if you have to move significant amounts of data across a network, in particular, across a wide-area network.