Backup and Restore the database using WITH MOVE option

By using this option you can move a Database to new directory, location or drive. You can use either T-SQL or SQL Server Management Studio (SSMS) to backup and restore the database. When using this option you will be creating a new database at destination server and you can perform this action during online.

To backup the database use below script

BACKUP DATABASE TestDB TO DISK = 'C:\Backup\TestDB.bak'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Copy the Database to the Destination server and if the destination server has same driver and folder structure then you can restore the database using below script.

RESTORE DATABASE TestDB from DISK = 'C:\Backup\TestDB.bak'
WITH RECOVERY,STATS=1
GO

If you want to move the database to new folder or new drive then use “WITH MOVE” with restore command.

RESTORE DATABASE TestDB
FROM DISK='C:\Backup\TestDB.bak'
WITH MOVE 'TestDB' TO 'E:\SQLData\TestDB_Data.mdf',
MOVE 'TestDB_Log' TO 'E:\SQLLog\TestDB_Log.ldf'

You must know the logical filenames of the database in order to use WITH MOVE clause, if you don’t know then use below command to find

RESTORE FILELISTONLY
FROM DISK = 'C:\Backup\TestDB.bak'
GO

Backup and Restore the database using SQL Server Management Studio(SSMS)
You can perform the same using SQL Server Management Studio, follow the below steps

Backup the database using SSMS

1. Select Database that you want to backup, Right click and choose Tasks and Backup option
2. Provide the Backup Set Name (For ex:- TestDB_FullBackup.bak)
3. Select the Destination for Backup to as “Disk” or Tape
4. Select the file or backup device for the backup destination.
5. Click OK

Restore the Database using SSMS

1. Right client on Databases, Select Restore Database
2. Choose “From Device” and Specify the Backup media and its location of your restore
3. Select the backup sets to restore
4. Select or type the name of a new or existing database for your restore option
5. Go To “Options” tab , Modify the path under “Restore As” if you want to MOVE data and log files in different folder or drive
6. Select “Recovery State” as RESTORE WITH NO RECOVERY, If you want to apply additional logs ( Otherwise the default option will be RESTORE WITH RECOVERY)
7. Click on OK

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com