Sql-server – Microsoft SQL Server 2008 R2 backup error

backupsql server

I'm trying to do a simple backup to an external disk and getting this error:

Backup failed for Server 'Server-name'

Additional information:

System.Data.SqlClient.SqlError: The media loaded on "C:\Program Files\Microsoft SQL.." is formatted to tsupport 1 media families, but 2 media families are expected according to the backup device specification.
(Microsoft.SqlServer.Smo)`

Backup type: Full

Why am I getting this error and why is the message pointing to C:\Program Files.. if the destination file is an external device?

Best Answer

This error means that you "striped" your backup across multiple files, and you're only providing one of the files when you're trying to restore.

If you're backing up using SQL Server Management Studio, make sure that you click "remove" to remove any existing backup destinations before you "add" the file you want to back up to.

Related Solutions

Sql-server – How to restore multiple backups

Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx

Sql-server – How to Backup and Restore Filestream Data

You have to restore the database first with the norecovery switch, then restore the other file group. Then roll the transaction log forward then bring the database online.

This requires a minimum of three RESTORE DATABASE commands to do.

Best Answer

Related Solutions

Sql-server – How to restore multiple backups

Sql-server – How to Backup and Restore Filestream Data

Related Question