Search code examples
sqlsql-serversql-server-2005database-migrationmanagement-studio-express

SQL 2005 Moving from Godaddy SQL Server to a company owned server

What is the best way to go about moving a database from a Godaddy SQL 2005 account to a local SQL 2005 server? I have access to the DB through Server Management Studio Express and also through the Godaddy SQL explorer.

However, I have no idea where to start. In MySQL, I would just export the data through the PHPMyAdmin page, and conversely import it on the other server using an ASCII file. In access, I'd just use the migrate tool. In Server Management Studio, I've thought about using the "Backup" and "Restore" method, but I'm afraid that I won't have the ability to create a new object with the correct permission schema on the new server.

What are your thoughts? Keep in mind that I do not have access to the MDF or temp files. I've been studying this page.

Solution

  • SMSS should be able to do it for you. If you are looking to move the entire database and not just a few tables, the Backup and Restore method is probably your best bet.

    Your steps might include:

    1. connect to Godaddy sql server in ssms
    2. Right click the database you want to move and select Tasks > Backup
    3. Keep/set Backup type = "Full" and add a destination at the bottom that you will be able to access.
    4. After backup completes, move the .bak file to a location that your local sql server can see.
    5. Connect to your local sql server in ssms.
    6. Right click Databases > Restore Database
    7. Enter the database name you want in "To Database:"
    8. Select "From device:" and locate the .bak file you created before.
    9. In the row that shows up in the grid display, check the restore check box.
    10. If it matters to you where the recreated files will be stored, select options on the top left menu and confirm the file locations under the "Restore As" column in the data grid.
    11. Click ok and the restore should start.
    12. To migrate users, follow the directions at http://support.microsoft.com/kb/918992