Restoring a native mssql replicated database
Hello,
I am trying to restore a database that is a publisher of MSSQL native replication. I have a script that should turn off replication and start the restore. But it gives me an error, SQL error 3201: Cannot open backup device 'SQLBACKUP_80CAB5CA-FA31-48B8-B4F3-F40EAC42CBEF'. Operating system error 0x80070002(The system cannot find the file specified.).
This all worked before I had to add replication to the mix so I am wonder if anyone else here has tried to disable replication and do a restore using t-sql scripts/stored procs? Would you share?
Thank you.
Will
*************************************
Here is a copy of the log file.
Also, here is the sp I'm using.
I am trying to restore a database that is a publisher of MSSQL native replication. I have a script that should turn off replication and start the restore. But it gives me an error, SQL error 3201: Cannot open backup device 'SQLBACKUP_80CAB5CA-FA31-48B8-B4F3-F40EAC42CBEF'. Operating system error 0x80070002(The system cannot find the file specified.).
This all worked before I had to add replication to the mix so I am wonder if anyone else here has tried to disable replication and do a restore using t-sql scripts/stored procs? Would you share?
Thank you.
Will
*************************************
Here is a copy of the log file.
SQL Backup log file 7.7.0.7 -SQL "RESTORE DATABASE [MYDB] FROM DISK = 'G:mydb g_backupsfullFULL_mydb_201*.SQB' WITH PASSWORD = 'XXXXXXXXXX', LOGTO = 'G:mydb g_backupslogs', NORECOVERY, MOVE DATAFILES TO 'G:mydbxxx', MOVE LOGFILES TO 'G:mydbxxx', THREADPRIORITY = 5, REPLACE, DISCONNECT_EXISTING " ----------------------------- ERRORS AND WARNINGS ----------------------------- 7/6/2015 2:54:00 PM: Restoring PointClickCare (database) from: 7/6/2015 2:54:00 PM: G:mydb g_backupsfullFULL_mydb_20150626.SQB 7/6/2015 2:54:02 PM: ALTER DATABASE [MYDB] SET ONLINE RESTORE DATABASE [MYDB] FROM VIRTUAL_DEVICE = 'SQLBACKUP_80CAB5CA-FA31-48B8-B4F3-F40EAC42CBEF' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY, MOVE 'xyzzy' TO 'G:mydbxxxxyzzy.mdf', MOVE 'xyzzy_log' TO 'G:mydbxxxxyzzy_log.ldf', REPLACE 7/6/2015 2:54:32 PM: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. Check that the SQL Server instance is running, that you have the SQL Server System Administrator server role; and that no other processes are blocking the backup or restore process; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINESOFTWARERed GateSQL BackupBackupSettingsGlobal[InstanceName] 7/6/2015 2:54:32 PM: Also check that the database is not currently in use. 7/6/2015 2:54:32 PM: 7/6/2015 2:54:51 PM: SQL error 3013: RESTORE DATABASE is terminating abnormally. 7/6/2015 2:54:51 PM: SQL error 3201: Cannot open backup device 'SQLBACKUP_80CAB5CA-FA31-48B8-B4F3-F40EAC42CBEF'. Operating system error 0x80070002(The system cannot find the file specified.). 7/6/2015 2:54:51 PM: 7/6/2015 2:54:52 PM: Memory profile 7/6/2015 2:54:52 PM: Type Maximum Minimum Average Blk count Total 7/6/2015 2:54:52 PM: ---------------- ----------- ----------- ----------- ----------- ----------- 7/6/2015 2:54:52 PM: Commit 1401815040 4096 7378903 8420 62130368512 7/6/2015 2:54:52 PM: Reserve 23772921856 4096 18681317 2184 40799997952 7/6/2015 2:54:52 PM: Free 140592303661056 4096 32263032329 4359 140634557923328 7/6/2015 2:54:52 PM: Private 23772921856 4096 10707583 9588 102664310784 7/6/2015 2:54:52 PM: Mapped 5218304 4096 228797 92 21049344 7/6/2015 2:54:52 PM: Image 31469568 4096 265158 924 245006336 7/6/2015 2:54:52 PM:
Also, here is the sp I'm using.
USE [hoit] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /********************************************************************* Create date: June 30, 2015 Description: This sp will restore the MYDB database from the full backup file found in G:mydb g_backupsfull and the transaction log files found in G:mydb g_backups logs. **********************************************************************/ CREATE PROCEDURE [dbo].[spMYDB_Restore_All] AS DECLARE @exitcode int DECLARE @sqlerrorcode int DECLARE @eSubject varchar(1000) DECLARE @eBody varchar(1000) BEGIN SET NOCOUNT ON; SET @eSubject = 'Restore Message' SET @eBody = 'Base restore OK. Trans log restore OK.' BEGIN TRY -- Turn off replication exec master..sp_replicationdboption @dbname = 'PointClickCare', @optname = 'publish', @value = 'false' -- Restore full backup EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MYDB] FROM DISK = ''G:mydb g_backupsfullFULL_mydb_201*.SQB'' WITH PASSWORD = ''xxxxxxxxxxxxxxxxxx'', LOGTO = ''G:mydb g_backupslogs'', NORECOVERY, MOVE DATAFILES TO ''G:mydbxxx'', MOVE LOGFILES TO ''G:mydbxxx'', THREADPRIORITY = 5, REPLACE, DISCONNECT_EXISTING"', @exitcode OUT, @sqlerrorcode OUT IF (@exitcode >= 500) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) END ELSE BEGIN -- Restore all transaction logs EXECUTE master..sqlbackup '-SQL "RESTORE LOG [MYDB] FROM DISK = 'G:mydb g_backups logsLOG_mydb_201*.SQB'' WITH PASSWORD = ''xxxxxxxxxxxxxxxxxx'', LOGTO = ''G:mydb g_backupslogs'', RECOVERY, THREADPRIORITY = 5, DISCONNECT_EXISTING "', @exitcode OUT, @sqlerrorcode OUT IF (@exitcode >= 500) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) END END -- Turn on replication exec master..sp_replicationdboption @dbname = 'PointClickCare', @optname = 'publish', @value = 'true' END TRY BEGIN CATCH SET @eSubject = 'Restore Error' SET @eBody = 'Error code: '+cast(ERROR_NUMBER() as varchar(20))+', error message: "'+ERROR_MESSAGE()+'".' END CATCH -- Email the restore return codes and/or status message out exec msdb.dbo.sp_send_dbmail @profile_name = 'Default Profile', @recipients = 'myemail@myserver', @subject = @eSubject, @body = @eBody END GO
Comments
Thanks for your inquiry.
We have opened a ticket for you, and will be contacting you through this ticket for further troubleshooting.
Thanks for your time!
Rick
Technical Support
Red Gate Software Ltd.
Of course, what I was doing to "disable/enable" replication is not right (it's way more complex than what I have). I found that to make that work I have to delete replication, do the restore, and recreate replication. Lucky for me, this can all be done via t-sql. Lots and lots of t-sql but it's doable.
I'm all sorted out now.
Thanks Rick, have a good one.