SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

Question

I've installed and successfully configured our SQL Server 2012 AlwaysOn 2-node servers for our new "Intranet" that is coming out. I've gotten AlwaysOn working great, and our Front End servers for the Intranet will be using SharePoint 2013. The glitch is that SharePoint 2013 is configured to add databases automatically to our SQL Server 2012 back end, but NOT to AlwaysOn. In reading about this and in contacting Microsoft MSDN support, the default answer is "you must manually find, select, back-up and then add those new databases individually to get them into AlwaysOn."

But wait; that can be quite a task, constantly checking the SQL Server back-end servers to see what databases were created, then having to add them into AlwaysOn, 7/24! I'm looking for a script or process that will check for new databases, back those new databases up in FULL mode, (for being added to AlwaysOn, of course) then add those databases to AlwaysOn, all automatically. Or have this run every...1-2 hours? (without user intervention)

What I've come up with so far is this script that actually identifies the newly-added databases, (not yet in AlwaysOn), and then backs them up to a shared location. My next task is to find those newly-added databases and through the various processes needed, get them added to AlwaysOn. This will involve some sort of looping action, I imagine. I'm not a T-SQL/scripting guru; is there any solution or script that I might access that would do this? (add databases to AlwaysOn automatically)?

Please advise, I'm sure I'm not the first person to have this issue. I have seen previous posts on various Internet Sites (including this one!) , and the solution is either incorrect, or states something like "sure, go ahead and just script that!". Thanks, but I need just a little more detail there.

Thanks again,

-Allen

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  

-- specify database backup directory
SET @path = '\\atel-web-be2\backups\'  

DECLARE db_cursor CURSOR FOR  
select name from sys.databases
where group_database_id is null and replica_id is null 
    and name not in('master','model','msdb','tempdb')

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0   
BEGIN   
    SET @fileName = @path + @name + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName   
    FETCH NEXT FROM db_cursor INTO @name   
END 

CLOSE db_cursor   
DEALLOCATE db_cursor

Where you do define the group(s) you want to add the database(s) to?
Also do you have linked servers set up for each of the replicas? And what do you plan to do in the event that the group has failed over to a different replica?
Aaron: I not yet defined the Availabilty Group that I want to add the databases to; I don't know how to put that into the script. I'm not that good with T-SQL scripts, When a failover occurs, I'd like this script to of course, still work so the databases are added to the Active replica in AlwaysOn. This is what makes this so complex; it needs to work in the event of a failover. But yet, this is so basic and essential; when a database is added, manual intervention should not be needed to insure the databases are added to AlwaysOn.
You're talking about two very different events when you mention failover and adding a database. Also I don't know how you can add a database to an availability group when you've just created the database and no availability group exists. Are you trying to create a distinct availability group for every database? WHY?
This is a fairly advanced topic in SQL Server. I strongly suggest you stop approaching it as an "I just need a script that works" problem. You need to learn the fundamentals of how scripts works, how to create and alter stored procedures, how to call stored procedures, etc. Server admin for 20 years or not, you're going to have the same challenges if you turn to PowerShell or any other tech to solve this problem - you need to have a good grasp on the fundamentals and what is going on underneath a script you can copy, paste and run before you should ever consider running it.

Kin · Accepted Answer · 2013-04-18 20:24:03Z

You don't have to write a cursor tsql script to check for new database created and schedule it to run for e.g. every minute. Instead use EVENTDATA() function in conjunction with server level trigger.

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

Since you now have an automated mechanism in place that will fire up when a new database is created, you can use ALTER AVAILABILITY GROUP and ALTER DATABASE - SET HADR

basically you have to just include :

-- Move each database into the Availability Group
-- Change database name and Group as per your environment.
ALTER DATABASE Test1 SET HADR AVAILABILITY GROUP = TestAG
ALTER DATABASE Test2 SET HADR AVAILABILITY GROUP = TestAG
GO

Thinking of this a little bit more, you can be more creative to automate it --

-- create a driver table
create table AlwaysON_Candidates (
    DatabaseName sysname
    ,createdate datetime default getdate()
    ,IsAlwaysOnMember bit default 0 -- 0 = Not a part of AG
    ) -- 1 = Is part of AG
go
-- below insert will be governed by the server level trigger
insert into AlwaysON_Candidates (DatabaseName)
values ('Test1')
--- check the values in the driver table
select *
from AlwaysON_Candidates
--- add database to AG
alter database Test1
set HADR AVAILABILITY group = TestAG
-- update the bit in the driver table AlwaysON_Candidates
update AlwaysON_Candidates
set IsAlwaysOnMember = 1
where DatabaseName = 'Test1'

