October 16, 2011 at 10:38 pm
Hi,
I've got a group of databases with secondary data (all the data gets transactionally replicated into them). I don't want to backup them, so to avoid any performance impact, so I want to script them out and in case of any failure restore the databases from the script and then re-initialise the replication.
So I was wondering if it is possible to run "Generate and Publish Scripts Wizard" using an sql job?
Thanks.
October 16, 2011 at 10:43 pm
Hi,
You can try SSMS its a free tool for sql server which will generate scripts for you.
October 17, 2011 at 12:13 am
Yes, I know, but I need to automate it and run it from an sql job, not manually.
October 17, 2011 at 1:01 am
I don't want to backup them, so to avoid any performance impact,
If it's just object definitions, scripting make sense else it (script for objects with data) would be worse on performance than backup / restore.
October 17, 2011 at 4:39 am
Dev @ +91 973 913 6683 (10/17/2011)
I don't want to backup them, so to avoid any performance impact,
If it's just object definitions, scripting make sense else it (script for objects with data) would be worse on performance than backup / restore.
Not really. Roust_m suggested he'd reinitialise therefore repopulating the data.
Roust_m (10/16/2011)
...and then re-initialise the replication.
As always there are a number of ways you can do it. You can do it manually through SSMS or automate it.
There are tools to automate scripting or you could look at scripting it yourself. If it was me I was script it through powershell and use the opensource SQLPSX tools.
That said, what are your reasons for not wanting to perform backups? Also, if you reinitialise, are your articles configured to drop/recreate objects anyway as this is the default setting?
Either way would mean far less work. My preference would be to backup the subscriber as you could restore with relative ease without needing to reinitialise.
October 17, 2011 at 4:58 am
The requirement is:
I want to script them out and in case of any failure restore the databases from the script and then re-initialise the replication.
The emphasis is on restore the database with scripts (i.e. Data Insert Scripts). It can't be faster than backup / restore. Re-initialisation of replication is secondary (& should be done when you have restored database).
October 17, 2011 at 6:05 am
Powershell is really good for calling SMO and scripting out the definitions; there's a ton of examples for that.
There's also some CLR examples as well as TSQL calling SMO via xp_cmdshell;
and i've done it purely via TSQL with a proc that i made for scripting tables; allt he other objects like procs/functions/views you can get from the definitions in sys.sql_modules
Lowell
October 17, 2011 at 6:13 am
via xp_cmdshell;
Please follow the discussion... I cringe every time I see a recommendation for xp_cmdshell....
http://www.sqlservercentral.com/Forums/Topic1186236-1526-1.aspx
October 17, 2011 at 6:15 am
Dev @ +91 973 913 6683 (10/17/2011)
via xp_cmdshell;
Please follow the discussion... I cringe every time I see a recommendation for xp_cmdshell....
http://www.sqlservercentral.com/Forums/Topic1186236-1526-1.aspx
oh yeah fully aware of that one...just throwing out possibilities, I didn't even want to post an example of doing it via xp_cmdShell.
I was tempted to jump in that thread as a devils advocate and procalim that xp_cmdshell is the best thing since sliced bread...:-D
Lowell
October 17, 2011 at 7:03 am
Dev @ +91 973 913 6683 (10/17/2011)
The requirement is:I want to script them out and in case of any failure restore the databases from the script and then re-initialise the replication.
The emphasis is on restore the database with scripts (i.e. Data Insert Scripts). It can't be faster than backup / restore. Re-initialisation of replication is secondary (& should be done when you have restored database).
He didnt suggest speed was the issue either.
October 17, 2011 at 7:13 am
He didnt suggest speed was the issue either.
I take it as non-functional requirements (by default). 😉
October 17, 2011 at 7:53 am
Roust_m (10/16/2011)
I don't want to backup them, so to avoid any performance impact, so I want to script them out and in case of any failure restore the databases from the script and then re-initialise the replication.
I'm trying to understand why you are worried about performance? I think that performance would be more affected by scripting out the data and restore data from that script. First, these are transactionally replicated, so the data is only as new/current as the publisher. Are you backing up the publisher database (I hope you are :)? Why not just restore from the publisher, or generate a snapshot (unless your data set is too large) and re-initialize?
Thanks,
Jared
Jared
CE - Microsoft
October 17, 2011 at 3:33 pm
Dev @ +91 973 913 6683 (10/17/2011)
I don't want to backup them, so to avoid any performance impact,
If it's just object definitions, scripting make sense else it (script for objects with data) would be worse on performance than backup / restore.
Yes, exactly what it is: only objects definitions
October 17, 2011 at 3:36 pm
MysteryJimbo (10/17/2011)
Dev @ +91 973 913 6683 (10/17/2011)
I don't want to backup them, so to avoid any performance impact,
If it's just object definitions, scripting make sense else it (script for objects with data) would be worse on performance than backup / restore.
Not really. Roust_m suggested he'd reinitialise therefore repopulating the data.
Roust_m (10/16/2011)
...and then re-initialise the replication.As always there are a number of ways you can do it. You can do it manually through SSMS or automate it.
There are tools to automate scripting or you could look at scripting it yourself. If it was me I was script it through powershell and use the opensource SQLPSX tools.
That said, what are your reasons for not wanting to perform backups? Also, if you reinitialise, are your articles configured to drop/recreate objects anyway as this is the default setting?
Either way would mean far less work. My preference would be to backup the subscriber as you could restore with relative ease without needing to reinitialise.
The failure may or may not happen. If it does happen, I will use scripts and re-initialise the data. I will save a lot of disk space on backups however together with the performance impact of the database backup. I may never have to restore, but the backups have to be run every day, so that is why I want to replace them with schema scripting only, as this is only secondary data.
October 17, 2011 at 3:38 pm
Dev @ +91 973 913 6683 (10/17/2011)
The requirement is:I want to script them out and in case of any failure restore the databases from the script and then re-initialise the replication.
The emphasis is on restore the database with scripts (i.e. Data Insert Scripts). It can't be faster than backup / restore. Re-initialisation of replication is secondary (& should be done when you have restored database).
No, sorry, you misunderstood: no data inserts, just schema restore and then replication re-initialisation IF the disaster happens.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply