USE [master]
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
DECLARE @FilesCmdshell TABLE
(
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
DECLARE @DatabaseLocation NVARCHAR(MAX) = 'C:\mnt\Data.SQL2022'
DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
DECLARE @sql NVARCHAR(MAX) = 'master.sys.xp_cmdshell "dir /B ' + @DatabaseLocation + '*.mdf"'
PRINT @sql
INSERT INTO @FilesCmdshell (outputCmd) EXEC (@sql)
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell
OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FilesCmdshellOutputCmd = REPLACE(@FilesCmdshellOutputCmd, '.mdf', '')
SET @sql =
'
CREATE DATABASE ['+@FilesCmdshellOutputCmd+'] ON
( FILENAME = '''+@DatabaseLocation+@FilesCmdshellOutputCmd+'.mdf'' ),
( FILENAME = '''+@DatabaseLocation+@FilesCmdshellOutputCmd+'_Log.ldf'' )
FOR ATTACH
'
print @sql
exec(@sql)
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END