Ask the Expert

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?

    Requires Free Membership to View

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
  • SHUTDOWN
  • RESTORE

 

For More Information

This was first published in June 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: