June 13, 2014 at 12:32 pm
Hi,
What is the best method to copy everything (including jobs, maintenance plan, alerts) to different server?
I would appreciate the steps to do this.
Thanks you
June 22, 2014 at 7:33 am
hi,
I think the best way to do it is: move all the databases to the new instance.
For user databases:
If the file paths are the same you can just copy the pysical database files.
If the file paths differ you can still copy the files but use the sp_attach procedure to define the new files paths.
In any case take the original intance offline first!
For moving the system databases - they inlcude logins, jobs and that stuff - have a look at books online how to do it. You need master, msdb and model database. TempDB ist not needed.
If you have SSIS-packages on your instance it depends. When having them stored in msdb-database you'll have your packages after moving the system databases.
Otherwise I'm not sure how to transfer them.
If you have different tools like replication, Service Broker or others take special care on each separate tool.
June 23, 2014 at 4:00 am
WolfgangE (6/22/2014)
For moving the system databases - they inlcude logins, jobs and that stuff - have a look at books online how to do it. You need master, msdb and model database. TempDB ist not needed.
The steps to move the system databases apply to moving the database paths on an existing instance. They do not support moving them to another instance, you should not do this as it can cause a whole range of issues.
smtzac (6/13/2014)
Hi,What is the best method to copy everything (including jobs, maintenance plan, alerts) to different server?
I would appreciate the steps to do this.
Thanks you
Migrate all objects from the source to the target server. Logins, agent jobs, etc can be scripted and moved. SSIS packages will need to be exported and then re imported, otherwsie just store them on the filesystem
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 24, 2014 at 2:43 pm
hmmm ... moving system databases to another server ...
hmmm ... should not be attempted ???
I would call that Disaster Recovery !
any decent DBA should be able to recover all system and user databases to another SQL Server WITHOUT the same name and have everything work correctly when they have completed things.
It is just a matter of testing it !
It is really not that hard at all ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 24, 2014 at 5:23 pm
Rudyx - the Doctor (6/24/2014)
hmmm ... moving system databases to another server ...hmmm ... should not be attempted ???
I would call that Disaster Recovery !
any decent DBA should be able to recover all system and user databases to another SQL Server WITHOUT the same name and have everything work correctly when they have completed things.
It is just a matter of testing it !
It is really not that hard at all ...
The system databases are not designed to be moved between instances, there are all sorts of issues relating to agent job execution, instance encryption and the instancename.
Although it is possible to restore system databases on another instance (and i have done this myself before) its not something i would recommend especially to users who are not well experienced with sql server.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply