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:
- Stop the sql server services using the Service Manager
- Open a command window
- Start sqlserver in single user mode using the sqlservr.exe -m command from a command prompt
- Open a second command window connect to sqlserver using osql
- RESTORE the master database
- Use the SHUTDOWN command to shutdown SQL Server
- 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.
- Using DTS create a package that will extract data from a table, view, or SELECT statement to a comma delimited text file
- 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
- 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.
This was first published in June 2001