472,119 Members | 1,726 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

SQL Server 2005 - Restore failed

I did a COPY_ONLY backup of a production DB. The BACKUP worked fine, here's the code:

Expand|Select|Wrap|Line Numbers
  1. BACKUP DATABASE [ifas] 
  2.   TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\IFAS Backup Copy Only' 
  3.   WITH COPY_ONLY, DESCRIPTION = N'Backup COPY ONLY for restore to IFASTest  ',  
  4.   RETAINDAYS = 60, NOFORMAT, INIT, NAME = N'IFAS-Full Database Backup COPY ONLY ', 
  6. GO
  7. declare @backupSetId as int
  8. select 
  9.   @backupSetId = position 
  10. from 
  11.   msdb..backupset 
  12. where 
  13.   database_name=N'ifas' and 
  14.   backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ifas' )
  15. if @backupSetId is null 
  16.   begin 
  17.     raiserror(N'Verify failed. Backup information for database ''ifas'' not found.', 16, 1) 
  18.   end
  20.   FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\IFAS Backup Copy Only' 
  21.   WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
  22. GO
I then tried to restore the backup to an existing test version of the DB. The restore failed. Here's the code:
Expand|Select|Wrap|Line Numbers
  1. RESTORE DATABASE [ifastest] 
  2.   FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\IFAS Backup Copy Only' 
  4. GO
The RESTORE failed with this message:
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Totally unrelated to the reason for doing the BACKUP, in between the backup and restore, I did a RESTART of the server, plus executed this script:
Expand|Select|Wrap|Line Numbers
  2. go
  4. go
  5. select
  6.   name
  7.  ,is_read_committed_snapshot_on
  8.  ,snapshot_isolation_state
  9. from
  10.   sys.databases
  11. go
Does anyone know what the problem is?
Thanks a bunch in advance.
Jun 24 '10 #1
2 2544
Jerry Winston
145 Expert 100+
The snapshot is the problem. You can't drop, detach, or restore a source database when a snapshot is present. Try removing the snapshots and trying it again. If this works, let me know.
Jun 28 '10 #2
I was able to restore that database without removing the snapshot. It just took several attempts. The DB is being used for training, so I am unable to re-try the restore at this time. When I do, I'll post another message.
Jun 29 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Arpan | last post: by
1 post views Thread by edmundleungs | last post: by
3 posts views Thread by Lee T. Hawkins | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.