Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving SQL Server db and associated classes Expand / Collapse
Author
Message
Posted Saturday, January 11, 2014 6:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 16, 2014 12:01 AM
Points: 2, Visits: 5
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 ?
Post #1530030
Posted Saturday, January 11, 2014 1:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:40 AM
Points: 9, Visits: 287
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.
Post #1530054
Posted Saturday, January 11, 2014 3:01 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 809, Visits: 5,139
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.
Post #1530065
Posted Sunday, January 12, 2014 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 16, 2014 12:01 AM
Points: 2, Visits: 5
Thanks to you both, any idea about the Linked Server migration ?
Post #1530107
Posted Tuesday, January 14, 2014 3:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:15 AM
Points: 146, Visits: 2,153
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
Post #1530593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse