sqlcmd -Q "RESTORE DATABASE <database-name> FROM DISK=<database-backup-file> WITH REPLACE"

Simple Example

sqlcmd -Q "RESTORE DATABASE mydatabase FROM DISK=C:\Backup\mydatabase.bak WITH REPLACE"

Example script which restores a backup file with today date, and then deletes backup files older than 30 days

@echo off
set LOGFILE=SQL-ScriptBackup.log
set DBNAME=cvp_dn_config
set BACKUP-FOLDER=D:\X-Backup



FOR /f "tokens=1-4 delims=/ " %%A IN ('date /t') DO (
set nday=%%A
set nmonth=%%B
set nyear=%%C
)
set SORTDATE=%nyear%%nmonth%%nday%

FOR /f "tokens=1-2 delims=: " %%A IN ('time /t') DO (
set hour=%%A
set min=%%B
)

set SORTTIME=%hour%%min%
set TIMESTAMP=%sortdate%-%sorttime%
echo Timestamp: %TIMESTAMP%



if exist "%BACKUP-FOLDER%" (
goto :start
) else (
md %BACKUP-FOLDER%
)

:start
@echo on

echo ----------------START---------------------------->> %LOGFILE%
echo Date: %date% >> %LOGFILE%
echo Start of Script Time: %time% >> %LOGFILE%



: Take Backup before restore in case, if A side is no longer Master and we need to backout restore
echo ----------------BACKUP--------------------------->> %LOGFILE%
echo. >> %LOGFILE%
echo sqlcmd -Q "BACKUP DATABASE %DBNAME% TO DISK='%BACKUP-FOLDER%\%DBNAME%_B_Side_%SORTDATE%.bak'" >> %LOGFILE%
sqlcmd -Q "BACKUP DATABASE %DBNAME% TO DISK='%BACKUP-FOLDER%\%DBNAME%_B_Side_%SORTDATE%.bak'" >> %LOGFILE%

echo End of Script Time: %time% >> %LOGFILE%



:Restore
echo. >> %LOGFILE%

echo sqlcmd -Q "ALTER DATABASE %DBNAME% SET SINGLE_USER WITH ROLLBACK IMMEDIATE">> %LOGFILE%
sqlcmd -Q "ALTER DATABASE %DBNAME% SET SINGLE_USER WITH ROLLBACK IMMEDIATE">> %LOGFILE%
echo. >> %LOGFILE%

sqlcmd -Q "RESTORE DATABASE %DBNAME% FROM DISK='%BACKUP-FOLDER%\%DBNAME%_%SORTDATE%.bak' WITH REPLACE">> %LOGFILE%
echo ------------------------------------------------->> %LOGFILE%
echo. >> %LOGFILE%


echo sqlcmd -Q "ALTER DATABASE %DBNAME% SET MULTI_USER">> %LOGFILE%
sqlcmd -Q "ALTER DATABASE %DBNAME% SET MULTI_USER">> %LOGFILE%
echo. >> %LOGFILE%


echo forfiles -p %BACKUP-FOLDER% -m %DBNAME%*.bak /d -30 -c "cmd /c del @path" >> %LOGFILE%
forfiles -p %BACKUP-FOLDER% -m %DBNAME%*.bak /d -30 -c "cmd /c del @path" >> %LOGFILE%
echo. >> %LOGFILE%

echo End of Script Time: %time% >> %LOGFILE%
echo ==================END=============================>> %LOGFILE%
@echo OFF