How to restore all databases using the LiteSpeed for SQL Server extended stored procedures.
/*Submitted by Mohammed Azam via the LiteSpeed Community.
This script will restore all databases for a sql server instance.I have used it for sql server 2008.
It does better job on local path than network path.If you are using it for a new instance you must restore master database first because it will read all database information from master database.
*/DECLARE@dbname sysname
DECLARE cur CURSOR FORWARD_ONLY KEYSET FOR
SELECT name FROM Master.sys.databases WHEREowner_sid <> 0x01
OPEN cur
FETCH NEXT FROM cur INTO@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
exec master.dbo.xp_restore_automated
@database =@dbname ,
@backuppath = N'C:\Program Files\Backup',--Change it based on backup location
@backupextension = N'bak',
@checksubfolders = 1,
@sourceserver = N'DEV08SQL1',--- your server name here
@sourcedatabase =@dbname,
@backuptype = N'tlog', -- could be full, diff, or tlog (only tlog will restore --full,diff and tlog)
@affinity = 0,
@logging = 0,
@withreplace = 1,
@with = N'STATS = 10'
FETCH NEXT FROM cur INTO@dbname
END
CLOSE cur
DEALLOCATE cur
