Geeks With Blogs
Jim Lahman's Blog Fortitude|Endurance|Faith|Teamwork

When restoring a database backup, by default, SQL Server places the database files in the master database file directory.  In this example, that location is in L:\MSSQL10.CHTL\MSSQL\DATA as shown by the issuance of sp_helpfile

Default_directory_location_of_master_database

 

Hence, the restored files for the database CHTL_L2_DB are in the same directory

 

Default_location_of_restored__SQL_Server_files_

 

Per SQL Server best practices, the log file should be on its own disk drive so that the database and log file can operate in a sequential manner and perform optimally.

The steps to move the log file is as follows:

  1. Record the location of the database files and the transaction log files
  2. Note the future destination of the transaction log file
  3. Get exclusive access to the database
  4. Detach from the database
  5. Move the log file to the new location
  6. Attach to the database
  7. Verify new location of transaction log

Record the location of the database file

To view the current location of the database files, use the system stored procedure, sp_helpfile

   1:  use chtl_l2_db
   2:  go
   3:   
   4:  sp_helpfile
   5:  go
sp_helpfile_output

 

Note the future destination of the transaction log file

The future destination of the transaction log file will be located in K:\MSSQLLog

Future_location_of_log_file

 

Get exclusive access to the database

To get exclusive access to the database, alter the database access to single_user.  If users are still connected to the database, remove them by using with rollback immediate option.  Note:  If you had a pane connected to the database when the it is placed into single_user mode, then you will be presented with a reconnection dialog box.

   1:  alter database chtl_l2_db
   2:  set single_user with rollback immediate
   3:  go

Detach from the database

 

Now detach from the database so that we can use windows explorer to move the transaction log file

   1:  use master
   2:  go
   3:   
   4:  sp_detach_db 'chtl_l2_db'
   5:  go

 

After copying the transaction log file

After_copying_the_trans_log_file

re-attach to the database

   1:  use master
   2:  go
   3:   
   4:  sp_attach_db 'chtl_l2_db',
   5:  'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB.MDF',
   6:  'K:\MSSQLLog\CHTL_L2_DB_4.LDF',
   7:  'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB_1.NDF',
   8:  'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB_2.NDF',
   9:  'L:\MSSQL10.CHTL\MSSQL\DATA\CHTL_L2_DB_3.NDF'
  10:  GO
Posted on Monday, June 20, 2011 4:52 PM | Back to top


Comments on this post: Move SQL Server transaction log to another disk

# re: Move SQL Server transaction log to another disk
Requesting Gravatar...
Excelente código, continua asi, FELICITACIONES
Left by Mor Tales on Sep 12, 2013 5:52 PM

Your comment:
 (will show your gravatar)


Copyright © Jim Lahman | Powered by: GeeksWithBlogs.net