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

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

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL Server gurus are waiting to answer your toughest questions.