Database developers should not create stored procedures or test applications on a production server. Instead, a SQL database is set up on a test environment. However, developers create several stored procedures and tables on the development server. Moving these objects can be a tedious task. Microsoft SQL Server has a wizard that allows database administrators to script the new objects and the data. The scripts can be run on the target machine, making the transfer of objects and data a quick process.

Copy a SQL Database to the Script File

By default, the SQL database server only scripts the table and stored procedure objects. If you want to transfer data, you need to set the option in the scripting wizard. These steps script all objects and data, and they copy a SQL database to the console window for review before you run it on the production server.

  1. Right-click the SQL database to copy. From the list of options, select “Tasks” and then select “Generate Scripts.”
  2. Click the “Next” button at the opening screen. The next window asks the administrator what SQL database to script. Although the database was chosen at the beginning, SQL server asks for the database selection again. Press the “Next” button.
  3. Scroll down to the section labeled “Table/View Options.” In this section, notice the drop down box next to “Script Data.” Select “True.” This option tells the script wizard to include the SQL data along with the tables. Click the “Next” button.
  4. Choose the objects to script. To copy a SQL database, the stored procedures, tables, triggers and views are the very basic components. Select all the check boxes to transfer all the database data. Click the “Next” button.
  5. Select the tables to transfer. To avoid problems with the transferred data, it’s best to transfer all the tables used on the production server. Be careful not to overwrite tables on the production server, or you can accidentally delete production data, which is usually an issue that requires a complete restore of the SQL database. Click the “Next” button.
  6. Select “Script to New Query window.” This option lets the administrator view the data and code within the SQL Management Studio window.
How to Copy a SQL Database: Transfer Data from One Server to Another
How to Copy a SQL Database: Transfer Data from One Server to Another

Run the Transfer SQL Script on the Production Server

Review the file created by the SQL server. If the database contains a lot of information, this file can be long. Switch the SQL server focus in Management Studio to the production server. If a connection hasn’t been made yet, click on the “Connect” button. Copy the new script to a query window for the production server. Press the F5 key to run the script. It may take several minutes for the script to run, but once it’s finished, a SQL database is copied.

Use this method to quickly copy a SQL database. This is beneficial for smaller database. If the SQL tables contain millions of records, it might be too much for the query window and the Management Studio console may crash. However, the administrator can still copy tables one-by-one and then transfer the data later.