What are the different ways to move User Databases in SQLServer?

What are the different ways to move User Databases in SQLServer?

There are different methods to move user databases from one location to another location

1. Attach/Dettach Method
2. Take Full backup and Restore
3. Alter Database command within same instance
4. Transfer Database Task through SSIS

Out of 4 methods first and fourth methods you can follow to move a user database within same instance or on other instance, whereas 2^nd  is for moving to other instance and 3^rd method is only applicable within same instance

Attach/Dettach:

This is the simple and easy method

1. Connect to SQLInstance through SSMS
2. Click on database which you want to move
3. Right click and select Dettach
4. Move  database files (.MDF an d.LDF) locations to new location
5. Right click on Databases select Attach
6. Here give new .MDF and .LDF location

Take Full backup and Restore:

1. Connect to SQLInstance through SSMS
2. Click on database which you want to move
3. Right click –Tasksà Backup
4. Take full backup
5. Copy the backup to other sql server
6. Restore it

Alter Database command:

1. Run following command

USE master;

GO

ALTER DATABASE ABC

MODIFY FILE (NAME = ABC, FILENAME = 'E:\SQLData\ ABCdata.mdf');

GO

ALTER DATABASE ABC

MODIFY FILE (NAME = ABC, FILENAME = 'F:\SQLLog\ ABC log.ldf');

GO

2.       Copy the files to new location

3.       Restart SQL Services.

Transfer Database Task through SSIS:

The Transfer Database Task is used to move a database to another SQL Server instance or create a copy on the same instance (with different database name).