Backup & Restore permissions

1.Backup

--User/Batch id should be db_backupoperator role or 'SysAdmin' role

2.Restore
--User/Batch id should be dbcreator role or 'SysAdmin' role
--Or If User/Batch id have administror privileges on windows server level,,then database can restore without dbcreator role or 'SysAdmin' role.

Database Backup & Restore using Batch scripts

--Backup batch file script
SQLCMD -S SQLInstance1 -E -i "c:\test\Backupquery.sql"

--Backupquery.sql script
--Get Date format to YYYYMMDDHHMM

Declare @BkpFiledate varchar(255)
set @BkpFiledate=CONVERT(VARCHAR(20), GETDATE(), 112) +CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()))+CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112)

--print 'BkpFiledate'+@BkpFiledate
DECLARE @BkpFilePath NVARCHAR(255), @BkpFileName NVARCHAR(255), @BkpFullPath NVARCHAR(255)
SET @BkpFilePath = 'C:\Test\'

print '['+convert(varchar(25),Getdate())+'] '+'Start BACKUP DATABASE TestDB ';
SET @BkpFileName = 'Backup\TestDB_backup_'+@BkpFiledate+'.BAK'
SET @BkpFullPath = @bkpFilePath + @bkpFileName
BACKUP DATABASE TESTDB TO DISK = @BkpFullPath WITH INIT, STATS=10
print '['+convert(varchar(25),Getdate())+'] '+'End BACKUP DATABASE TestDB ';
--------------------------------------------------------------------------------------

--Restore batch file script
set bkpLocation=C:\Test\Backup
set Data1Location=C:\Test\Data1
set Log1Location=C:\Test\LOG1

set DBName=TestDB2
REM Finds the FOR loop for recent backup file on %BackupLocation%\%DBName%\ location
FOR /F %%I IN ('DIR "%BkpLocation%\%DBName%\*.BAK" /B /O:D') DO set RecentBackupFile=%%I

SQLCMD -S SQLInstance2 -E -d master -Q "RESTORE DATABASE %DBName% FROM DISK=N'%BkpLocation%\%DBName%\%RecentBackupFile%' WITH FILE = 1,
MOVE N'%DBName%_Data' TO N'%Data1Location%\%DBName%.mdf',
MOVE N'%DBName%_Log' TO N'%Log1Location%\%DBName%_1.ldf',
REPLACE,STATS=10"