some good references for setting it up using tsql can be found here and here

EDIT: Below script will help you. Obviously you have to Understand it and test it in a test environment.

/************************************************************************************
Author  : Kin Shah
Version : 1.0.0 for dba.stackexchange.com

Note:   This script does not have ERROR handling and is not tested.
        Use at your own risk, It will print out the sql statements, but wont execute it
        unless the print statements have been modified to use "exec"

        UNDERSTAND the script and then test it on a TEST environment !!!!!!!!

*************************************************************************************/
-- create table 
set ansi_nulls on
go

set quoted_identifier on
go

create table AlwaysON_Candidates (
    ID int identity(1, 1)
    ,EventType nvarchar(128) null
    ,DatabaseName nvarchar(128) null
    ,LoginName nvarchar(128) null
    ,UserName nvarchar(128) null
    ,AuditDateTime datetime null
    ,IsAlwaysOnMember bit default 0
    )
go

alter table [dbo].[AlwaysON_Candidates] add default((0))
for [IsAlwaysOnMember]
go

-- create server trigger
if exists (
        select *
        from master.sys.server_triggers
        where parent_class_desc = 'SERVER'
            and name = N'ddl_trig_database'
        )
    drop trigger [ddl_trig_database] on all server
go

set ansi_nulls on
go

set quoted_identifier on
go

create trigger [ddl_trig_database] on all server
for CREATE_DATABASE as

insert into NewDatabases
select EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') as EventType
    ,EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)') as DatabaseName
    ,EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)') as LoginName
    ,EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)') as UserName
    ,GETDATE() as AuditDateTime
    ,0 as IsAlwaysOnMember
go

set ansi_nulls off
go

set quoted_identifier off
go

ENABLE trigger [ddl_trig_database] on all server
go

--- PREREQUISITE ... CREATE A LINKED SERVER FROM PRIMARY TO SECONDARY SERVER !!!
--- fill in *** CHANGE HERE values 
--- test it on a TEST server
--- Not tested and not responsible for any dataloss. UNDERSTAND it and test it before implementing it.
declare @databasename varchar(max)
declare @sqlbackup varchar(max)
declare @sqlrestore varchar(max)
declare @PrimaryAG varchar(max)
declare @SecondaryAG varchar(max)
declare @backupPath varchar(max)

set @backupPath = '\\servername\sharedfolder\' --- *** CHANGE HERE

declare @group sysname

set @group = N'your_group_name' --- *** CHANGE HERE

declare @remotesql1 varchar(max)
declare @remotesql2 varchar(max)
declare @linkedserverName sysname

set @linkedserverName = 'kin_test_AG_LS' --- *** CHANGE HERE

