Daily Database Restore in Report Server

Create a job in Report Server

Job Name : Daily Database_Name Resotre

Step 01(Copy Differential/Restore/Archive):

DECLARE 

@rc int,

@FileName varchar(255),

@statement nvarchar(max),

@return int

 CREATE TABLE ##BackupFiles(

                [FileName] [varchar](255) NULL,

)

 -- Copy Full

 Insert Into ##BackupFiles(FileName)

EXEC @rc = master.dbo.xp_cmdshell 'dir \\SourceServer_Name\g$\MSSQL\100\Backups\Full\Database_Name /B'

 

IF @rc <> 0

BEGIN

RAISERROR('50005 Full Copy Failed',16, -1, @@servername )

END

 Set @FileName = (Select top 1 FileName From ##BackupFiles

Order By FileName Desc)

 Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''copy \\ SourceServer_Name \g$\MSSQL\100\Backups\Full\ Database_Name \' + @FileName + ' E:\PTCRestore\ Database_Name _backup.bak /Y /V'' IF @rc <> 0 BEGIN RAISERROR(''50005 Full Copy Failed'',16, -1, @@servername ) END'

print @statement

EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Full Restore Failed',16, -1, @@servername )

END

 Delete From ##BackupFiles

 -- Copy Differential

 Insert Into ##BackupFiles(FileName)

EXEC @rc = master.dbo.xp_cmdshell 'dir \\ SourceServer_Name \g$\MSSQL\100\Backups\Differential\ Database_Name /B'

 IF @rc <> 0

BEGIN

RAISERROR('50005 Differential Copy Failed',16, -1, @@servername )

END

 Set @FileName = (Select top 1 FileName From ##BackupFiles

Order By FileName Desc)

 

Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''copy \\ SourceServer_Name \g$\MSSQL\100\Backups\Differential\ Database_Name \' + @FileName + ' E:\ Database_Name Restore\' + @FileName + ' /Y /V'' IF @rc <> 0 BEGIN RAISERROR(''50005 Differential Copy Failed'',16, -1, @@servername ) END'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Restore Failed',16, -1, @@servername )

END

 -- Restore/Archive

 exec sp_killusers 'ptc'

 IF (select datename(dw,getdate())) = 'Sunday'

BEGIN

 RESTORE DATABASE [ptc] FROM  DISK = N'E:\ Database_Name Restore\ptc_backup.BAK' WITH  FILE = 1,  MOVE N'PTC_Data' TO N'E:\PTCRestore\ptc.MDF',  MOVE N'PTC_Log' TO N'F:\ Database_Name Restore\ptc.LDF',  NOUNLOAD,REPLACE,  STATS = 10

 

END

ELSE

BEGIN

 RESTORE DATABASE [Database_Name] FROM  DISK = N'E:\ Database_Name Restore\ Database_Name _backup.BAK' WITH  FILE = 1,  MOVE N' Database_Name _Data' TO N'E:\ Database_Name Restore\ptc.MDF',  MOVE N' Database_Name _Log' TO N'F:\ Database_Name Restore\ Database_Name.LDF',  NORECOVERY,  NOUNLOAD,REPLACE,  STATS = 10

 

Set @statement = 'RESTORE DATABASE [Database_Name] FROM  DISK = N''E:\ Database_Name Restore\' + @FileName + ''' WITH  FILE = 1,  MOVE N''PTC_Data'' TO N''E:\ Database_Name Restore\ Database_Name.MDF'',  MOVE N'' Database_Name _Log'' TO N''F:\ Database_Name Restore\ Database_Name.LDF'',  NOUNLOAD, STATS = 10'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Restore Failed',16, -1, @@servername )

END

 END

 Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''move /Y E:\ Database_Name Restore\' + @FileName + ' E:\ Database_Name Restore\Archives'' IF @rc <> 0 BEGIN RAISERROR(''50005 Differential Move Failed'',16, -1, @@servername ) END'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Move Failed',16, -1, @@servername )

END

 Drop Table ##BackupFiles

 ALTER DATABASE [ptc] SET READ_ONLY WITH NO_WAIT

 Step 02(CleanUp TransLog Files):

 EXEC MSDB.dbo.sp_start_job @Job_Name = 'CleanUp Translog.Subplan_1'

 Step 03(CleanUp Differential Files):

EXEC MSDB.dbo.sp_start_job @Job_Name = 'CleanUp Differential.Subplan_1'