1

I have a one problem. I have a database(MSSQL) on some server. I need copy this DB on my local DB. But! This DB is huge 7gb. I don't need data from this DB. I need just copy structure of this DB (What I mean about structure - all DB, all tables, connections, etc. (!!!But I don't need a data from this tables)). How I understand it - It will clean DB with the same structure. I have no idea how implement it. I read what I can do it using PowerShell, Git and SQL manager. But i didn't find example. Could you tell me something about it?

Кирилл Филободченко
  • 15
  • 11
  • 1
    *"This DB is huge 7gb"* 7GB is *not* a big database. That, is honestly, pretty small in real world terms. If you just need to structure, do you not have this in your database project already? Alternatively, why not use Generate Scripts tool in SSMS? Alterantively, as it's just the schema, have you considered a DACPAC? – Thom A Jul 16 '20 at 12:12
  • https://devblogs.microsoft.com/scripting/use-powershell-to-script-sql-database-objects/ – Vivek Kumar Singh Jul 16 '20 at 12:15

2 Answers2

2
  1. Right click on your Database => Tasks => Generate Scripts

Generate scripts

Screenshots shows this on a System Database. Don't do this on system databases.

  1. Select the Tables / Views / Stored Procedures you want.
  2. Change the scripting options depending on your preferences/requirements.
  3. Generate the script
Preben Huybrechts
  • 5,853
  • 2
  • 27
  • 63
  • I need copy all of my tables. I selected "Script entire databases and all database objects". But What I need to select on a next step? "How scripts should be save" If I understand it correctly after save this file I can just open this script in my local DB, right? If it's right, what is best idea to save as? – Кирилл Филободченко Jul 16 '20 at 12:26
  • The best idea is file, since if you have many tables, you'll probably go out of memory if you choose clipboard. You can than load the file and execute it against localdb in your c# code. – Preben Huybrechts Jul 16 '20 at 12:27
  • How Could I implement it in Visual Studio? Thanks for help – Кирилл Филободченко Jul 16 '20 at 12:31
  • You can connect like [this](https://www.sqlshack.com/how-to-connect-and-use-microsoft-sql-server-express-localdb/) Or you could do something like [this](https://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp) – Preben Huybrechts Jul 16 '20 at 12:35
0

If you use MS SQL Server Management Studio, you can right-click on the tables you want to have in your other db -> Generate Script for -> Create -> and then choose one opion.

Only drawback is, that you have to do that for every table individually.

DaedraEYE
  • 51
  • 7
  • *"Only drawback is, that you have to do that for every table individually."* no you don't. Just select "Script enitre database and all database objects" – Thom A Jul 16 '20 at 12:12
  • @Larnu Oh, nice info. Didn't know that option existed. Thx – DaedraEYE Jul 16 '20 at 12:19