Implementing Physical Database Structure - Microsoft SQL Server 2012 Performance Tuning - page 335

15. In the query result, examine the new locations of the data files and log files for the
AdventureWorks2012 database; see the following screenshot:
How it works...
In this recipe, we first queried the sys.database_files system catalog view to verify the
current location of the AdventureWorks2012 database. Because we wanted to move the
.mdf and .ldf files to new locations, we had to bring the database offline.
We brought the database offline with the ALTER DATABASE command. Note that, in
the ALTER DATABASE command, we included the ROLLBACK IMMEDIATE option.
This rolls back the transactions that are not completed, and current connections to
AdventureWorks2012 database are closed. After bringing the database offline, we
detached the AdventureWorks2012 database from the instance of SQL server.
You cannot move a database file to a new location if the database is
online. If a database is to be moved, it must not be in use by SQL Server.
In order to move a database, you can either stop the SQL Server service
or bring the database offline. Bringing the database offline is a preferable
option because stopping SQL Server service stops the functioning of the
whole SQL Server instance. Alternatively, you can also select the checkbox
Drop Connections in the Detach Database dialog box, which does not
require bringing a database offline.
We then created two new directories— E:\SQL_Data\ and L:\SQL_Log\ —to place the data
and log files for AdventureWorks2012 and moved AdventureWorks2012_Data.mdf and
AdventureWorks2012_Log.ldf over there. We then attached the AdventureWorks2012
database by attaching the .mdf and .ldf files from their new locations. Finally, we verified
the new location of the database by querying sys.database_files .
You can script your Attach Database and Detach Database actions
by clicking on the Script button in the wizard. This allows you to save
and re-use the script for future purposes.