How to migrate a MS-SQL database to another MS-SQL database?
To migrate from Microsoft SQL database to another Microsoft SQL database, open the enterprise manager on the Microsoft SQL server where the data will be imported to and perform the following steps:
- Create a new database.
- In the General tab > Name, enter AppStream.
- In the General tab > Collation name select SQL_Latin1_General_CP1_CI_AS.
- In the Data File tab under File properties check Automatically grow file.
- In the Data File tab under File growth, select In megabytes and enter 50.
- In the Data File tab under Maximum file size, select Unrestricted file growth.
- Repeat the same above three entries under the Transaction Log tab.
- Go to Security > logins.
- Create a new login with the name as user, choose SQL Server Authentication and use the password "change on install” (make sure to type this with the spaces). Choose the AppStream database.
- Go to the Database Access tab and check the AppStream check box.
- Click OK.
- Open the Data Transformation Services Import/Export Wizard from the computer where the data will be exported from (it is also possible to open it from any machine that has the Microsoft SQL tools installed).
- Choose the server from where the data will be exported. Use SQL Server Authentication. Choose the AppStream database. Click Next.
- Choose the server to where the data will be imported. Again, use SQL Server Authentication, choose the AppStream database and click Next.
- Choose Copy objects and data between SQL Server database.
- Use default options in the Objects to copy page.
- Use the default option in the next page as well.
- After importing the data open the da.conf under C:\Program Files\AppStream\awe\streamletEngine\da\conf, go to the “Microsoft SQL Server 2000 Configuration”. Change the appstream.database.host parameter to point to the new database.
Note the following:
- It’s recommended to stop all AppStream services before starting to export the data and then restart the machine after importing the data to the new SQL server.
- Keep the old database up and running until it is possible to connect to the new database.
- If an instanced name for the database was used, make sure that the same name is used in the database where the data is imported.