8

Currently, I've only got one backup file, IMTDB.bak, and it's on the same HDD as the Database itself. I want to increase the redundancy of this DB backup by essentially "copying" it to another disk, but I get an error "Backup failed for Server, The media is formatted to support 2 media families". I think this means that when I created the backup, it's only meant to put the backup on one drive, and I can't retroactively add more. I want to migrate the backup to this other drive (really just copy it over), but I don't want to have to delete the current backup to do it.

What do I do? Am I safe in just copying IMTDB.bak to a folder on the other drive?

  • sure. Using explorer to copy the file from one location to another, is not a problem at all. You may want to look at automating the process, perhaps with Windows Scheduler. – Max Vernon Oct 21 '15 at 21:13
  • Where/how are you seeing this error: "Backup failed for Server, The media is formatted to support 2 media families" – Max Vernon Oct 21 '15 at 21:14
  • When going to Tasks -> Back Up and trying to do "Add" and selecting the disk I have. – ijustlovemath Oct 21 '15 at 21:14
  • Adding another disk there will simply spread the backup over multiple disks, further reducing the reliability of the backup file, since if either of the two disks fails, the backup file will be unusable. Do as @Kin suggests in his answer, and use the T-SQL BACKUP DATABASE command to backup to two locations simultaneously. – Max Vernon Oct 21 '15 at 21:18
12

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.)

  • Thanks! Since my .bak file already has some backups in it, will the mirror that's created automatically include those backups? Or just the most recent one? – ijustlovemath Oct 21 '15 at 21:32
  • I'd suggest moving that file somewhere if you want to keep it since the MIRROR requires you to format the backup file the first time you use it, which will remove whatever backups are in that file. – Max Vernon Oct 21 '15 at 21:41
  • 1
    Thanks again for the detail in your post. Really helpful to a newbie without any other resources! – ijustlovemath Oct 21 '15 at 21:49
  • my pleasure. I cannot stress enough the importance of a tested and reliable backup and restore strategy. I recommended testing restore to some other machine so you have no possibility of clobbering your existing database, which is easy to do when you are learning. – Max Vernon Oct 21 '15 at 21:50
5

You can use backup database .... MIRROR TO DISK = to backup database to an additional location.

Am I safe in just copying IMTDB.bak to a folder on the other drive?

Yes, you can do that once the backup is finished.

Note: Make sure you are using T-SQL as opposed to GUI which has limited backup options exposed.

  • What are the recommended options here? My DB is very simple, only four tables, three of which only have 90,000 rows, the fourth having ~75M. It's also a fixed size; no more data will be added to it. I will only be running queries against it. – ijustlovemath Oct 21 '15 at 21:22
  • 5
    I would suggest you to put your db in read_only mode and just take one last backup with COMPRESSION. Store it in safe location. – Kin Shah Oct 21 '15 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.