When a novice DBA or Developer create a database they use SQL Server Management Studio to create new database. Additionally, the T-SQL script to create a database is very easy as well. You can just write CREATE DATABASE DatabaseName and it will create new database for you. The point to remember here is that it will create the database at the default location specified for SQL Server Instance (this default instance can be changed and we will see that in future blog posts). Now, once the database goes in production it will start to grow.
It is not common to keep the Database on the same location where OS is installed. Usually Database files are on SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.
Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.
USE MASTER;
GO
-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'
GO
Now move the files from loc1 to loc2. You can now reattach the files with new locations.
-- Move MDF File from Loc1 to Loc 2
-- Re-Attached DB
CREATE DATABASE [TestDB] ON
( FILENAME = N'F:\loc2\TestDB.mdf' ),
( FILENAME = N'F:\loc2\TestDB_log.ldf' )
FOR ATTACH
GO
Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Hi,
Is the procedure the same if I use database mirroring or replication? If I’m not wrong is not possible to detach db
Regards
it is possible to move tempdb in shard network location?
it’s better to copy files than to move them because if there are trouble with big files, you had to restore the database.
Hi Pinal,
Excellent Blog Congrats.!!!
According to the topic, I prefer to use ALTER DATABASE to indicate new location of the database files. What do you think?
– 0.- MAKE DATABASE BACKUP (IF POSSIBLE)
– 1.-SET DATABASE OFFLINE
ALTER DATABASE [TestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
– 2.- MOVE FILES TO NEW LOCATION
EXEC xp_cmdshell ‘COPY “C:\OriginalLocation\TestDB_log.ldf” “C:\NewLocation\TestDB_log.ldf”‘
GO
EXEC xp_cmdshell ‘COPY “C:\OriginalLocation\TestDB.mdf” “C:\NewLocation\TestDB.mdf”‘
GO
– 3.- ALTER DATABASE MODIFY FILE NAME
– 3.- LOG
ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB_log’, FILENAME = ‘C:\NewLocation\TestDB_log.ldf’ )
GO
– 3.- DATA
ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB’, FILENAME = ‘C:\NewLocation\TestDB.mdf’ )
GO
– 4.- SET DATABASE ONLINE
ALTER DATABASE [TestDB] SET ONLINE
GO
– OPTIONAL
– 5.- CHECK INTEGRITY
DBCC CHECKDB(‘TestDB’)
GO
– 6.- DELETE THE OLD DATABASE FILES
EXEC xp_cmdshell ‘DEL /Q “C:\OriginalLocation\TestDB_log.ldf”‘
GO
EXEC xp_cmdshell ‘DEL /Q “C:\OriginalLocation\TestDB.mdf” ‘
[...] I wrote a blog post about SQL SERVER – Move Database Files MDF and LDF to Another Location and in the blog post we discussed how we can change the location of the MDF and LDF files after [...]
What if i want to create a backup and then want to move it
You can directly specify UNC path
Backup database db_name to disk=’\\system\path\dbname.bak’
Moving a standard datafile is as easy as it is written here but a better article would have included how to move the master database to a new location. Although it isn’t done often it is well worth knowing simply because there are occasions where this might save you a lot of time, effort and downtime.
Hi guys,
This is my situation. I have 68 servers, all local. Now i want these servers to be hosted a single or 2 servers. how do I go about the procedure of transferring the data without making disruptions on the operation on the server.
is it work when database in replication mode