Q

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

 

For More Information

This was first published in June 2001

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close