Move SQL Server Database to Different Location :
There are several way to do this but I find this option being simple.Sometimes we face the issue with Drive being full. It happens when we have Database on C Drive and we usually don’t allocate much space on C Drive.Its absolutely bad practice to put any database files on C. By default Microsoft installs on System Drive and we need to be cognizant of this fact while installing and configuring new SQL Server.
Even in Lab, this needs to be taken into account.
EXEC sp_helpdb 'msdb' MSDBData 1 C:\Program Files\Microsoft SQL Server\MSSQL10.DEV2008\MSSQL\DATA\MSDBData.mdf PRIMARY 1416768 KB Unlimited 10% data only MSDBLog 2 C:\Program Files\Microsoft SQL Server\MSSQL10.DEV2008\MSSQL\DATA\MSDBLog.ldf NULL 22144 KB 2147483648 KB 10% log ONLY ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'G:\Microsoft SQL Server\MSSQL10.DEV2008\MSSQL\DATA\MSDBData.mdf' ) ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog , FILENAME = 'G:\Microsoft SQL Server\MSSQL10.DEV2008\MSSQL\DATA\MSDBLog.ldf' ) -- After Moving the Files EXEC sp_helpdb 'msdb' name fileid filename filegroup size maxsize growth usage MSDBData 1 G:\Microsoft SQL Server\MSSQL10.DEV2008\MSSQL\DATA\MSDBData.mdf PRIMARY 1416768 KB Unlimited 10% data only MSDBLog 2 G:\Microsoft SQL Server\MSSQL10.DEV2008\MSSQL\DATA\MSDBLog.ldf NULL 22144 KB 2147483648 KB 10% log ONLY