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.
| 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 DatabaseNameAnd 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: 3241Severity: 16State: 201Line 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) |
 |
|
|
|
|
|
|
|
Unable to create a working backup