Backup failed for Server, The media is formatted to support 2 media families Error

sql tsql sql-server-express database-backups
CAD picture CAD · Aug 1, 2014 · Viewed 46.9k times · Source

Can any body tel me how to overcome this error when taking a backup in SQL Server 2008 Express?

Backup failed for Server '\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

System.Data.SqlClient.SqlError: The media loaded on "D:\Project 2014\PayrollBackup" is formatted to support 2 media families, but 1 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&LinkId=20476

I just want to take a single backup of my database. But always I have to give two destinations and then when restoring I cant restore the database using a single backup file? Is there anything to configure in SSMS? If any, could you please clearly explain?

EDIT:

BACKUP DATABASE [Payroll] TO  DISK = N'D:\Project 2014\PayrollBackup' WITH NOFORMAT, NOINIT,  NAME = N'Payroll-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Answer

Dave Mason picture Dave Mason · Aug 1, 2014

The backup statement is using the NOINIT clause. This causes each successive backup to append to the existing backup file. You are attempting to backup to one media set (one file), but it appears the existing backup you are attempting to append to consisted of two media sets (two files).

For a quick turnaround, specify a new filename: BACKUP DATABASE [Payroll] TO DISK = N'D:\Project 2014\<insert new filename>' WITH ...

Related questions

SQL Server after update trigger

I have a problem with this trigger. I would like it to update the requested information only to the row in question (the one I just updated) and not the entire table. CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE] …

Read More
How to insert into a table with just one IDENTITY column (SQL Express)

Pretty much the same as this question. But I can't seem to get this to work with SQL Server Express in Visual Studio 2008. Same Table layout, One column with identity and primary key.

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More