Moving SQL Server db and associated classes

  • Hi Guys,

    This is my first thread and first of all let me ask you to pardon my ignorance as regards SQL Server. I have been using Ms Access all this time and now we have moved on...........Hence the post.

    I have an existing SQL Server db on one of my machine's local disk (C:\), (that system would be taken away), I need to port that whole database to the local drive (C:\ or D:\) of my laptop. Along with the data, I need to transfer all jobs, Stored Procedures, and the linked server.

    Both Servers are 2008R2, I have sa access on both.

    How best do I do this ?

  • Easiest way to move the database with the data inside would be to take a backup and then restore the database on the new server. Another way to do the same thing would be to detach the database, move the files and then attach them on the new server (see BOL).

    Stored Procedures are kept in the database and will therefore move with the options I mentioned above.

    Agents jobs can be scripted by right clicking the job inside Management Studio and select "Script job as". Copy the script and execute it on the new server.

    Don't forget to create the login for the moved database on the new SQL server.

  • And don't forget MSDB. That's where all your jobs etc are stored.

    Before the original server goes away, make sure you have a working SQL Server instance where you need it. Would be a shame to miss some pieces.

  • Thanks to you both, any idea about the Linked Server migration ?

  • you can script out the linked server definition in ssms

    'server objects' -> 'linked servers'.

    you need to edit the scripts to insert the correct passwords

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply