It’s a very common scenario for developer run into “out of space” on C drive because the Model database default location was set at “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA”.
Therefore, there are few occasion that I was in need of moving the datafiles from C drive to my D drive which is more expandable and have lots more space for my huge database. Sure, you can definitely do a backup and then create new database in your D drive and then restore your backup file into that new database! However, what if you just want to get it done quick?
Here are few easy steps for you to move the datafiles from one location to another location; example here is we want to move a database called “test1″:
- Backup your database (just in case anything go wrong, this is extremely important if you have valuable data)
- Make database offline or make sure there’s no active connection on the database you plan to move the data files.
- Run this sp_detach_db script in query management studio:
EXEC sp_detach_db 'test1', 'true'
- Move the datafiles for test1 - “test1.mdf” and “test1_log.ldf” from “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA” to “D:\MSSQL\DATA” folder on file system
- Run this sp_attach_db script:
EXEC sp_attach_db @dbname = N'test1',
@filename1 = N'D:\MSSQL\DATA\test1.mdf',
@filename2 = N'D:\MSSQL\DATA\test1_log.ldf'
- Finish! Now you can use your test1 db just like before!
Keep in mind that you will need access to the database server file system and you will need to know the location of your database and have sysadmin role in the database.
Hope this help!
