It is always good to have database files to be placed on a
non-system partition (system partition is where default program files and
windows folder resides)
Last week, unfortunately my PC got corrupted, had to restore to 3
days old system image of windows, and I lost my sql data. Now for future, I
have taken a preventive measure, and moved my sql database files to non-system
drive.
Here are the steps,
Database name is testDB,
Note: make sure new location is not read only, has
sufficient privileges to Administrator account, and the drive is
uncompressed
Step 1 : Create a new location
New location is D:\MSSQL\DATA\
Step 2 : Run these commands in master database
ALTER DATABASE testDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE testDB SET OFFLINE;
ALTER DATABASE testDB MODIFY FILE
(
Name = testDB,
Filename = 'D:\MSSQL\DATA\testDB.MDF'
);
ALTER DATABASE my MODIFY FILE
(
Name = testDB_log,
Filename ='D:\MSSQL\DATA\testDB_log.LDF'
);
Step 3 : Move original sql data files from C:\program
files\mssql\data to newly created location
Make sure the newly pasted files are not read-only, remove the
read-only or compression settings.
Step 4: Move original sql data files from C:\program
files\mssql\data to newly created location
Bring the database to online mode
ALTER DATABASE testDB SET
ONLINE;
ALTER DATABASE testDB SET MULTI_USER;
