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

How to move SQLServer Database

For a DBA, it is one of the common activities to move the database to new folder or location or drive.

We have couple of options available to move the database in SQL Server.

1. Backup and Restore the database by using WITH MOVE option
2. Detach and Attach database using SQLServer Management Studio
3. Detach and Attach database using Transact-SQL

If you choose any option, I always recommend running DBCC CHECKDB after you move the database to check the database is structurally consistent.

Backup and Restore the database by using WITH MOVE option

Using Backup and Restore the database option you will be creating a new database at destination server and you can perform this action during online. If the database that you are moving is a production database then I advise to stop the application and take the backup.

When you use WITH MOVE option, the destination folder or driver must be available

Do not keep Backups on the same device where database is sitting. In case of device failure you should be able to restore the database using backup. Also keeping on separate drive enhances the I/O performance for both backups and database use.

Detach and Attach database using Server Management Studio or T-SQL 

When you use Detach and attach database you are moving the database instead of copying and you must perform this task during offline only.

When you detach a database, it removes the data and transaction files from the instance. You can attach these files to any other SQL Server instance

You CANNOT detach a database if any of the following is true

1. The database is replicated and published – Before you detach you must disable publishing by running sp_replicationdboption or remove replication by running sp_remove dbreplication.
2. Database snapshot exists– Before you detach you must drop all database snapshots
3. Database is being mirrored – Before you detach remove the Database Mirroring
4. A suspect Database – Before you detach you must put it into emergency mode
5. System Database – You cannot detach a System Database from Instance

When you attach a database, all f MDF and NDF files must be available. 

Make sure that no data files or file groups are in read-only mode. If primary data file being attached is ready only then database engine assumes that database is read-only.

Regards,

Satishbabu Gunukula

http://www.sqlserver-expert.com