Moving database from one location to another

Hi All,

Recently I ran across a situation where in there was a GIS application which was trying to feed a large amount of data (more than 22GB) to SQL server. This led to the increase of the data file (.mdf) and eventually this led to filling the disk space. So came the situation of moving the database to a different location where there was more room for growth !!

Solution/Approach:
1. using Attach/detach method.
use adventureworks
go

sp_helpdb 'adventureworks' ---> this will show you the logical and physical file names of the data and the log files for a particula database

Detach the database:

USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks';
GO

Move the files data(.mdf), secondary data file(.ndf) and log files (.ldf) to the desired location.

Attach the database:


USE master;
GO
CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')
    FOR ATTACH;
GO

This method has certain drawback and is not recomended by BOL: "We recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach."

So the recomended method is to use ALTER DATABASE.


1. sp_helpdb 'adventureworks' -> will give logical and physical file names

2. use the alter database command to move the files.

ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_dat, FILENAME = 'E:\adventureworks_database\adventureworks.mdf');
ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_dat2, FILENAME = 'D:\adventureworks_data_file2\adventureworks_dat2.ndf'); --- here if you have any secondary data file, if not ignore this
ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_log, FILENAME = 'E:\adventureworks_database\adventureworks.ldf');


ALTER DATABASE adventureworks SET OFFLINE
WITH ROLLBACK IMMEDIATE --- this will take your database immediately offline

Now move the data files and log file to the respective desired location.

ALTER DATABASE adventureworks SET ONLINE -- this will take your database online

This will move your database from one location to another and is more efficient way to do.

This is a good link to follow: http://www.sqlservercentral.com/articles/Administration/65896/

Hope this helps !!

\\K