select @databasename = min(DatabaseName)
from AlwaysON_Candidates
where IsAlwaysOnMember = 0

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

    -- ** RESTORE HAPPENS HERE **
    select @sqlrestore = N'RESTORE DATABASE ' + QUOTENAME(@databasename) + ' FROM DISK = ''' + @backupPath + @databasename + '_forAG.BAK'' WITH REPLACE, NORECOVERY;
    RESTORE LOG ''' + @backupPath + @databasename + '_forAG.TRN'' WITH NORECOVERY;'

    print @sqlrestore

    select @remotesql1 = N'EXEC ' + QUOTENAME(@linkedserverName) + '.master..sp_executesql @sqlrestore;'

    print @remotesql1 --- *** CHANGE HERE for EXEC master..sp_executesql @sqltext

    -- 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

    -- 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 @sqlrestore;'

    print @remotesql2 --- *** CHANGE HERE for EXEC master..sp_executesql @SecondaryAG

    -- 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

Except this doesn't show all the interim steps that are required. (For example, where is your restore sequence?)
@AaronBertrand edited my answer to reflect some creative idea to consider for automation along with some references on setting up AlwaysOn using TSQL. Currently, time does not permit me to write up the script that will do what I have mentioned. But the OP will get an idea on how it can be done.
Kin: you wrote: the OP will get an idea on how it can be done. Who is the OP? (not sure what OP is) Not being a T-SQL/scripting master, I'm not sure how to take what you've written and expand upon it. Thoughts?
@AllenValk66 Apologies for abbreviation .. OP = Original Poster (learned it from sqlserver central :-)). I meant to give a starting point to provide what can be done as currently I dont have an enviornment that I can test the script on. The references provide have some t-sql code. What I have mentioned is simple -- Create a serverlevel trigger to log the newely created databases into a table (I have given the table structure as well) and then using a while loop the databases can be added to the Availablity group (following the tsql code that I have referenced or Aaron's code as well).

Aaron Bertrand · Answer 2 · 2013-04-18 18:14:01Z

Lots and lots of caveats here. I've tested this in a very limited way in a scenario where the data/log paths are congruent across all replicas, haven't added error handling, etc. You can call this stored procedure from the DDL trigger if you decide to go that way, as Kin suggested, or from a job, or what have you.

See additional comments inline.

CREATE PROCEDURE dbo.AddNewDBsToGroup
  @group SYSNAME = N'your_group_name', -- *** SPECIFY YOUR GROUP NAME HERE ***
  @path  SYSNAME = N'\\atel-web-be2\backups\',
  @debug BIT = 1
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE 
    @sql        NVARCHAR(MAX) = N'',
    @remote_sql NVARCHAR(MAX) = N'';

  DECLARE @t TABLE(db SYSNAME);

  INSERT @t SELECT name FROM sys.databases 
  WHERE replica_id IS NULL AND database_id > 4;

  DECLARE @r TABLE(s NVARCHAR(512));

  -- get the *healthy* replicas available for this group
  -- you'll need error handling to handle cases where any
  -- of the replicas is currently *not* healthy. This 
  -- script does not tell you this happened.

  INSERT @r SELECT r.replica_server_name
  FROM sys.availability_groups AS g
  INNER JOIN sys.dm_hadr_availability_group_states AS s
  ON g.group_id = s.group_id
  INNER JOIN sys.availability_replicas AS r
  ON g.group_id = r.group_id
  AND r.replica_server_name <> @@SERVERNAME
  WHERE g.name = @group
  AND s.primary_replica = @@SERVERNAME
  AND s.primary_recovery_health_desc = 'ONLINE'
  AND s.synchronization_health_desc = 'HEALTHY';

  -- add the database to the group on the primary:

  SELECT @sql += N'ALTER AVAILABILITY GROUP ' 
    + QUOTENAME(@group) + ' ADD DATABASE ' + QUOTENAME(db) + ';'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- back up the database locally:
  -- this assumes your database names don't have characters illegal for paths

  SET @sql = N'';

  SELECT @sql += N'BACKUP DATABASE ' + QUOTENAME(db) -- ** BACKUP HAPPENS HERE **
    + ' TO DISK = ''' + @path + db + '.BAK'' WITH COPY_ONLY, FORMAT, INIT, COMPRESSION;
    BACKUP LOG ' + QUOTENAME(db) +
    ' TO DISK = ''' + @path + db + '.TRN'' WITH INIT, COMPRESSION;'
  FROM @t;

  IF @debug = 1
  BEGIN
    PRINT @sql;
  END
  ELSE
  BEGIN
    EXEC master..sp_executesql @sql;
  END

  -- restore the database remotely:
  -- this assumes linked servers match replica names, security works, etc.
  -- it also assumes that each replica has the exact sime data/log paths
  -- (in other words, your restore doesn't need WITH MOVE)

  SET @sql = N'';

  SELECT @sql += N'RESTORE DATABASE ' + QUOTENAME(db) -- ** RESTORE HAPPENS HERE **
    + ' FROM DISK = ''' + @path + db + '.BAK'' WITH REPLACE, NORECOVERY;
    RESTORE LOG ''' + @path + db + '.TRN'' WITH NORECOVERY;
    ALTER DATABASE ' + QUOTENAME(db) + ' SET HADR AVAILABILITY GROUP = '
    + QUOTENAME(@group) + ';'
  FROM @t; 

  SET @remote_sql = N'';

  SELECT @remote_sql += N'EXEC ' + QUOTENAME(s) + '.master..sp_executesql @sql;'
    FROM @r;

  IF @debug = 1
  BEGIN
    PRINT @sql;
    PRINT @remote_sql;
  END
  ELSE
  BEGIN
    EXEC sp_executesql @remote_sql, N'@sql NVARCHAR(MAX)', N'SELECT @@SERVERNAME;';
  END
END
GO

Once you've created the stored procedure, you can call it this way and look at the messages pane to see if it has identified the right group, databases, and servers before ever running it:

EXEC dbo.AddNewDBsToGroup @debug = 1;

When you are confident it is going to do the right thing (and you fully understand what 'the right thing" is), then change that to:

EXEC dbo.AddNewDBsToGroup @debug = 0;

If it fails, don't worry, it will tell you.

The above command to "ALTER DATABASE Test1 SET HADR AVAILABILITY GROUP = TestAG" will not (and does not) work until the database and its log file is backed up and restored to the Secondary server. I've tried this command on a stand-alone database, and while it is added into the AlwaysON configuration, it is in a serious error state. This is because it's not been fully copied and restored to the Secondary, first. This process must be done for each database, in order to have a successful inclusion into AlwaysOn.
@AllenValk66 Please look closer at the script. I do perform the backup and restore sequences necessary. As I said, I've tested this.
@AllenValk66 well, I don't call SET HADR ... until very close to the bottom, so I don't know how you saw that without seeing the backup/restore too. shrug
Great, you've created the stored procedure. Now you need to call it, e.g. EXEC dbo.AddNewDBsToGroup;. Also note that if you want to test it you can specify @debug = 1 - you won't see all the commands that will be run but it will print out most of them. Now, don't take this the wrong way, but can I ask who put you in charge of HA/DR at your company if you can't get past these minor issues?

AllenValk66 · Answer 3 · 2013-04-19 21:27:28Z

I found the answer, in working with MSDN and modifying the script:

xp_cmdshell 'del /F /Q /S \atel-vm-test\backup*.*'

--drop table #dbs --deallocate adddbs IF OBJECT_ID('dbo.#dbs', 'U') IS NOT NULL DROP TABLE dbo.#dbs

--IF CURSOR_STATUS('global','adddbs')>=-1 --BEGIN -- DEALLOCATE adddbs --END

IF (SELECT CURSOR_STATUS('global','adddbs')) >=0 BEGIN DEALLOCATE adddbs END

create table #dbs(a int primary key identity, dbname varchar(100))

declare @nextdb varchar(100) declare @restorestring varchar(200)

--Populate temp table insert into #dbs(dbname) --select name from sys.databases where create_date between getdate()-1 and getdate()

select name from sys.databases where group_database_id is null and replica_id is null and name not in('master','model','msdb','tempdb')

--Create a cursor to declare adddbs cursor for select dbname from #dbs

open adddbs

FETCH NEXT FROM adddbs INTO @nextdb

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC ('BACKUP DATABASE ' + @nextdb + ' TO DISK = ' + '''\atel-vm-test\backup\' + @nextdb + 'initialize.bak''')

EXEC ('ALTER AVAILABILITY GROUP [atel-testAG] ADD DATABASE ' + @nextdb)

EXEC ('BACKUP DATABASE ' + @nextdb + ' TO DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '.bak''')

