Unable to create a working backup

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

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Unable to create a working backup

Author

Topic

jleydon
Starting Member

2 Posts

Posted - 2013-03-21 : 08:44:21

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

2 Posts

Posted - 2013-03-21 : 09:07:54

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources