Yes it is possible...
I want to call out a few points before offering the solution:
- You must configure permissions so that the appropriate applications have the full control over the directory and files in the destination. This includes:
- SQL Server Service Account or whichever account you are running the SQL Server process under.
- If batch jobs run under a different account, and you perform full transaction log backups, this account must also have permissions.
- Beware of the latency of committing across a remote drive will impact performance
- Remote drives can be bottlenecks if the remote server is using the drive for other purposes (such as page files, or a enterprise file share).
- Performance test this scenario!!
With those concerns out of the way (plus more that were highlighted by @James in the comments).... here is a possible solution.
As a pre-requisite - this example assumes that you have a mapped drive to the
E:\. The access is available to the appropriate SQL accounts.
To quote a referenced site entitled Move SQL Server transaction log files to a different location via TSQL and SSMS:
In a nutshell, the three main steps involved in moving a log file to
new location are:
- Detach the database
- Move log file to new location
- Attach the database by referencing the new location of the transaction log file
Detach script is as follows:
-- Set database to single user mode
ALTER DATABASE adventureWorks
-- Detach the database
Once the detach has completed, move the file manually via whatever means necessary. Then re-attach with the following T-SQL:
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf', -- Data File
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf' -- Transaction Log File