Restore problem due to replication
-
I have encountered a problem while restoring a backup of a database, published for transactional replication, on the same server with a different database name. The error says...Msg 15247, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 112
User does not have permission to perform this action.
The replication agent job 'SON1834-TDB4x_Test_21Jan-rpln1834_TDB4x_Tes-SON1841-TDB4x_Test_21Jan-15249630-0A1A-4F6C-839F-9654E806689E' was not removed because it has a non-standard name; manually remove the job when it is no longer in use.
My simple intention is to have a backup and restore the same with a different name on the same server using T-SQL, as follows -
USE [master]
GO
DECLARE @STRSQLVARCHAR(2000),
@DATABASE_NAMEVARCHAR(25),
@DB_PATHVARCHAR(200),
@DB_USERVARCHAR(25),
@OLD_DB_NAMEVARCHAR(25)
SET @DATABASE_NAME='TDB4x'
SET @DB_PATH='D:\DATA\'
SET @DB_USER='TDB4Test'
SET @OLD_DB_NAME='TDB4x_Test_21Jan'
IF NOT EXISTS (SELECT name
FROM sys.databases
WHERE name = @DATABASE_NAME)
BEGIN
/*******************************************************************************\
--***************************BACKUP DATABASE***********************************--
\*******************************************************************************/
SET @STRSQL='EXEC sp_addumpdevice ''disk'',
''OLD_DB_BKUP'',
'''+@DB_PATH+@OLD_DB_NAME+'.bak'''
EXEC(@STRSQL)
SET @STRSQL='BACKUP DATABASE '+@OLD_DB_NAME+' TO OLD_DB_BKUP'
EXEC(@STRSQL)
/*******************************************************************************\
--***************************RESTORE DATABASE***********************************--
\*******************************************************************************/
SET @STRSQL='RESTORE DATABASE '+@DATABASE_NAME+'
FROM OLD_DB_BKUP
WITH RECOVERY,
MOVE '''+@OLD_DB_NAME+''' TO '''+@DB_PATH+@DATABASE_NAME+'.mdf'',
MOVE '''+@OLD_DB_NAME+'_Log'' TO '''+@DB_PATH+@DATABASE_NAME+'.ldf'',
MOVE ''sysft_FullText_tdb4'' TO '''+@DB_PATH+@DATABASE_NAME+'_FullText\'''
EXEC(@STRSQL)
--IF EXISTS (SELECT name
--FROM master.dbo.sysdevices
--WHERE name = N'OLD_DB_BKUP')
--EXEC master.dbo.sp_dropdevice @logicalname = N'OLD_DB_BKUP'
--PRINT('Files not required any more deleted successfully!')
/*******************************************************************************\
--***************************CHANGE SCRIPTS ***********************************--
\*******************************************************************************/
END
ELSE
BEGIN
PRINT('Database already exists!')
PRINT('If you wish to create the database with the same name, then')
PRINT('drop the database first and then run the script!')
END
PRINT(' ')
PRINT('Note : If the script generated errors, please contact the script owner!')
My code is running perfectly, except that damn error....Pls. help.
Thanks in advance.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit. -
My guess would be that the repl ID is what's stopping you. It sounds like it's looking to do something that that ID like either kill it or keep it, but since it's in use by the actual live DB it can't.
I've never tried doing this before, but it makes sense to me that you can't.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply