Restoring a native mssql replicated database

willg

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.

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

RickyR

Hi Will,

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

willg

The error turned out to be a permission problem. When restoring this db, the user set as owner of the db didn't exist on the new server. So the owner was blank. Once I set that to the same user that was running the restore, in this case the one running the job, then the restore worked.

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.