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).
- 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.
- Expand the Management from object explorer section and right click Maintenance Plans to create create new maintenance plan.
- Drag and drop the Execute T-SQL Statement Task tool from Toolbox windows to design panel.
- Right click the task to put the below T-SQL statement on it
1234567891011121314151617181920212223242526272829303132333435363738-- To get the last backup file name and path-- Variable declarationDeclare @FileName varChar(255)Declare @BKLocation varchar(255)-- Set file path and database nameset @FileName = nullset @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 tableinsert into #FileList exec xp_dirtree @BKLocation,0,1select * from #filelist-- Get latest fileselect top 1 @FileName = @BKLocation + FileName from #FileList where Filename like '%.bak' order by filename descselect @filename-- Kick off current users/processesUSE masterALTER DATABASE [DatabaseName]SET SINGLE_USER WITH ROLLBACK IMMEDIATE;-- Execute the restoreexec('RESTORE DATABASE [DatabaseName] FROM DISK = ''' + @filename + '''WITH REPLACE, STATS = 10')--Let people/processes back in!ALTER DATABASE [DatabaseName]SET MULTI_USER WITH ROLLBACK IMMEDIATE;GODROP TABLE #FileList - Save the task and run from Jobs under SQL Server Agent
- Make sure the job successfully completed and check the database updates.
Cheers…!
Comments