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'
WITH FORMAT
, INIT
, SKIP
, 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.
The 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'
WITH RECOVERY
, 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.)
BACKUP DATABASE
command to backup to two locations simultaneously. – Max Vernon yesterday