Have you ever tried change the default path of your data files and log files?
One scenario you can step into this case is manually failover. You mounted the data drive and log drive, but unfortunately the log drive unable to be mounted as original drive letter. So after you start up the instance. You found all your original databases were under the recovery pending status, because SQL Server can’t find the log file for your user databases. So you need to do something to detach and attach these databases, with a log folder path.
Here is a simple script which will save your wrists and mouse from massive clicks:
:setvar newDataPath “D:\SQL Server\Data”
:setvar newLogPath “E:\SQL Server\Log”
set nocount on
declare @databaseID int
declare @databaseName sysname
declare @dbStmt nvarchar(1024)
declare curOFLDB cursor for
select d.database_id, d.name from sys.databases d
inner join sys.database_recovery_status r
on d.database_id = r.database_id
where r.database_guid is null
open curOFLDB
fetch next from curOFLDB into @databaseID, @databaseName
while @@FETCH_STATUS = 0
begin
set @dbStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ SET OFFLINE;’
select @dbStmt
declare @logicalName sysname
declare @physicalName nvarchar(260)
declare @type tinyint
declare @moveStmt nvarchar(1024)
declare curFiles cursor for
select f.name, f.physical_name, type from sys.databases d
inner join sys.master_files f
on d.database_id = f.database_id
where d.database_id = @databaseID
open curFiles
fetch next from curFiles into @logicalName, @physicalName, @type
while @@FETCH_STATUS = 0
begin
if @type = 0
begin
set @physicalName = RIGHT(@physicalName, CHARINDEX(‘\’, REVERSE(@physicalName)) – 1)
set @moveStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ MODIFY FILE’
set @moveStmt += ‘ (‘
set @moveStmt += ‘ name = ‘ + @logicalName + ‘, ‘
set @moveStmt += ‘ filename = ”$(newDataPath)’ + @physicalName + ””
set @moveStmt += ‘ )’
end
if @type = 1
begin
set @physicalName = RIGHT(@physicalName, CHARINDEX(‘\’, REVERSE(@physicalName)) – 1)
set @moveStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ MODIFY FILE’
set @moveStmt += ‘ (‘
set @moveStmt += ‘ NAME = ‘ + @logicalName + ‘, ‘
set @moveStmt += ‘ FILENAME = ”$(newLogPath)’ + @physicalName + ””
set @moveStmt += ‘ )’
end
select @moveStmt
fetch next from curFiles into @logicalName, @physicalName, @type
end
close curFiles
deallocate curFiles
set @dbStmt = ‘ALTER DATABASE ‘ + @databaseName + ‘ SET ONLINE;’
select @dbStmt
fetch next from curOFLDB into @databaseID, @databaseName
end
close curOFLDB
deallocate curOFLDB