SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.08 seconds. Powered By: Snitz Forums 2000