In the next sample, I'm demonstrate, how to generate script, that restore all the Dbs in the server.
After execution the script, you will get the required script
CREATE TABLE #t(dbName nvarchar(100),logicalName sysname, physical_name nvarchar(max),[Type] tinyint)
DECLARE @command varchar(max)
SELECT @command = 'USE ?
begin try
if DB_ID()>4 --here you can place more selective conditions
begin
INSERT INTO #t(dbName ,logicalName , physical_name,[Type])
SELECT DB_Name(),name, physical_name,[Type]
FROM sys.master_files
WHERE database_id = DB_ID(DB_Name())
end
end try
begin catch
select Db_name() , error_message()
end catch
'
--print @command
EXEC sp_MSforeachdb @command
DECLARE @DbName VARCHAR(10)
DECLARE @logicalName sysname, @physical_name nvarchar(max), @type tinyint
DECLARE Db_Cursor CURSOR
FOR SELECT DISTINCT dbName FROM #t
ORDER BY dbName
OPEN Db_Cursor
FETCH NEXT FROM Db_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
print 'USE '+@DbName
print ' Go'
PRINT 'RESTORE DATABASE @DbName FROM DISK = N''M:\'+@DbName+'.bak'' WITH FILE = 1, '
DECLARE Db_SubCursor CURSOR
FOR SELECT logicalName , physical_name,[type] FROM #t
WHERE dbName=@DbName
ORDER BY [type],right(physical_name,3)
OPEN Db_SubCursor
FETCH NEXT FROM Db_SubCursor INTO @logicalName,@physical_name,@type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type=0
PRINT 'MOVE N'''+@logicalName+''' TO N''G:\SqlData\'+@logicalName+'.'+right(@physical_name,3)+''', '
else
PRINT 'MOVE N'''+@logicalName+''' TO N''F:\sqllog\'+@logicalName+'.'+right(@physical_name,3)+''', NOUNLOAD, STATS = 10
GO
'
FETCH NEXT FROM Db_SubCursor INTO @logicalName,@physical_name,@type
END
Close Db_SubCursor
DEALLOCATE Db_SubCursor
FETCH NEXT FROM Db_Cursor INTO @DbName
END
Close Db_Cursor
DEALLOCATE Db_Cursor
SELECT dbName FROM #t
ORDER BY dbName
DROP TABLE #t