Transact-SQL Restore Latest Backup File

Here the very basic steps to restore latest SQL backup file on the backup media location using the below Transact-SQL statement (T-SQL Statement).

1. Open SQL management studio and make sure the SQL Server Agent is running.  If its not go to windows services.msc and start SQLSERVERAGENT service or use “net start SQLSERVERAGENT” cmd.
   
2. Expand the Management from object explorer section and right click Maintenance Plans  to create create new maintenance plan.
3. Drag and drop the Execute T-SQL Statement Task tool from Toolbox windows to design panel.
   
4. Right click the task to put the below T-SQL statement on it
   
   
   Transact-SQL Restore DB

   MySQL

   — To get the last backup file name and path — Variable declaration Declare @FileName varChar(255) Declare @BKLocation varchar(255) — Set file path and database name set @FileName = null set @BKLocation = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\’ create table #FileList ( FileName varchar(255), DepthFlag int, FileFlag int ) — Store all files in table insert into #FileList exec xp_dirtree @BKLocation,0,1 select * from #filelist — Get latest file select top 1 @FileName = @BKLocation + FileName from #FileList where Filename like ‘%.bak’ order by filename desc select @filename — Kick off current users/processes USE master ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; — Execute the restore exec(‘ RESTORE DATABASE [DatabaseName] FROM DISK = ”’ + @filename + ”’ WITH REPLACE, STATS = 10′) –Let people/processes back in! ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE; GO DROP TABLE #FileList

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

   — To get the last backup file name and path — Variable declaration Declare @FileName varChar(255) Declare @BKLocation varchar(255)   — Set file path and database name set @FileName = null set @BKLocation = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\’   create table #FileList ( FileName varchar(255), DepthFlag int, FileFlag int )   — Store all files in table insert into #FileList exec xp_dirtree @BKLocation,0,1 select * from #filelist   — Get latest file select top 1 @FileName = @BKLocation + FileName from #FileList where Filename like ‘%.bak‘ order by filename desc select @filename   — Kick off current users/processes USE master ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;   — Execute the restore exec(‘ RESTORE DATABASE [DatabaseName] FROM  DISK = ”‘ + @filename + ‘” WITH  REPLACE,  STATS = 10′)   –Let people/processes back in! ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE; GO DROP TABLE #FileList

5. Save the task and run from Jobs under SQL Server Agent
   
   
6. Make sure the job successfully completed and check the database updates.

Cheers…!