<p>I need to copy about 40 databases from one server to another.  The new databases should have new names, but all the same tables, data and indexes as the original databases.  So far I've been:  </p>

<p>1) creating each destination database<br>
2) using the "<a href="http://msdn.microsoft.com/en-us/library/ms140052.aspx" rel="noreferrer">Tasks->Export Data</a>" command to create and populate tables for each database individually<br>
3) rebuilding all of the indexes for each database with a SQL script  </p>

<p>Only three steps per database, but I'll bet there's an easier way.  Do any MS SQL Server experts out there have any advice?</p>

[{'answer_id': 79673, 'author': 'dimarzionist', 'author_id': 10778, 'author_profile': 'https://Stackoverflow.com/users/10778', 'pm_score': 2, 'selected': False, 'text': '<p>Backup -> Restore is the simplest, if not to use the replication.</p>\n'}, {'answer_id': 79679, 'author': 'X-Cubed', 'author_id': 10808, 'author_profile': 'https://Stackoverflow.com/users/10808', 'pm_score': 1, 'selected': False, 'text': '<p>Backup the databases using the standard SQL backup tool in Enterprise Manager, then when you restore on the second server you can specify the name of the new database.</p>\n\n<p>This is the best way to maintain the schema in its entirety.</p>\n'}, {'answer_id': 79682, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>use backups to restore the databases to the new server with the new names.</p>\n'}, {'answer_id': 79687, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': '<p>Maybe the easiest is to detach/reattach. Right-click in the server manager on the DB, tasks --> detach. Then copy the MDF/LDF files to the new server and then reattach by clicking on the server icon and tasks-->attach. It will ask you for the MDF file - make sure the name etc is accurate.</p>\n'}, {'answer_id': 79691, 'author': 'Leon Bambrick', 'author_id': 49, 'author_profile': 'https://Stackoverflow.com/users/49', 'pm_score': 7, 'selected': True, 'text': "<p>Given that you're performing this on multiple databases -- you want a simple scripted solution, not a point and click solution.</p>\n\n<p>This is a backup script that i keep around. \nGet it working for one file and then modify it for many.</p>\n\n<pre><code>(on source server...)\nBACKUP DATABASE Northwind\n  TO DISK = 'c:\\Northwind.bak'\n\n(target server...)\nRESTORE FILELISTONLY\n  FROM DISK = 'c:\\Northwind.bak'\n\n(look at the device names... and determine where you want the mdf and\nldf files to go on this target server)\n\nRESTORE DATABASE TestDB\n  FROM DISK = 'c:\\Northwind.bak'\n  WITH MOVE 'Northwind' TO 'c:\\test\\testdb.mdf',\n  MOVE 'Northwind_log' TO 'c:\\test\\testdb.ldf'\nGO\n</code></pre>\n"}, {'answer_id': 79703, 'author': 'stephbu', 'author_id': 12702, 'author_profile': 'https://Stackoverflow.com/users/12702', 'pm_score': 2, 'selected': False, 'text': "<p>In order of ease</p>\n\n<ul>\n<li>stop server/fcopy/attach is probably easiest.</li>\n<li>backup/restore - can be done disconnected pretty simple and easy</li>\n<li>transfer DTS task - needs file copy permissions</li>\n<li>replication - furthest from simple to setup</li>\n</ul>\n\n<p>Things to think about permissions, users and groups at the destination server esp. if you're transferring or restoring.</p>\n"}, {'answer_id': 79715, 'author': 'Kevin Sheffield', 'author_id': 590, 'author_profile': 'https://Stackoverflow.com/users/590', 'pm_score': 0, 'selected': False, 'text': '<p>Redgate SQL Compare and SQL Data Compare.  The Comparison Bundle was by far the best investment a company I worked for ever made.  Moving e-training content was a breeze with it.</p>\n'}, {'answer_id': 80221, 'author': 'MotoWilliams', 'author_id': 2730, 'author_profile': 'https://Stackoverflow.com/users/2730', 'pm_score': 2, 'selected': False, 'text': '<p><strong><em>There are better answers already but this is an \'also ran\' because it is just another option.</em></strong></p>\n\n<p>For the low low price of free you could look at the <a href="http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&amp;displaylang=en" rel="nofollow noreferrer">Microsoft SQL Server Database Publishing Wizard</a>.  This tool allows you to script the schema, data or data and schema.  Plus is can be run from a UI or command line &lt;- think CI process.</p>\n'}, {'answer_id': 82212, 'author': 'Mike L', 'author_id': 4796, 'author_profile': 'https://Stackoverflow.com/users/4796', 'pm_score': 2, 'selected': False, 'text': '<p>If you use the Backup/Restore solution you\'re likely to have orphaned users so be sure to check out <a href="http://technet.microsoft.com/en-us/library/ms175475.aspx" rel="nofollow noreferrer">this article</a>&lt;microsoft&gt; on how to fix them.</p>\n'}, {'answer_id': 1064956, 'author': 'Yordan Georgiev', 'author_id': 65706, 'author_profile': 'https://Stackoverflow.com/users/65706', 'pm_score': 0, 'selected': False, 'text': '<p>Check those links:</p>\n\n<ul>\n<li><a href="http://ysgitdiary.blogspot.com/2009/06/backup-sql-server-2005-2008-databases.html" rel="nofollow noreferrer">For multiple db\'s backup</a></li>\n<li><a href="http://ysgitdiary.blogspot.com/2009/06/restore-db-from-file-on-sql-server-2005.html" rel="nofollow noreferrer">and single db restore</a></li>\n</ul>\n'}, {'answer_id': 1818024, 'author': 'Doug', 'author_id': 115749, 'author_profile': 'https://Stackoverflow.com/users/115749', 'pm_score': 2, 'selected': False, 'text': '<p>Another one to check out that is quick and simple:</p>\n\n<p>Simple SQL BULK Copy</p>\n\n<p><a href="http://projects.c3o.com/files/3/plugins/entry11.aspx" rel="nofollow noreferrer">http://projects.c3o.com/files/3/plugins/entry11.aspx</a></p>\n'}]
['https://Stackoverflow.com/questions/79669', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/13728/']