teecee72 avatar image
teecee72 asked

SQL Server 2005 Backup Error

I have a production SQL Server 2000 instance that I back up (databases) daily and restore to a failover SQL server instance. Both SQL servers have inherited their default (host server)names. I can backup my production databases (SQL Server 2000) and restore them to the failover server (SQL Server 2005) and all databases work fine, and the Dot Net applications that connect to each of them run fine.

When I attempt to back up a database from the failover instance, I receive the following error:

Backup failed for Server 'Sandbox' (Microsoft.SqlServer.Smo)

Additional information:

System.Data.SqlClient.SqlError: THe backup of the filegroup "sysft_Name" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo).

I managed to find some article a while back that indicated that this was catalog related (system files thinking that the server instance is still the production server) and that deleteing the catalog and recreating it would solve the problem, but I don't know how to do that. Has anyone in this forum run into this before/ It is especially pressing, because I want to upgrade my production SQL server, and need to know that I can backup a restored database from a server that was named differently.

The master database backs up ok, it is just the other database that throws the error.

EDIT: Here is the script of the backup:

BACKUP DATABASE [13Software] TO  DISK = N'F:\Temp\Test_Bkp_1'                     
  WITH NOFORMAT, INIT,                      
  NAME = N'13Software-Full Database Backup',                     
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10                    
declare @backupSetId as int                    
select @backupSetId = position                     
         from msdb..backupset                     
         where database_name=N'13Software'                     
         and backup_set_id=(select max(backup_set_id)                     
                          from msdb..backupset                     
                           where database_name=N'13Software' )                    
if @backupSetId is null                     
    raiserror(N'Verify failed. Backup information for database ''13Software'' not found.', 16, 1)                     
RESTORE VERIFYONLY                     
       FROM  DISK = N'F:\Temp\Test_Bkp_1'                     
       WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND                    

The only error log output is to check the application log, which reveals no useful information.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Kristen avatar image
Kristen answered

I would get rid of the TAPE-specific Backup instructions REWIND / NOUNLOAD etc.

I find the use of NOFORMAT, INIT and SKIP contradictory.

I imagine that using INIT with SKIP will mean that if the file already includes backups that have not yet reached their expiry date the file WILL be pre-deleted, but maybe SQL Server doesn't see it that way! for good measure you could either manually pre-delete the file yourself, or use a brand new filename.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.