Jim Lahman's Blog

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

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

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

Note the future destination of the transaction log file

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

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

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

I scheduled a task to perform a windows backup of a single disk on the my server.  When I tested it, the task ran successfully – no problems, no errors; just as I expected. 

However, when the task ran as scheduled, it failed with error value 2147943645.  I wondered was this the answer to life, the universe and everything in it?  No.  That is 42. 

After doing some research and reviewing the task configuration, I realize that the task will only run if the user of logged on:

So, this was the answer!!  I have to configure the task to run whether the user is logged or not.  Or, else I’ll get that nasty error value.