How to Transfer SQL Server Database to a New Location

You can easily move your database created in SQL Server 2012 to a different location using SQL Server Management Studio. You have the choice to copy your database to the same server instance or another server instance or restore it all together to a new directory path. As a prerequisite, you must have administrative privileges to restore a full database backup and no other user must be currently connected to the database. If the database uses a Full or Bulk-logged recovery model, you should create a backup of the active transaction log before starting with the process.

Permissions

If the database being restored already exists, you must be a member of the ‘sysadmin’ and ‘dbcreator’ fixed server roles, or must be the owner (dbo) of the database to have RESTORE permissions. In case the database does not exist, the user must be granted CREATE DATABASE permissions for executing RESTORE.

Restoring SQL Server Database to a New Location Using SQL Server Management Studio

  • Set a connection to the SQL Server Database Engine instance. Navigate to the Object Explorer and click on the server name. A tree appears below the server name.
  • Right-click ‘Database’ in the tree and select ‘Restore Database’. The ‘Restore Database’ dialog box is displayed on the screen.
  • Go to the ‘General’ page and specify the database to be restored and the location of backup sets in the ‘Source’ section. Choose the following options:

Database

Choose the database you need to restore from the drop-down list. The drop-down menu will only list those databases that are included in the ‘msdb’ backup history.

Device

When you click the browse button, the ‘Select backup devices’ dialog box shows up on the screen. Choose any of the listed device types in the ‘Backup media type’ box. Add one or more devices to the ‘Backup media’ box by clicking ‘Add’. After selecting the backup devices, click ‘OK’.
Choose the database to be restored in the ‘Source: Device: Database’ list box.

  • ¬†Once you specify options in the ‘Source’ section, the name of the database being restored automatically shows up in the ‘Database’ box in the ‘Destination’ section. You can choose to rename the database by typing in a new name in this box.
  • The ‘Restore to’ box gives you options for restoring SQL database to a specific point in time. Click ‘Timeline’ to open the ‘Backup Timeline’ dialog box. In this dialog, you can select a point in time up to which you need to restore. You can also choose the default option ‘To the last backup taken’ in the ‘Restore to’ box.
  • The ‘Backup sets to restore’ grid shows all backups present at the location you have specified. Choose the backups you need to restore. You are presented with a recovery plan. You can modify this plan according to your preference by changing selections in the grid.
  • You can choose to specify a new location for the database files by navigating to the ‘Files’ page. Click ‘Relocate all files to folder’. Specify a new location for both the folders: ‘Data file folder’ and ‘Log file folder’.

If you are moving your database from SQL Server 2005 or SQL Server 2008 to SQL Server 2012, it will be automatically upgraded during the restore process.

Leave a Reply

Your email address will not be published. Required fields are marked *