Backup a database from one server and restore the same on the other server?


Well, this is a common question, which is asked frequently on MSDN forums, so think of writing a quick note on how to do the same. The scenario is as follows:

Scenario: Say we have two database servers, ServerA and ServerB. On ServerA, we have a database called DB01. We need to backup DB01 from ServerA and restored it on ServerB. How should we do it?

Solution: We can achieve it in the following ways:

Solution A:

  1. Backup the database DB01 on ServerA.
  2. Copy the backup files from ServerA to ServerB.
  3. Restored the database DB01 on ServerB.

Solution B: Say we have a shared folder on ServerB named Backup. We need to verify that the folder is accessible through the UNC name (\\ServerB\Backup).  The SQL Server Database Engine Service account of ServerA should have both Read and write access on the folder \\ServerB\Backup and the SQL Server Agent Service on ServerB has read access to the shared folder \\ServerB\Backup. By following Solution B, we are reducing one step of copy the backup files from source to destination server as it is done automatically.

So we can execute the following command on ServerA, to initiate a backup of DB01 and copy the same to ServerB.

USE [master]
GO
BACKUP DATABASE [DB01]
    TO DISK = '\\ServerB\Backup\DB01.bak';
GO

Once, the backup is completed, we can initiate a restore of the backup file on ServerB by using the following command:

USE [master]
GO
RESTORE DATABASE [DB01]
    FROM DISK = 'D:\Backup\DB01.bak';
GO

From the above command, we need to understand that the backup is located on the “D:” drive of the destination server, i.e. ServerB. Also, we need to remember that the logical data file and logical log file name of the destination database should be same as of source database.

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s