generate restore script
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @dataPath VARCHAR(1000)
DECLARE @logPath VARCHAR(1000)
DECLARE @sql VARCHAR(8000)
— specify database backup directory
SET @path = ‘\yourcomputerpublic’
SET @dataPath = ‘D:SQL Server Data’
SET @logPath = ‘E:SQL Server Log’
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’) — exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = ‘DECLARE @name VARCHAR(1000) ‘ + CHAR(13) + CHAR(10)
+ ‘DECLARE @fileName VARCHAR(1000) ‘ + CHAR(13) + CHAR(10)
+ ‘DECLARE @logName VARCHAR(1000) ‘ + CHAR(13) + CHAR(10)
+ ‘DECLARE @logFileName VARCHAR(1000) ‘ + CHAR(13) + CHAR(10)
+ ‘DECLARE @restoreSQL VARCHAR(1000) ‘ + CHAR(13) + CHAR(10)
+ ‘use [‘+@name+’] ‘ + CHAR(13) + CHAR(10)
+ ‘DECLARE db_cursors CURSOR FOR ‘ + CHAR(13) + CHAR(10)
+ ‘SELECT rtrim(name), right(rtrim(filename), CHARINDEX(“",REVERSE(rtrim(filename)))-1) FROM dbo.sysfiles ‘ + CHAR(13) + CHAR(10)
+ ‘OPEN db_cursors ‘ + CHAR(13) + CHAR(10)
+ ‘FETCH NEXT FROM db_cursors INTO @name, @fileName ‘ + CHAR(13) + CHAR(10)
+ ‘FETCH NEXT FROM db_cursors INTO @logName, @logFileName ‘ + CHAR(13) + CHAR(10)
+ ‘use [master] ‘ + CHAR(13) + CHAR(10)
+ ‘CLOSE db_cursors ‘ + CHAR(13) + CHAR(10)
+ ‘DEALLOCATE db_cursors’ + CHAR(13) + CHAR(10)
–generate restore script
+ ‘set @restoreSQL = “RESTORE DATABASE [‘+@name+’] “‘ + CHAR(13) + CHAR(10)
+ ‘+" FROM DISK = “"‘+@path+@name+’.bak"" “‘ + CHAR(13) + CHAR(10)
+ ‘+" WITH MOVE “""+@name+""" TO “"‘+@dataPath+"‘+@fileName+""", “‘ + CHAR(13) + CHAR(10)
+ ‘+" MOVE “""+@logName+""" TO “"‘+@logPath+"‘+@logFileName+""", “‘ + CHAR(13) + CHAR(10)
+ ‘+" REPLACE “‘ + CHAR(13) + CHAR(10)
+ ‘print @restoreSQL’ + CHAR(13) + CHAR(10)
–print @sql
exec(@sql)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor