April 19, 2017 at 3:03 am
Help appreciated here.
I need to make a copy of a no. of SQL Server DDBBs I've been working on (along with other colleagues), version SSIS 2005. I have admin status or close on these DDBBs, so my problem is not about the lack of permissions.
Unfortunately the employer setup of the network doesn't allow for backing up the DB to an accessible drive (i.e. I can back up the DB, but haven't got access to the drives where the backup is stored. That facilitates backups/restores, but not transferring the information somewhere else) and later restoring it to a separate device/pc/network.
I'm not *particularly* interested in the data itself, but in the DB design and internal elements. That is, simply put I would like to copy all the key elements in the DB (mainly table definitions + views + stored procedures & functions; anything else (data, synonyms, security) would be welcome if easy but is not really needed), in a quick and efficient way.
I've explored the DB contextual menu (CREATE TO/ DROP TO), but I'm not really familiar with it. Also it seems you can only script one type of element each time (.e.g. table creation, but not Views + stored procedures in one command). I'm unsure about the command Tasks > Copy Database (see img), how is that different from a backup?
Any advice on best way to do this? Thanks in advance, p.
April 19, 2017 at 3:12 am
In SSMS object explorer, right click the database -> tasks -> generate scripts. That will let you script tables, views, procedures, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2017 at 3:44 am
Thanks GilaMonster, that worked fine.
One last question: being this a SQL script, if I run this .sql on a new system, would that recreate from scratch all the elements: DB creation -> tables-> views -> sp, respecting dependencies/order?
Or is it more like a repo where all elements are stored, and one has to select some elements and use them carefully to recreate previous structure?
Thx
April 19, 2017 at 4:15 am
a_ud - Wednesday, April 19, 2017 3:44 AMOne last question: being this a SQL script, if I run this .sql on a new system, would that recreate from scratch all the elements: DB creation -> tables-> views -> sp, respecting dependencies/order?
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply