Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312

    Unanswered: SQL Server 2005 Newbie

    I am relatively new to SQL Server and I have 2005. I am developing a database with an Access ADP frontend. The development is taking place on my laptop running a separate instance of SQL Server. I am trying to put the database on the production server and I am not sure how to proceed.

    My laptop is not part of the network so I need to create a file and then import it on the SQL Server instance running on the production server. I thought I could do a backup and restore, but apparently that is not working.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by DCKunkle

    Any help would be greatly appreciated.

    Ugh. You have some options. I am not well-versed with SQL 2005, but the steps should be pretty similar.

    You can:
    1. Backup the database on your laptop and copy the resulting backup file to the target database server. Then restore the backup file.

    2. Detach the database from the laptop, copy the .mdf and .ldf files (from the data directory) to the target database server and re-attach them.

    3. Shut down your laptop's SQL instance, and copy the .mdf and .ldf files to the target database server. Then attach them.

    4. Finally you could try using DTS to copy objects to the new database server. I'm having a lot of trouble with the new version of DTS (SSIS), so I won't advocate this route.

    The logins on the target database will not match the logins on your laptop. In SQL 2000, there is a stored procedure called 'sp_change_users_login'. The help on my SQL 2005 install is somehow corrupted and I can't verify that it works the same way now as in 2000. Basically, it relinks a user in a database with the login on the server.

    After you have moved the database, you will need to update the connection in the Access Data Project. Look under File | Connection to set the new properties.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is it an empty db? If so another option would be to script it out and run the script from management studio? I presume you can still generate a create db script in 2005....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The database is not currently empty, but it is just test data. So that definitely is an option. But I would rather not have to recreate it. The 'production' environment is technically not live yet. We are converting from staight Access to an Access and SQL Server environment.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Thanks for the help! I went the copy the files and attach route.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •