November 15, 2011 at 3:11 pm
I am in need of any assistance with how to automate a way to find new databases on the primary sql server and then have it created on the secondary sql server which is configured for log shipping and have log shipping on the new db.
Basically I have 300 dbs configured for log shipping on a DR site but I need the DR sql server to identify new dbs that were created on the primary server restored and configured for log shipping on the second or DR server.
Please help
November 16, 2011 at 5:07 am
sys.databases (Transact-SQL)
Contains one row per database in the instance of Microsoft SQL Server.
log_shipping_primary_databases (Transact-SQL)
Stores one record for the primary database in a log shipping configuration. This table is stored in the msdb database.
So get all DBs from sys.databases EXCEPT log_shipping_primary_databases. Then do whatever is required (you know it 😉 ).
November 17, 2011 at 6:04 am
See the following link for details of how to implement log shipping via T-SQL, it details all the stored procedures you will need to execute.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 17, 2011 at 6:40 am
might not be the answer you want to hear but with that many databases and more being created (by an app perhaps?) you should if possible be looking at a more automated process where the replication is taken out of SQLs hands, i.e. SAN replication,
---------------------------------------------------------------------
November 17, 2011 at 7:10 am
I agree with SAN snap-shots but I'm working with a client twhere I inherited this infrastructure that has stand-alone direct attached storage but is now shopping for a SAN environment. Right now I'm trying to get them some type of DR site in place until the SAN environment is in place.
November 17, 2011 at 7:15 am
I have LS configured and working for all present databases.
I was looking for a solution that would help with finding newly created dbs on the primary server to be added to the secondary server and have LS created on it with as less human intervention as possible.
November 17, 2011 at 7:26 am
Ok, but DR typically does not encompass your whole estate, it's meant as temporary failover with core services\applications exposed.
Do you really need to LS everything??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 17, 2011 at 7:50 am
I do, because we have new client dbs created on a regular. So I would like to have a solution that would minimize my time looking on each of my 5 instances for new dbs to be log shipped on the DR site.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply