Windows Platform

MS-DOS (CMD) Create FULL SQL BACKUP

This article will show you how to create a full backup of your SQL server. We will use the SQLCMD command. First we will exported the full database list to a csv file. Notice, the sql database backup can only be save locally, you will not be able to save it on a remote server. So after the backup is complete I will compare the old backup on the remote server with the new local backup. We will test file by file. If there any change the files will be transferred to the remote server.

@echo off

set “ServerName=Audain-Server”
set “BackupPath=c:\Temp\SQLBACKUP\”
set “ServerPath=\\Audain_SQLBAckup\”
set “CsvFileName=databaselist.csv”
set “ServerUserName=SQLUSERNAME”
set “ServerUserPassword=SQLPASSWORD”

sqlcmd -s %ServerName% -U %ServerUserName% -P %ServerUserPassword% -Q “exit(SET NOCOUNT ON select db_name(dbid) a from sys.sysaltfiles Where db_name(dbid) IS NOT NULL)” > “%BackupPath%temp_db.csv”

for /f %%a in (%BackupPath%temp_db.csv) do (
findstr /ixc:”%%a” %BackupPath%%CsvFileName% || >> %BackupPath%%CsvFileName% echo.%%a

)

del %BackupPath%temp_db.csv

for /F “skip=2” %%a in (‘Type “%BackupPath%%CsvFileName%”‘) do (

sqlcmd -s %ServerName% -U %ServerUserName% -P %ServerUserPassword% -q “exit(BACKUP DATABASE %%a TO DISK = ‘%BackupPath%%%a.bak’ WITH NOFORMAT, INIT)”
sqlcmd -s %ServerName% -U %ServerUserName% -P %ServerUserPassword% -q “exit(BACKUP LOG %%a TO DISK = ‘%BackupPath%%%a_log.bak’)”

)

fc /b %BackupPath%%%a %ServerPath%%%a

if errorlevel 1 (
echo different
) else (
xcopy %BackupPath%%%a*.* %ServerPath% /s /d /y
)

)