| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2007-08-28 : 11:24:08
|
| Env.: SQL Server 2000 on Windows 2003I have two servers: 1. Production with 20 or more databases (number of databases is varying and is being increased or decreased)2. DR box. I need to replicate the production into the DR box every night. For this I have implemented a mechanism that drops all DBs in the DR box and restores latest backups from production. I also maintain the user rights for each object in DR box. So, next day the DR box has all the user databases with appropriate access rights. For now, the logins has to be created in DR box manually. Also the jobs has to be created manually. I want to automate this. Thought if I can restore Master and MSDB every night, then my problem would be resolved. Is this correct? If that is correct, then I have two plans:Plan A: Backup production’s Master and MSDB and restore on DR box. How can I restore these?Plan B: Implement a transactional replication that replicates Master and MSDB into DR box. Any document for this? How to implement.Thanks,Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-28 : 11:46:01
|
| Are the paths the same on both boxes, meaning the location where the database files reside? If so, you can easily do this by just copying the mdf and ldf files of all of the databases. This will mean you'd have to stop the services briefly on both servers though. Perhaps you could take this hit one a week for the system databases though.You can't replicate data out of master or msdb.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2007-08-28 : 12:06:36
|
| The path is the same but different drive.Do you have any link to restore Master and MSDB with notice to above fact?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-28 : 12:10:30
|
| No, but I've done it enough times that I should write my own article.The paths and drive letters need to be exact for it to work.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-08-28 : 12:27:30
|
| Looking for link(s) to restore Master and MSDB... ANY HELP?I found one link to restore Master on same machine but not sure it works for my case that files are on different path.Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-28 : 12:30:04
|
| I said no in my last post.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-08-28 : 12:43:31
|
| Sorry Tara. I left that for other readers.Canada DBA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 22:06:54
|
| You can copy logins and jobs with dts tasks. |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-08-29 : 08:48:18
|
Good idea! I didn't know it can be done that easy. Thanks.quote:
Originally posted by rmiao
You can copy logins and jobs with dts tasks.
Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-09-04 : 11:54:19
|
| Using DTS package, I scheduled a job on destination server to copy production logins and jobs every night. The jobs are being transferred but are disabled at the target server (which is good). I can enable the jobs later whenever I need. I have a problem with those jobs that refer to a maintenance plan. The maint plan does not exist on the targer server. How can I copy the maint plans?Canada DBA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-04 : 23:31:47
|
| You need to recreate plan. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 04:11:17
|
| Why not make the drives / paths identical on both machines?For a DR machine I would insist on that - sooner or later someone is going to build some import tool, or the like, that relies on hard-coded drives and paths! and that is going to get hosed when the DR machine takes overThen you can just copy the files over, as Tara describedKristen |
|
|
|
|
Replicating system databases