EXEC ('BACKUP LOG ' + @nextdb + ' TO DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '_log.bak''')

set @restorestring='sqlcmd -S atel-vm-test2 -E -Q"RESTORE DATABASE ' + @nextdb + ' FROM DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring print 'sqlcmd -S [atel-vm-test2] -E -Q"RESTORE DATABASE ' + 'agd2' + ' FROM DISK = ' + '''\atel-vm-test\backup\' + 'agd2' + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' set @restorestring='sqlcmd -S atel-vm-test2 -E -Q"RESTORE LOG ' + @nextdb + ' FROM DISK = ' + '''\atel-vm-test\backup\' + @nextdb + '_log.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S atel-vm-test2 -E -Q"ALTER DATABASE ' + @nextdb + ' SET HADR AVAILABILITY GROUP = [atel-testag]"' exec xp_cmdshell @restorestring

FETCH NEXT FROM adddbs INTO @nextdb END

CLOSE adddbs DEALLOCATE adddbs

--end Substitute atel-testAG with your own Availability Group name. Substitute \atel-vm-test\backup with the shared backup location of your server. Substitute \atel-vm-test2 with the name of your secondary server. This job is scheduled to be run from the Primary server. (I don't have it set as yet to run from the variable "Primary Server", just for now it's running on my primary server called atel-vm-test.

Make sure you have the "xp_cmdshell" function permissions to run on your SQL server.

I have gotten this to run with zero errors, and it now is a scheduled SQL Job to run every hour. It works as expected. thanks for the assistance.

The above script will only take care of taking backup, restoring it to secondary and joining the AG group. If you see the server trigger that I have mentioned, it will fire when a new database is created and then you can run your script (which you have tested as working). This way, you don't have to schedule a job that will check for new databases.
Thanks for the note, Kin. I tried to run your script, but kept getting the errors: Msg 1781, Level 16, State 1, Line 2 Column already has a DEFAULT bound to it. Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors.
try removing the default IsAlwaysOnMember bit default 0 to IsAlwaysOnMember bit in the create table. You dont need it. Let me know how it goes

2 revs AllenValk66 · Answer 4 · 2013-04-26 18:12:31Z

Below is a completed and now-working script that I have to run as a SQL Server Agent job every 20 minutes. It will: -Automatically determine the Primary and the Secondary server name. -Automatically determine the Availabilty Group name. -Convert the database(s) being added from Simple mode to Full mode. (necessary for any database to be successfully added to AlwaysOn). work on a SQL server with a single SQL instance or on the second SQL instance. If a 2nd SQL instance is being engaged with this script, you MUST share out the second name of the backup path.

If this script is being run manually in the SSMS console, you must first run the command "DROP TABLE dbo.#dbs" (if this script was previously run). For some reason, this command doesn't get read in the manual running of this script in SSMS, but it does work when run as a SQL Server Agent job. (????)

The \backups location is hard-coded in this script; change this as needed. You must also enable the command function "xp_cmdshell" in your SQL server SSMS console. Google that to find out; it's an easy command call. This is designed to be run on a 2-SQL server configuration, with one Primary and one Secondary.

xp_cmdshell 'del /F /Q /S \atel-web\be1\backups*.*'

exec xp_cmdshell 'del /F /Q /S \atel-web\be2\backups*.*'

USE master

DECLARE @secondaryservername nvarchar(50)

DECLARE @primaryservername nvarchar(50)

DECLARE @availabilitygroup nvarchar(50)

SET @secondaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states
, sys.dm_hadr_availability_replica_cluster_states where role_desc = 'SECONDARY' AND sys.dm_hadr_availability_replica_states.replica_id = sys.dm_hadr_availability_replica_cluster_states.replica_id)

SET @primaryservername = (select replica_server_name AS Servername from sys.dm_hadr_availability_replica_states
, sys.dm_hadr_availability_replica_cluster_states where role_desc = 'PRIMARY' AND sys.dm_hadr_availability_replica_states.replica_id = sys.dm_hadr_availability_replica_cluster_states.replica_id)

SET @availabilitygroup = (SELECT name FROM [sys].[availability_groups])

IF OBJECT_ID('dbo.#dbs', 'U') IS NOT NULL DROP TABLE dbo.#dbs

IF (SELECT CURSOR_STATUS('global','adddbs')) >=0 BEGIN DEALLOCATE adddbs END

create table #dbs(a int primary key identity, dbname varchar(100)) declare @nextdb varchar(100) declare @restorestring varchar(400)

--Populate temp table insert into #dbs(dbname)

select name from sys.databases where group_database_id is null and replica_id is null and name not in('master','model','msdb','tempdb')

--Create a cursor to declare adddbs cursor for select dbname from #dbs

open adddbs

FETCH NEXT FROM adddbs INTO @nextdb

WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('ALTER DATABASE' + '[' + @nextdb + ']' + 'set RECOVERY FULL') EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\' +@primaryservername+'\backups\' + '[' + @nextdb + ']' + 'initialize.bak''')

