December 31, 2016 at 4:25 am
Hi,
I am trying to write a script that assesses whether an availability group is Primary or not. If it is then the appropriate databases will be backed up, if not the script ends with success. It has been developed on SQL Server 2016sp1.
The Problem lies in the variable @dbname which contains the Name of the database to be backed up. For some reason it Shows as not being declared.
Here is the script:
--create procedure sp_BackAG
--as
if (
select count(*) --adc.database_name
from sys.availability_groups ag
inner join sys.availability_databases_cluster adc
on (ag.group_id = adc.group_id)
inner join sys.dm_hadr_availability_replica_states dhars
on (dhars.group_id = ag.group_id)
where dhars.role = 1)
> 0
begin
declare @sql nvarchar(1000);
declare @path nvarchar(500);
declare @ext nvarchar(10);
declare @cmd nvarchar(1000);
declare @dbname nvarchar(100);
set @sql = 'BACKUP DATABASE @dbname TO DISK = N'''
set @path = '\\Windows-DC1\Backup\';
set @ext = '.bak''' + ';';
declare a cursor
static
for
select adc.database_name
from sys.availability_groups ag
inner join sys.availability_databases_cluster adc
on (ag.group_id = adc.group_id)
inner join sys.dm_hadr_availability_replica_states dhars
on (dhars.group_id = ag.group_id)
where dhars.role = 1
order by adc.database_name asc;
open a;
fetch next from a
into @dbname
while @@fetch_status = 0
begin
set @cmd =@sql
+ @path
+ @dbname
+ '_'
+ replace(convert(varchar, getdate(), 113), ' ', '_')
+ @ext;
--exec sp_executesql @cmd;
print @cmd;
end;
close a;
deallocate a;
end;
I have tried putting the declaration in different places and putting the fetch inside the block where the dynamic SQL is built but nothing seems to work.
In order to retain the smallest amount of sanity I would be grateful for any help you can offer.....
January 2, 2017 at 12:37 pm
you are declaring @dbname in one context and expecting it to appear in another context as already declared, not going to happen!
How about something like this (didn't test it, more for reading and discussing than running):
begin
declare @sql1 nvarchar(1000);
declare @sql2 nvarchar(1000);
declare @path nvarchar(500);
declare @ext nvarchar(10);
declare @cmd nvarchar(1000);
declare @dbname nvarchar(100);
set @sql1 = 'BACKUP DATABASE '
set @sql2 = ' TO DISK = N'''
set @path = '\\Windows-DC1\Backup\';
set @ext = '.bak''' + ';';
declare a cursor
static
for
select adc.database_name
from sys.availability_groups ag
inner join sys.availability_databases_cluster adc
on (ag.group_id = adc.group_id)
inner join sys.dm_hadr_availability_replica_states dhars
on (dhars.group_id = ag.group_id)
where dhars.role = 1
order by adc.database_name asc;
open a;
fetch next from a
into @dbname
while @@fetch_status = 0
begin
set @cmd =@sql1
+ @dbname
+ @sql2
+ @path
+ '_'
+ replace(convert(varchar, getdate(), 113), ' ', '_')
+ @ext;
--exec sp_executesql @cmd;
print @cmd;
end;
close a;
deallocate a;
end;
January 6, 2017 at 12:35 am
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'PRIMARY'
BEGIN
<ENTER CODE HERE>
END
This has work for me. You can simply set it to kick off\ enable a pre-scheduled SQL Agent back up job.
HTH
January 7, 2017 at 1:17 pm
Hi all and sorry for not responding earlier.....
The answer was quite simply that I didn't put the FETCH inside the BEGIN...END block.
This is the final script:
CREATE PROCEDURE sp_AGDB_Backup
AS
IF (SELECT COUNT(*)
FROM sys.availability_groups ag
INNER JOIN sys.availability_databases_cluster adc
ON (ag.group_id = adc.group_id)
INNER JOIN sys.dm_hadr_availability_replica_states dhars
ON (dhars.group_id = ag.group_id)
WHERE dhars.role = 1) > 0
BEGIN
DECLARE @sql NVARCHAR(1000);
DECLARE @path NVARCHAR(500);
DECLARE @ext NVARCHAR(10);
DECLARE @cmd NVARCHAR(1000);
DECLARE @dbname NVARCHAR(100);
DECLARE @role int;
SET @sql = 'BACKUP DATABASE @dbname TO DISK = N''';
SET @path = 'C:\Backup\';
SET @ext = '.bak''' + ';';
SET @role = 1; --Sets to backup on Primary
DECLARE a CURSOR
STATIC
FOR
SELECT adc.database_name
FROM sys.availability_groups ag
INNER JOIN sys.availability_databases_cluster adc
ON (ag.group_id = adc.group_id)
INNER JOIN sys.dm_hadr_availability_replica_states dhars
ON (dhars.group_id = ag.group_id)
WHERE dhars.role = @role
ORDER BY adc.database_name ASC;
OPEN a;
FETCH NEXT FROM a
INTO @dbname;
WHILE @@fetch_status = 0
BEGIN
SET @cmd = REPLACE(@sql, '@dbname', QUOTENAME(@dbname))
+ @path + @dbname
+ '_'
+ REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ' ', '_'),':','-')
+ @ext;
exec sp_executesql @cmd;
--PRINT @cmd;
FETCH NEXT FROM a INTO @dbname;
END;
CLOSE a;
DEALLOCATE a;
END;
Now to see if I can improve it a little more....
Thanks for your help! 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply