March 8, 2018 at 3:28 am
Good Morning Experts,
We have a request to elevate only the database structure from Dev to QA. Size of database is 10 TB and there are lot of objects(lakhs of tables, stored procedures etc). I tried Generate Scripts method but it is unable to script lakhs of objects. Is there any other way to elevate only the database structure(NOT DATA) .
March 8, 2018 at 4:02 am
coolchaitu - Thursday, March 8, 2018 3:28 AM. I tried Generate Scripts method but it is unable to script lakhs of objects.
Why not - what happens when you try?
John
March 8, 2018 at 4:28 am
John Mitchell-245523 - Thursday, March 8, 2018 4:02 AMcoolchaitu - Thursday, March 8, 2018 3:28 AM. I tried Generate Scripts method but it is unable to script lakhs of objects.
Why not - what happens when you try?
John
It is giving thousands of lines of code and Generate Scripts Wizard is executing for hours. I am surprised that a GURU like you is asking such a question. Did you never face this situation?
March 8, 2018 at 4:46 am
Ditch the attitude when you're asking for help, please. (I've never referred to myself as a "guru" - it's just a forum classification.)
No, I haven't faced this situation. I was wondering whether you were getting an error message, but it seems you're just frustrated with how long it's taking. Of course if you have hundreds of thousands of objects, you're going to get millions of lines of code when you script it out. And yes, it's going to take a long time. The only alternative I can think of is a backup and restore and then delete the data, but I suspect that that would be just as painful in its own way.
john
March 8, 2018 at 4:53 am
Have you taken a look at the PowerShell scripts at DBAtools.io?
After a quick glance I guess you can use the function "copy-dbasysdbuserobjects".
Edit: I missed the previous two posts. If the issue lies in the duration and the amount of generated lines, this won't solve your issue.
How are these objects created in the first place? Perhaps you can re-use that process again or extract the code from there?
March 8, 2018 at 5:06 am
John Mitchell-245523 - Thursday, March 8, 2018 4:46 AMDitch the attitude when you're asking for help, please. (I've never referred to myself as a "guru" - it's just a forum classification.)No, I haven't faced this situation. I was wondering whether you were getting an error message, but it seems you're just frustrated with how long it's taking. Of course if you have hundreds of thousands of objects, you're going to get millions of lines of code when you script it out. And yes, it's going to take a long time. The only alternative I can think of is a backup and restore and then delete the data, but I suspect that that would be just as painful in its own way.
john
Sincere Apologies John. My intention was not to make you feel bad.
March 8, 2018 at 5:33 am
I suspect that you are hitting a limit of the output in SSMS.
Try saving it to a file, and see if it completes without errors. It will take quite a while.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 8, 2018 at 8:19 am
Michael L John - Thursday, March 8, 2018 5:33 AMI suspect that you are hitting a limit of the output in SSMS.Try saving it to a file, and see if it completes without errors. It will take quite a while.
You can also try scripting it out one object type a time and see if that helps.
March 13, 2018 at 5:29 am
"lakhs of tables, stored procedures"
What are lakhs of tables?
March 13, 2018 at 6:39 am
William Rayer - Tuesday, March 13, 2018 5:29 AM"lakhs of tables, stored procedures"What are lakhs of tables?
It's an Indian thing. one lakh = 100,000.
See also "crore" - one crore = 10,000,000
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 13, 2018 at 6:45 am
Looks like a useful unit of measure for discussing UK property prices 🙂
March 13, 2018 at 6:28 pm
I know it's not helpful but having "lakhs" of database objects usually means that there's a pretty bad database and application design going on even if it's spread across several databases, never mind just one.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2018 at 9:47 pm
Jeff Moden - Tuesday, March 13, 2018 6:28 PMI know it's not helpful but having "lakhs" of database objects usually means that there's a pretty bad database and application design going on even if it's spread across several databases, never mind just one.
True Jeff. But, could you please help me with the solution
March 13, 2018 at 10:10 pm
coolchaitu - Tuesday, March 13, 2018 9:47 PMJeff Moden - Tuesday, March 13, 2018 6:28 PMI know it's not helpful but having "lakhs" of database objects usually means that there's a pretty bad database and application design going on even if it's spread across several databases, never mind just one.True Jeff. But, could you please help me with the solution
Have you tried what ZZMartin suggested above? Go ahead and select all the objects you want in the SSMS script generator but have each object create its own file.
The trouble is going to be when you try to use those scripts because of dependencies and any dependencies within SQL Server could be incorrect. Even if you could generate all the objects to a single file, there is no guarantee that they will be in the correct order.
Hmmmm... I've never tried it but maybe create a totally empty new database and do a RedGate SQL Compare between your 10TB db and the empty database to gen the difference script might do it. Pray that you don't have any circular dependencies.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply