469,126 Members | 1,295 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

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 ', 
  5.   SKIP, NOREWIND, NOUNLOAD, STATS = 10
  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
  19. RESTORE VERIFYONLY 
  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
  23.  
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' 
  3.   WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
  4. GO
  5.  
The RESTORE failed with this message:
[ERROR 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.)
[/ERROR MESSAGE]

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
  1. ALTER DATABASE [ifas] SET READ_COMMITTED_SNAPSHOT ON;
  2. go
  3. ALTER DATABASE [ifastest] SET READ_COMMITTED_SNAPSHOT ON;
  4. go
  5. select
  6.   name
  7.  ,is_read_committed_snapshot_on
  8.  ,snapshot_isolation_state
  9. from
  10.   sys.databases
  11. go
  12.  
Does anyone know what the problem is?
Thanks a bunch in advance.
Jun 24 '10 #1
2 2431
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.