EXEC ('ALTER AVAILABILITY GROUP ['+ @availabilitygroup +'] ADD DATABASE ' + '[' + @nextdb + ']')

EXEC ('BACKUP DATABASE ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\'@primaryservername+'\backups\' + '[' + @nextdb + ']' + '.bak''')

EXEC ('BACKUP LOG ' + '[' + @nextdb + ']' + ' TO DISK = ' + '''\'@primaryservername+'\backups\' + '[' + @nextdb + ']' + '_log.bak''')

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE DATABASE ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\' +@primaryservername +'\backups\' + '[' + @nextdb + ']' + '.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"RESTORE LOG ' + '[' + @nextdb + ']' + ' FROM DISK = ' + '''\' +@primaryservername+'\backups\' + '[' + @nextdb + ']' + '_log.bak''' + ' WITH NORECOVERY, NOUNLOAD, STATS = 5"' exec xp_cmdshell @restorestring

set @restorestring='sqlcmd -S ' +@secondaryservername+' -E -Q"ALTER DATABASE ' + '[' + @nextdb + ']' + ' SET HADR AVAILABILITY GROUP = [' + @availabilitygroup +']"' exec xp_cmdshell @restorestring

FETCH NEXT FROM adddbs INTO @nextdb END

CLOSE adddbs DEALLOCATE adddbs

asked	24 days ago
viewed	244 times
active	15 days ago

SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

migrated from stackoverflow.com Apr 17 at 20:03

4 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server sql-server-2012 alwayson or ask your own question.

SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell

migrated from stackoverflow.com Apr 17 at 20:03

4 Answers

Did you find this question interesting? Try our newsletter

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server sql-server-2012 alwayson or ask your own question.

Related