May 5, 2017 at 12:14 am
declare @databasename nvarchar(max)
declare @sqlbackup nvarchar(max)
declare @sqlrestore nvarchar(max)
declare @PrimaryAG nvarchar(max)
declare @SecondaryAG nvarchar(max)
declare @backupPath nvarchar(max)
declare @linkedserverName sysname
select @sqlrestore = N'RESTORE DATABASE ' + QUOTENAME(@databasename) + ' FROM DISK = ''''' + @backupPath + @databasename + '_forAG.BAK'''' WITH REPLACE, NORECOVERY;
RESTORE LOG ' + QUOTENAME(@databasename) + ' FROM DISK = ''''' + @backupPath + @databasename + '_forAG.TRN'''' WITH replace, NORECOVERY ;'
select @remotesql1 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql ''' + @sqlrestore + ''''
EXEC master..sp_executesql @remotesql1
Error :
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Thanks.
May 5, 2017 at 12:35 am
any advise please?
Thanks.
May 5, 2017 at 1:14 am
what data type is @remotesql1, that's the only parameter I can't see in the declare list, guessing its varchar
May 5, 2017 at 3:25 am
Please don't bump your post after only 20 minutes. You've been around here long enough to know that that's going to annoy people.
Yes, you haven't declared @remotesql1, and also you haven't set values for all those other variables you've declared, which means that @remotesql1 is going to evaluate to NULL anyway. But that's not the issue here. The problem is that in your dynamic SQL, you're calling sp_executesql, which takes an nvarchar parameter. You need to change that part of your string as follows:...'.master..sp_executesql N''' + @sqlrestore...
John
May 5, 2017 at 7:01 am
This is the whole script. The issue is at the restore side while executing the command in Primary node to run the restore on the secondary node (that node is as linked server under sys.servers).
Any advise will be helpful.
--drop table AlwaysON_Candidates
--Create table AlwaysON_Candidates (
-- ID int identity(1, 1)
-- ,DatabaseName nvarchar(128) null
-- ,LoginName nvarchar(128) null
-- ,UserName nvarchar(128) null
-- ,AuditDateTime datetime null
-- ,IsAlwaysOnMember bit default 0
-- )
--go
-- Insert into AlwaysON_Candidates values ('DB1', 'User1',NULL,'05/04/2017',0);
-- Insert into AlwaysON_Candidates values ('DB2', 'User1',NULL,'05/04/2017',0)
Set nocount on
--DBCC TRACEON (3226,-1)
declare @databasename nvarchar(max)
declare @sqlbackup nvarchar(max)
declare @sqlrestore nvarchar(max)
declare @PrimaryAG nvarchar(max)
declare @SecondaryAG nvarchar(max)
declare @backupPath nvarchar(max)
set @backupPath = '\\backup path\folder' --- *** CHANGE HERE
declare @group sysname
set @group = (select name from sys.availability_groups_cluster) --- *** CHANGE HERE
select @group as AGGroupName
declare @remotesql1 nvarchar(max)
declare @remotesql2 nvarchar(max)
declare @linkedserverName sysname
set @linkedserverName = (select replica_server_name from master.sys.availability_replicas AS AR
inner join master.sys.availability_groups AS AG
ON AG.group_id = AR.group_id
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 0) --- *** CHANGE HERE
select @linkedserverName as LinkedServerName
select @databasename = min(DatabaseName)
from AlwaysON_Candidates
where IsAlwaysOnMember = 0
select @databasename as ListOfDatabases
select * from AlwaysON_Candidates
Begin try
--BEGIN TRANSACTION;
while @databasename is not null
begin
-- ** BACKUP HAPPENS HERE **
select @sqlbackup = N'BACKUP DATABASE ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
BACKUP LOG ' + QUOTENAME(@databasename) + ' TO DISK = ''' + @backupPath + @databasename + '_forAG.TRN'' WITH INIT, COMPRESSION;'
from AlwaysON_Candidates
where IsAlwaysOnMember = 0
print @sqlbackup --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
EXEC master..sp_executesql @sqlbackup
-- ** RESTORE HAPPENS HERE **
select @sqlrestore = N'RESTORE DATABASE ' + QUOTENAME(@databasename) + ' FROM DISK = ''''' + @backupPath + @databasename + '_forAG.BAK'''' WITH REPLACE, NORECOVERY;
RESTORE LOG ' + QUOTENAME(@databasename) + ' FROM DISK = ''''' + @backupPath + @databasename + '_forAG.TRN'''' WITH replace, NORECOVERY ;'
print @sqlrestore
select @remotesql1 = 'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql ''' + @sqlrestore + ''''
Print @remotesql1
EXEC master..sp_executesql @remotesql1
--select @remotesql1 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql ''' + @sqlrestore + ''''
--print @remotesql1 --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
--Execute(@remotesql1)
-- join the AG group on primary
select @PrimaryAG = N'ALTER AVAILABILITY GROUP ' + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(@databasename) + ';'
print @PrimaryAG --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext
EXEC master..sp_executesql @PrimaryAG
-- join the AG group on secondary
select @SecondaryAG = 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' SET HADR AVAILABILITY GROUP = ' + QUOTENAME(@group) + ' ;'
print @SecondaryAG
select @remotesql2 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql ''' + @SecondaryAG + '''';
print @remotesql2 --- *** CHANGE HERE for EXEC master..sp_executesql @SecondaryAG
EXEC master..sp_executesql @remotesql2
-- finally update the table
update AlwaysON_Candidates
set IsAlwaysOnMember = 1
where DatabaseName = @databasename
-- go to another database if it is added newly
select @databasename = min(DatabaseName)
from AlwaysON_Candidates
where IsAlwaysOnMember = 0
and DatabaseName > @databasename
end
--COMMIT TRANSACTION;
End try
Begin catch
--IF @@TRANCOUNT > 0
-- ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
select Error_number() as errorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_LINE() as ErrorLine,
ERROR_PROCEDURE() as ErrorProcedure;
Throw;
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
End Catch
GO
Set nocount off
Thanks.
May 5, 2017 at 7:08 am
SQL-DBA-01 - Friday, May 5, 2017 7:01 AMAny advise will be helpful.
What about the advice I gave you - did you try it?
John
May 10, 2017 at 7:36 pm
Thanks much for the advise. It worked. I think that "N" solved my issue by converting varchar to char!
Thanks.
May 11, 2017 at 2:32 am
You're welcome, and I'm glad it worked for you. I know this is probably what you meant to say, but it actually changed varchar to nvarchar.
John
May 11, 2017 at 6:24 am
Yes,,thanks Jon.
Thanks.
May 20, 2017 at 12:35 pm
Hello,
While restoring databases from one node to another, getting the below error. The other node is added as a linked server and the db size is 50 GB.
OLE DB provider "SQLNCLI11" for linked server "" returned message "Query timeout expired".
Any advise if I need to do something to fix this query timeout error?
Thanks
Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply