September 30, 2016 at 11:36 am
Hey all, I have a bit of a situation and wondering if someone can help me. I have 700+ DBs in QA which I need to restore (without the data) in DEV. Dev team needs just the structure and not any data in any tables. All those 700 tables have same sets of table. I can restore the DB and then truncate all tables which is the 1st option I thought of. 2nd option is to create the DB in Dev, generate the script of tables, views, SPs, Functions from Task->Generate script from QA and run that on dev which did work but I hate to do this manually for 700+ DBs. Any suggestions from expert on how to best tackle this?
September 30, 2016 at 11:42 am
You might (I'd test this against QA first) be able to use DBCC CLONEDATABASE for this. If you can do what I'm thinking, you'd get a nice, empty, read-only copy of the databases, which you could then either detach and copy to Dev, or backup and restore to Dev (then make them read/write and let the Devs have their fun.)
https://www.mssqltips.com/sqlservertip/4410/clone-a-sql-server-database-using-dbcc-clonedatabase/[/url]
Probably wouldn't take long to gin up a script to generate the DBCC commands for all the DBs, either.
September 30, 2016 at 12:14 pm
My apologies, the source server is running on 2008R2 and the destination server is on 2014
September 30, 2016 at 12:20 pm
No problem, I just saw it was in the SQL2014 forum and presumed.
Being on SQL2008, you're either going to need to script up something to auto-generate the scripts, find a product that does that for you and purchase (presuming there is such a thing, and I'd be surprised if not,) take a backup of each DB and manually truncate every table, or script out every object...
None of which will be easy.
I've not had to do something like this, generally I can give the developers backups of the databases and be done with it...
September 30, 2016 at 12:28 pm
Truncating tables is out of the question as of now I think.
So what I did was (just for 1 DB), I generated the script from the Source and executed the script on the destination (I had created a DB prior) and after the script ran successfully, I had pretty much all the objects except the data. Now I have to do it for 700+ DBs and I don't want to do it manually. Any advice on that.
October 2, 2016 at 6:05 pm
What if I save the DDL as .sql file and use powershell to execute that command from the location on each database? Will that work? If so, then what's the syntax since I have never powershell scripting.
October 3, 2016 at 4:26 am
If you only want to move all the structures and no data, I'd suggest taking a look at SQL Compare [/url]from Redgate (disclosure: my employer). If I had 700+ databases that I had to move all at once in this way, plus between versions of SQL Server, I'd script it all out using PowerShell and the SQL Compare command line. You could have it all done pretty quickly. I even wrote an article [/url]showing how to automate it using PowerShell. Based on all your requirements, that's what I'd do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 3, 2016 at 5:39 am
not used it myself but wondering if dacpac will help here
https://msdn.microsoft.com/en-gb/library/ee210546(v=sql.110).aspx
right click database, tasks, extract data-tier application
---------------------------------------------------------------------
October 3, 2016 at 6:01 am
george sibbald (10/3/2016)
not used it myself but wondering if dacpac will help herehttps://msdn.microsoft.com/en-gb/library/ee210546(v=sql.110).aspx
right click database, tasks, extract data-tier application
That could work, but you're going to want to automate it too in order to move 700+. I think there is a way to call out a dacpac through PowerShell, but I don't know it well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply