Add a database to AG in a stored proc

• murrayb3024 50692

  SSC Enthusiast

  Points: 111

  More actions

  December 8, 2016 at 9:18 am

  #401182

  Each month we have 2 new monthly db's added to an instance. We have some stored procs run that do some work on them and then name them based on the date using variables. We just moved to a 2014 2 node cluster with AG. I have some scripting in place to create the full backups of them after they are built and they are in FULL recovery mode. I was hoping to add steps to the proc to then add these to the AG. I did the wizard and then had it script it out and it comes back with this:

  --- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

  :Connect MyServerPrimary

  USE [master]

  GO

  ALTER AVAILABILITY GROUP MyAG

  ADD DATABASE [MyDatabase_12012016];

  GO

  :Connect MyServerPrimary

  BACKUP DATABASE [MyDatabase_12012016] TO DISK = N'\\somelocation\MyDatabase_12012016.bak' WITH COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

  GO

  :Connect MyServerSecondary

  RESTORE DATABASE

  Has anyone done something like this in a stored proc? I haven't found much online for this.