How to Schedule a Full SQL Server Backup

How to Schedule a Full SQL Server Backup

CHUONG K. NGUYEN – BSc., MCSEx2, MCSAx2, MCP, MCTS, MCITP, CCNA

In this guide we will go through the process of scheduling a Full SQL Server Backup of two databases, namely MYDB1NAME and MYDB2NAME.

Step 1: Create a BAT file called BackupDB.bat and store it in C:\Windows\System32 with the following contents: (Replace D:\DBDropShip with your drive/directory of choice).

if exist D:\DBDropShip\FullBackup_MYDB1NAME.bak del D:\DBDropShip\FullBackup_MYDB1NAME.bak
if exist D:\DBDropShip\FullBackup_MYDB2NAME.bak del D:\DBDropShip\FullBackup_MYDB2NAME.bak
cd "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\"
sqlcmd -S .\ -i "C:\Windows\System32\backup\BackupDB.sql"

Step 2: Also in C:\Windows\System32, create a file called BackupDB.sql with the following contents:

BACKUP DATABASE [Abel] TO DISK = N'D:\DBDropShip\FullBackup_ MYDB1NAME.bak' WITH NOFORMAT, NOINIT, NAME = N'MYDB1NAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [Abel] TO DISK = N'D:\DBDropShip\FullBackup_ MYDB2NAME.bak' WITH NOFORMAT, NOINIT, NAME = N'MYDB2NAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 3: Create an empty directory called D:\DBDropShip and test by launching CMD and run C:\Windows\System32\BackupDB.bat.

Step 4: Open Windows Task Scheduler to configure the schedule necessary for backing up the database. In this scheduler, add the execution of the BackupDB.bat at a scheduled time.