Auto Restore Script

Hi, Today I am going to share you  most useful script for restoring a database from backup file.

Suppose if your database having more number of data & log files it will be bit tough to restore using with MOVE option. Below script will generate the restoration script.

use [ReportServer]  --current database
go
declare @dbname varchar(100)
declare @filepath varchar(500)
--declare @islightspeed int
set @dbname ='ReportServer'  --databasename
set @filepath ='\\mhcalbq34clu1bu\BACKUP\DAILY_BACKUP\AANGSQL\QNXT_REPORTDB_WA_DIFF_DB.BAK'  --backup file path
--set @islightspeed =1  -- =1 lightspeedscript else SQL script
--if @islightspeed =0
begin
select 'restore database '+@dbname +' from disk ='+''''+@filepath +'''  with'
 union all
select 'move' +''''+name +'''' +' to ' +''''+physical_name + ''',' from sys.database_files
union all
select 'replace,stats=10'
end
-- else
-- begin
-- select 'exec master.dbo.xp_restore_database @database =N'+''''+@dbname +''''+',
--'+ '@filename = N'+''''+@filepath +''','
--union all
--select '@filenumber = 1,
--@with = N''REPLACE'',
--@with = N''STATS = 10'','
--union all
--select  '@with = N''MOVE N''''' + name +'''''' +' To N''''' + physical_name +''''''',' from sys.database_files
--union all
--select '@affinity = 0,
--@logging = 0'
--end