• Advertisement
Advertise Here!
Get exposure and get noticed!
We offer affordable advertisement options, click Here to get more information or visit the "How to Advertise on IT Notebook" under the Information section.

Any - Microsoft SQL Server 7+ - Exporting and importing databases

Any software title released for Windows. Articles are posted in "Windows Version - Software Title - Subject" format.

Any - Microsoft SQL Server 7+ - Exporting and importing databases

Postby lmmtux » Tue Aug 28, 2012 12:15 pm

There are a few different ways to backup, copy and move databases with Microsoft SQL Server. The method here works the best in most cases and offers a complete solution to back up a database, or to copy/move it to another server.

Exporting databases:

  1. Open SQL Management Studio.
  2. Locate the database to export, right-click it and select Tasks / Generate Scripts.
  3. A wizard will come up to guide through the following steps to do the export.
    • Again select the database and click Next.
    • Script Options: leave the defaults and click Next.
    • Object Types: select Tables. If you need to copy the Users associated with the database check Users also.
    • Choose Tables: select all and click Next.
    • Output Option: select "Script to file" and set the destination .sql file to save as.
    • Script Wizard Summary: review the options and click Finish to generate the .sql file.

Importing databases:

  1. Open SQL Management Studio.
  2. Create a new empty database to import to.
  3. Select the new empty database you created.
  4. Go to the File menu, then Open, then File, and select the .sql file you wish to import from.
  5. Wait while the .sql file is opened for viewing.
  6. When you are ready, click the Execute button which will run the SQL code to import the data.
  7. If you get an "out of memory" error, you can perform the following steps to import based on a commandline tool which handles larger .sql files better than the Management Studio.
    • Open the command prompt.
    • Run the following command, by substituting the databasename, sqlfile.sql and log.txt with your values.
      Code: Select all
      sqlcmd -E -d databasename -i sqlfile.sql -o log.txt
    • The log file will be dumped in the directory where you executed the command above.
Posts: 55
Joined: Mon Jul 30, 2012 9:40 pm
Reputation: 0

Return to Microsoft Windows


  • Related topics
    Last post