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.