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
I am sorry to say Pinal .. The process you explained this is not correct.
AJITESH – I am happy to learn if you can point out to the mistake.
Question : If admin wants to move the database file from one location to other . Their is possibility that other users detached the Database and give it to admin. So now for admin he needs user permission to copy file from the Detach Database ..
So how to get rid of the permission issue…
Which permission? File level? Admin needs to modify that.
i faced error:
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)
Hello Pinal,
I ran into this error:
“Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\PTest_Log.ldf”. Operating system error 5: “5(Access is denied.)”.
File activation failure. The physical file name “G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\PTest_Log.ldf” may be incorrect.
Msg 5181, Level 16, State 5, Line 1
Could not restart database “PTest”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.”
This perhaps mean the SQL service account does not have permission to the new location? I’ve read somewhere that one would have to preserve the permission when copying the file to the new location. Not sure how to do that. Can you help please?
Awesome Pinal, clean and simple.
Hi Pinal,
I have a similar issue to what Phu Huynh explained in his post on May 26, 2016. In December I detached my database, and moved my .mdf and .ldf from the F drive to the D drive on the same virtual machine.. I then re-attached. All is working fine. We did a reboot of the server just last week and the database would not come up. I looked in the Event Viewer and found that SQL Server was still trying to locate the files on the F drive which no longer exists. I’m really baffled on how/why this would happen. Even when restoring from backup, it would error telling me the F drive does not exists. The only way I could recover was to restore using SQL script and actually specify the D drive paths. It seems to me that detach/re-attaching a database isn’t changing a default for the database in the system databases. Any thought on how/why this would be? I use SQL Server 2014 SP2 standard edition.
Thanks. John
That can only happen when SQL configuration was restored to old state (typical master database restore). Any possibility that VM was restored to older state.
TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
——————————
Object reference not set to an instance of an object. (SqlManagerUI)
——————————
BUTTONS:
OK
——————————
Msg 5011, Level 14, State 5, Line 3
User does not have permission to alter database ‘KasaMP’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 40
The database ‘KasaMP’ does not exist. Supply a valid database name. To see available databases, use sys.databases.
Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file “C:UsersSlavenDocumentsvisual studio 2013ProjectsPCKasaKasaMPDatabaseKasaMP.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
OK I ran this piece of code to my original file path and it’s ok now LOL! But I guess I didnt’ have permissions to alter DB o_O??
— Move MDF File from Loc1 to Loc 2
— Re-Attached DB
CREATE DATABASE [KasaMP] ON
( FILENAME = N’C:UsersSlavenKasaMP.mdf’ ),
( FILENAME = N’C:UsersSlavenKasaMP_log.ldf’ )
FOR ATTACH
GO
Msg 5011, Level 14, State 5, Line 3
User does not have permission to alter database ‘KasaMP’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 40
The database ‘KasaMP’ does not exist. Supply a valid database name. To see available databases, use sys.databases.
It seemed like an awesome solution if the code worked :C
UPDATE: Even though I got some errors while runing the code, I right clicked on my DB -> Properties -> Files and I see the new path that I set… now I’m just confused but I guess that’s it – sorry for spaming your post ^ ^
Thanks for sharing the final status.
Pinal, thanks for the post but you are missing a vital step. For each file moved, you need to run the following ALTER statement:
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = ‘new_pathos_file_name’ );
Essential! See here:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017
Hi Pinal, Need to understand if we can move the SSISDB database (MSSQL server) the same way, as this contains the package(s) deployed, will this cause any disturbance with jobs which are scheduled with the packages.
Please suggest
Tried a few other ways, but found this one to be the easiest. Thank you.
Great. I always believe in “Keep It Simple” philosophy.
HI Pinal,
Can you let me know for the below if I can move the user DB data and log file by following below step
DB; MS sequel Server 2014
Old File Loc :C:/
New File Loc : D:/
Db name : customer
Steps:-
Right click on the name of the database (customer)
Select Properties
Go to the Files tab
Make a note of the Path and FileName of MDF and LDF files.
Right click on the database (customer)
Select Tasks -> Detach
Move the files from C: to D:
Right click on the Databases node of your server
Select Attach
Click on the Add button
Point to the new location as D:
Click OK
Please confirm me on the above if I can do this way for my user DB
Yes. That is perfect.