-1

I need to copy an entire database (all tables and registers) from one server to another and keep it equal. Is there any script (it can be in PHP) that I can use to do this? For example, I read all the data from server 1 and insert in server2.

I've tried generate script (all inserts and creates) but the database from server 1 is too big and I can't execute on server 2.

I'm using SQL Server 2014 Management Studio.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Beatriz Sgavioli
  • 31
  • 1
  • 8
  • This should help! https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/ – N00b Pr0grammer Dec 11 '17 at 11:21
  • I've tried this but my database is too big and I can't execute that script in the new server. – Beatriz Sgavioli Dec 11 '17 at 11:25
  • 2
    Why don't you just do a backup and restore? Is there any actual need for this copy to be done by a script? (And I don't think PHP would be the best option anyway.) – underscore_d Dec 11 '17 at 11:29
  • Transactional Replication may be able to help you with this, depending what exactly you need to do. Also you mention "sql server management studio 2014." but actually it would be more useful to know the version of the backend SQL Server to understand what features you've got - you can use Management Studio with multiple versions of the server. – ADyson Dec 11 '17 at 11:33
  • Possible duplicate of [How best to copy entire databases in MS SQL Server?](https://stackoverflow.com/questions/79669/how-best-to-copy-entire-databases-in-ms-sql-server) or https://stackoverflow.com/questions/18866321/how-to-copy-sql-server-2008-r2-database-from-one-machine-to-another or etc. Did you search at all? If so, why did the many existing answers not resolve your problem? – underscore_d Dec 11 '17 at 11:34
  • And how I do that? – Beatriz Sgavioli Dec 11 '17 at 11:34
  • do what? Replication? It's a complex topic, but the feature is built into SQL Server. There are dozens of articles explaining the possibilities and different ways to implement it, if you do some research. That's why I said "depending exactly what you need to do". You can set it up in lots of different ways to try and suit your situation. – ADyson Dec 11 '17 at 11:36

1 Answers1

-1

Login to the SQL Server using management studio.

Right click on the Database that you want to copy and choose 

Tasks --> Generate Scripts

This will open the Generate and publish Scripts wizard. Now follow the below steps:

  1. Click next in Generate scripts for database objects window
  2. Choose radio button Select entire database and all database objects from Select the database objects to script in this page and click next.

  3. In Specify how scripts should be save or published window, choose Output Type as Save scripts to a specific location. Also choose Save to new query window from the below box

  4. Now click Advanced button to open Advanced Scripting options window Under General choose 'Schema and Data' for Types of data to script Under Table/View Options set everything to True and click OK to close the window

  5. Click Next in all following pages until Finish is enabled.
  6. Click finish and the entire database script will open in a new query editor in your SQL Server Management Studio
  7. If the generated script file is too big, try executing the script in batches. For example, execute the insert statement in batches
Shammas
  • 381
  • 1
  • 4
  • 15