Problem solve Get help with specific problems with your technologies, process and projects.

Restoring backups to a different server and creating a text file for bulkcopy

Can I recreate the logins, databases, transaction logs, maintenance plans and jobs on the new SQL server either by attaching mdf and ldf files (copying them to new server) from old server or by restoring the *.bak files of all the sys and user databases. How do I create a text file so I can use that text file for bulkcopy or to transfer data from one place to another?

It is possible to restore the backups to a different server; however, the code page and sort orders of the two servers should be the same. The first database to restore is master (it contains database and user login information). To restore the master database:

  1. Stop the sql server services using the Service Manager
  2. Open a command window
  3. Start sqlserver in single user mode using the sqlservr.exe -m command from a command prompt
  4. Open a second command window connect to sqlserver using osql
  5. RESTORE the master database
  6. Use the SHUTDOWN command to shutdown SQL Server
  7. Restart sql server services using the Service Manager

The remaining databases can be restored using Enterprise Manager; the jobs are in the msdb database while maintenance plans and transaction logs are part of the user database backups. Verify database users are mapped to the appropriate SQL Server accounts using the sp_change_users_login stored procedure.

There are two methods for creating a text file for bulkcopy.

  1. Using DTS create a package that will extract data from a table, view, or SELECT statement to a comma delimited text file
  2. Using Query Analyser modify the output to use comma separated values, run a query, save the output

For further information look in Books On Line for:

  • Restoring the master database from a current backup
  • osql Utility


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.