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 in SQL Server Instance (this default instance, can be changed and we will see that in future blog posts). Now, once the database files 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 in SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges come 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 (https://blog.sqlauthority.com)
49 Comments. Leave new
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:OriginalLocationTestDB_log.ldf” “C:NewLocationTestDB_log.ldf”‘
GO
EXEC xp_cmdshell ‘COPY “C:OriginalLocationTestDB.mdf” “C:NewLocationTestDB.mdf”‘
GO
— 3.- ALTER DATABASE MODIFY FILE NAME
— 3.- LOG
ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB_log’, FILENAME = ‘C:NewLocationTestDB_log.ldf’ )
GO
— 3.- DATA
ALTER DATABASE [TestDB] MODIFY FILE ( NAME = ‘TestDB’, FILENAME = ‘C:NewLocationTestDB.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:OriginalLocationTestDB_log.ldf”‘
GO
EXEC xp_cmdshell ‘DEL /Q “C:OriginalLocationTestDB.mdf” ‘
this is very good explanation
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=’\systempathdbname.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
Hie Dave,
Requesting your help..thanx in advance. One of our pc was crashed and windows was not booting up. I have connected the hdd to another pc and copied the database (.mdf & .ldf). Now how will make the .bak files from the downloaded files?
thank’s brother, helpfully
In SQL server, Detach/attach is on database level, can we do it on filegroup or file level? is there anyway that you can detach a filegroup or a physical file, copy it to different database server and install it to different db server?
Hi Pinal,
How we can move the .ldf & .mdf on standby configured with log shipping.
How to move .mdf, .ldf, .tuf fle.
Thanks
Hello Sir,
How i can access database file in c# without using sql server i means i want work on mdf file in c# on window08 without installing sql server 2000,because it does not installed on window08
hi pinal, i have a mdf and ldf files saved in sql server 2012 version and want to use them in sql server 2008 R2. how can i do with import/export utility or restore .pl help
i am getting below error
File activation failure. The physical file name “I:\MSSQL11.MSSQLSERVER\MSSQL\DATA\learner_log.ldf” may be incorrect.
Msg 5170, Level 16, State 1, Line 1
Cannot create file ‘I:\MSSQL11.MSSQLSERVER\MSSQL\DATA\Learner_log.ldf’ because it already exists. Change the file path or the file name, and retry the operation.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Learner’. CREATE DATABASE is aborted.
Thanks so much
I am getting Insufficient memory error in SQL Server 2012 while executing a 363 MB SQL file . Please help me to resolve this issue.
cannot execute script.
Additional Information
Insufficient memory to continue the execution of the program. (mscorlib).
Nick
Hi, how to attach the ldf file file from some other location, on thw same time the ldf will on other location. Ex:- mdf will on C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA and ldf will on d-drive .(folder with any name)
this is very good question, as most of the only need is; to move LDF file from location A to Location B
Hi there,
I have tried this script in SQL 2012. The database was set to Read-only after query was run successfully. And, I have encountered an error when trying to set the DB to Read-Write. Any idea how to bring the DB online?
Thank you for help in advance!
Yvonne
I have fixed the problem myself. The problem was caused by the permission issue. The MSSQLSERVER account needs to be added to the destination folder with allowing full control permission.
Thanks for sharing it Yvonne.
how to add MSSql server account to destination folder?
I find other solution: Use can login in Windows Authentication by: osql -S (servername) -E.
And after you do the same as the article, it will be OK for writing.
Hi ,
It shows access denied . Follow is error occurred
Unable to open the physical file “E:\FlexiPayroll.mdf”. Operating system error 5: “5(Access is denied.)”.
You need to go to file, properties and security tab. Make sure SQL Service account has permissions.