Get and restore latest SQL backup

@echo off
:: Variables
SET instance=localhost
SET user=backup
SET pwd=sekkrit
SET db=northwind

:: Using mapped drive here, should work with unc as well.
SET remotePath=N:
SET localPath=X:\temp\SqlServer

SET localFile=%localPath%\%db%\%db%_latest.bak

SET dbPath=X:\MSSQL\DATA
SET dbmdf=%db%_data
SET dblog=%db%_log
:: ------------------------------------------------------

:: File transfer. Could preferably use robocopy.
if exist "%localFile%" del "%localFile%"

:: Finding latest backup.
FOR /F "delims=|" %%I IN ('DIR "%remotePath%\%db%\*.bak" /B /O:D') DO SET newestFile=%%I
echo %date% %time% - Getting %remotePath%\%db%\%newestFile%

:: Copy latest backup
copy "%remotePath%\%db%\%newestFile%" "%localFile%"

if not exist "%localFile%" goto fail

echo %date% %time% - Copy done

echo.
echo.

:: Restore
echo %date% %time% - Restoring: %localFile% to database: %db%
echo.
echo Setting single_user (%db)
sqlcmd -S %instance% -U %user% -P %pwd% -d master -Q ^
"ALTER DATABASE [%db%] ^
SET SINGLE_USER WITH ^
ROLLBACK AFTER 60"

echo.
ping localhost /n 90 > nul

echo Restoring %db
sqlcmd -S %instance% -U %user% -P %pwd% -d master -Q ^
"RESTORE DATABASE [%db%] ^
FROM  DISK = N'%localFile%' ^
WITH  REPLACE, FILE = 1, ^
MOVE N'%dbmdf%' TO N'%dbPath%\%db%.mdf', ^
MOVE N'%dblog%' TO N'%dbPath%\%db%_log.ldf', ^
NOUNLOAD, STATS = 10"

echo.
echo %date% %time% - Completed restore of database: %db%
ping localhost /n 10 > nul

:end
goto :eof

:fail
echo Fail
goto :eof