0

Basically, I'm having this issue: http://www.sqlcoffee.com/Troubleshooting047.htm What I'm doing is running a script I found online (http://pastebin.com/3n0ZfybL) to do a full backup, then rar'ing up the file and moving it to my computer. The CRC of the backup file inside the rar is correct on both computers, so there is no problem with data being corrupted when I transfer it. But then I go and try to restore the database on my dev computer here and I get the errors "sql server cannot process this media family" ... "msg 3013". Why is this happening? I'd test out the backup on the server I'm getting it from, but it's a production server.

Edit: I was about to say how I wasn't doing anything stupid like trying to restore the database to an earlier version of SQL Server, but apparently I am:

From: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

To: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

Let me get back to this post after I reinstall this.

Edit: Ok, I removed sql server 2005 and 2008, reinstalled, and now I'm getting the same version as the production server when I select @@version. However:

System.Data.SqlClient.SqlError: The media loaded on "C:\Backup\db.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.

Chris
  • 183
  • 3
  • 3
  • 6

2 Answers2

1

Try restoring with this command just to make sure the backup file isn't corrupted:

RESTORE VERIFYONLY FROM DISK='D:\yourbackupfile.BAK'

Also, why not use the built-in SQL Server tools to perform a backup and try restoring that set and make sure that works?

NinjaBomb
  • 159
  • 2
  • 5
  • 17
  • I'm not using the built-in tools because this is SQL Server Express, and they can't be automated. So I instead have to use T-SQL scripts run through sqlcmd via a batch file to backup the database. – Chris May 13 '10 at 15:47
  • By the way, "The backup set on file 1 is valid." – Chris May 13 '10 at 17:00
0

You can restore forward (2005-> 2008) but you can't restore back to earlier versions.

This error means that you used multiple files for your backup, and you're only providing one of the files when you're trying to restore. Most likely the cause was when backing up using SQL Server Management Studio, somneone forgot to click "remove" to remove any existing backup destinations before you "add" the file you want to back up to.

Jim B
  • 24,081
  • 4
  • 36
  • 60
  • Yeah it was probably something like that. By default it backs up to somewhere buried in the program files directory, and I must have removed that. It's working now that I've removed SQL Server 2005 and redid the backup using the management studio. I'm not sure if that script I found online is producing good backups yet, so I'll have to test that more. But I don't think I'm having any issues anymore with this, thanks. – Chris May 13 '10 at 18:05