Restore user db from UNC I need to create a job that restores all user databases from UNC to my SQL Server.


All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.
What is the right way to do this? Build steps to all databases separately?


I need a suggestion on how to build it.
If you can setup a linked server to the source database server you can simply do a restore from the last backup device containing a full database backup by finding the last backup file like this:

Declare @Database sysname = 'a'
Declare @BackupFile NVARCHAR(1024)
select @BackupFile =  bf.physical_device_name from
(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date, 
            bm.physical_device_name   
            FROM SOURCESERVER.master.dbo.sysdatabases AS sd 
          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name 
          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id
          where sd.name = @database
          GROUP BY 
            bm.physical_device_name) bf
Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'
execute SP_EXECUTESQL @RestoreStatement;


Now if you only have access to the unc path but not the linked server then the @backupfile becomes like this:.

Declare @BackupFile NVARCHAR(1024)
DECLARE @cmdline varchar(2000)
DECLARE @ReturnCode nvarchar(max)
CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))
SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\\server\Backup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'
INSERT #tempTable
EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline
select @BackupFile = (select top 1 cmdShellOutput from #tempTable)
DROP TABLE #tempTable