Since you are not familiar with the T-SQL
BACKUP DATABASE command, I thought I'd add some details about that.
You probably want to run something along the lines of the following statement through the Windows Task Scheduler service, since you don't have access to SQL Server Agent (I see from your other questions, you use SQL Server Express).
BACKUP DATABASE [xyz]
TO DISK = 'C:\somepath\mybackupfile.bak'
MIRROR TO DISK = 'D:\somepath\myotherbackupfile.bak'
, STATS = 1;
You might want to strongly consider having the
MIRROR TO clause point to some location that is not on your local machine, since if you lose your local machine completely, you may not be able to access either backup file. Specifying a
MIRROR TO clause requires you to specify the
FORMAT keyword in the
WITH clause the first time you run that backup statement.
You can use the name of a Windows Share, such as
\\SomeServer\SQLBackups\MyBackupFile.bak as long as the security permissions on the share allow the Windows Scheduler service access.
WITH FORMAT, INIT part tells SQL Server to overwrite any existing backups that may be in the backup files. You could change this to
WITH NOINIT once you've completed the first mirrored backup if you want multiple backups (i.e. backups from different points in time) saved in those files.
NOSKIP tells SQL Server to not check for backup expiration, among other things.
STATS = 1 will display the output in
1 percent increments. You can change this number to anything you like. I use
1 for very large databases since it gives some indication of progress.
To have this run through the Windows Task Scheduler service, you'll need to save that command (once you have tested it in SQL Server Management Studio) to a file on your disk; let's call it
C:\somefolder\BackupMyDB.sql. You'll then want to add the following command to the Windows Scheduler:
<path to sqlcmd>sqlcmd -S localhost -E -i C:\somefolder\BackupMyDB.sql
You'll want to have that task "run as" you.
Once you've done all that, you want to very seriously consider attempting to restore the backup onto another machine so you understand how to do that. Having backups is only one part of a disaster recovery plan; the arguably more important part is testing that plan.
The restore process would use a command something like:
RESTORE DATABASE [xyz]
FROM DISK = 'D:\somepath\myotherbackupfile.bak'
, STATS = 1;
Be warned, running
RESTORE DATABASE on the machine where your current database resides can overwrite the current database without confirmation or warning, so please ensure you carefully evaluate the name of the database,
[xyz] in my example, and the other options you use. (This actually won't overwrite the existing database unless you add the
REPLACE keyword to the
WITH clause - I just want to emphasize being careful.)