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

Backup whole instance, restore to a new server, with all the logins and permissions intact Expand / Collapse
Author
Message
Posted Tuesday, May 11, 2010 11:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 13, 2010 11:43 PM
Points: 2, Visits: 8
Dear all,

This simple questions may have been asked by someone, but I've been looking through this forum for half an hour but still no clue.

The job is just as the title. there is an instance in a SQL server 2005, SQL01. We are going to migrate this whole instance to another SQL Server SQL02, while ensuring all the Logins and the linking to the database users and all the permissions are kept intact.

Please enlighten me

Thank you

Andrew
Post #920174
Posted Wednesday, May 12, 2010 4:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
Hi,

Script the logins from Master DB; Script the Jobs from MSDB;
Move the databases using either Detach\Attach or Backup\Restore to the new instance
Run the login script on Master
Create the jobs back using the script
If sny SSIS packages stored in MSDB, you may have to import them back.

Thank you


Renuka__
Post #920326
Posted Wednesday, May 12, 2010 5:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:27 PM
Points: 13,776, Visits: 28,178
You can backup & restore msdb between servers. Assuming the drives are the same, your Agent jobs should be ok. If the drives & whatnot are different, you'll need to go the scripting route in order to adjust the necessary scripts.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #920364
Posted Wednesday, May 12, 2010 9:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 9, 2010 4:10 AM
Points: 70, Visits: 117
Hi

We've just done this exact process, ie move all our DB's from 1 2005 box to another and I found a login script that worked very well. Not only does it create all the logins but it keeps the passwords too and it also sets the db access correctly.
I won't paste it on this forum as not sure what the protocols are? Are we allowed to do that?

The other replies are what we did too ie.
Stop the SQL service on old server
Detach DB's
Move the mdf and ldf files to new server
Attach DB's
Run login script
Create the jobs running in SQL Agent on the new server
Create new backup and maintenance plans and schedule them. Don't leave this for a week like we did before someone thought about it....
Go to the pub (this one is optional)
Post #920605
Posted Wednesday, May 12, 2010 10:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 9, 2010 4:10 AM
Points: 70, Visits: 117
Rather than post the script I've found the link - see below.
http://support.microsoft.com/kb/246133/

Hope it helps
Post #920640
Posted Thursday, May 13, 2010 1:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 6,423, Visits: 13,818
Grant Fritchey (5/12/2010)
You can backup & restore msdb between servers. Assuming the drives are the same, your Agent jobs should be ok. If the drives & whatnot are different, you'll need to go the scripting route in order to adjust the necessary scripts.


system database collations can be an issue here, ensure they match (I.e. Master, model, msdb). It's almost certain you will still end up amending some jobs.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #921034
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse