Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Unable to create a working backup
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jleydon
Starting Member

USA
2 Posts

Posted - 03/21/2013 :  08:44:21  Show Profile  Reply with Quote
Last month I was working on a database that was running on SQL 2000 that was on a Windows 2000 Server. There were no backups running on it and the hard drive raid was failing. I ended up running backups locally for each database. Then I took these databases and moved over to a new Windows 2008 R2 Server with SQL 2008 R2 and restored each one as a new database file (and set them all into SQL 2000 compatibility).

One database gave me some headaches because although it shows that it has 2 files, an LDF and MDF, the restore was looking for a ghost NDF file that did not exist. I ran:
SP_HelpDB DatabaseName

And I would see the existing NDF file. Eventually, I was able to restore it and it ignored the fact that this was not there. Everything was up and running on the new server and has been for a few weeks.

Last week I decided to check backups and the backup that was being created for this would always fail to restore with the error:
System.Data.SQLClient.SQLError: The media family on device 'pathname-backupfile.bak' is incorrectly formed. SQL Server cannot process this media family.

I ran a backup with verify using the GUI and it results in the same error. I believe the failed backups maybe related to the NDF file as it still shows the path name but the file does not exist. The NDF file is also part of a filegroup that does not exist and is offline. Unfortunately there were no backups running on the old server so it could have been years that it has been missing.

Now I am at the point where I have to figure out how to make backups start working:
- Is there a way to forcefully drop this reference to the NDF file?
- Can I somehow create a new database and import everything but this data?

I've been working on this for awhile now and have run out of ideas.

jleydon
Starting Member

USA
2 Posts

Posted - 03/21/2013 :  09:07:54  Show Profile  Reply with Quote
Below is the full technical details when I attempt a restore:
===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd)
at Microsoft.SqlServer.Management.Smo.Restore.ReadFileList(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.AddFilesFromDevice()
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.UpdateFilesGrid()
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.Microsoft.SqlServer.Management.SqlMgmt.IPanelForm.OnSelection(TreeNode node)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.DynamicallyEmbedUserControl(ViewInfo vi, TreeNode node)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.SetView(Int32 index, TreeNode node)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnBeforeSelection(Object sender, TreeViewCancelEventArgs e)

===================================

The media family on device 'D:\Backup Test\TMW_Live\tmw_live.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE FILELIST is terminating abnormally. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

------------------------------
Server Name: .
Error Number: 3241
Severity: 16
State: 201
Line Number: 1


------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000