June 5, 2009 at 11:19 am
We have a Server with Sql Server 2000 installed on it. It has 5 user databases, about 40 syslogins and 36 sql server agent jobs.
We have backed up on tape, backups of all 5 user databases, master,msdn and model databases. We also have a script that scripted all jobs.
If our original server dies for some reason and we have to restore to a brand new server, would what we have on tape be enough to create everything and get us going?
We do not know passwords for msany of the logins and also what kind of permissions they have, would we need to do something separately, like creating a script etc for that or will the backups cover us for that.
This is what i think we would do to build our databases on a brand new server, please correct and/or advise better ways if it is incorrect or not efficient:
1. Install Sql Server 2000 on the new server. The new sever should have the same drive names as the original server. Turn Sql Server and Sql Server Agent services on.
2. The sql server installation will have cretated Master, model and msdn databases. So we would restore on top of these from our backup files, the master and msdn databases in that order.
3. Restore all 5 user databasesfrom backups.
4. Run script to create all the jobs.
5. What about user logins and permissions? do we need to do something about that?
Please let me know if this is ok or not and what other steps need to be taken to get the system back so it can be used byall users as they were able to.
Thanks and appreciate all inputs.
BV
June 5, 2009 at 2:25 pm
The process you describe would recover your system. As you are restoring the system databases you would automatically get all logins and jobs back and would not have to run the scripts..
you would need to install SQL up front to exactly the same directory structure and same version level.
Post the SQL install you need to run the following to be able to edit the agent jobs going forward:
update msdb..sysjobs set originating_server = 'newservername'
and then update sysservers in master:
exec master..sp_dropserver 'OldServerName'
go
exec master..sp_addserver 'NewServerName', local
go
An improvement to this process would be when you next have SQL stopped on the current machine, copy off the system database files to another directory, which can then be backed up to your tape. When you build the new machine, slide its system database files out of the way (don't delete them) and move your saved ones into place. SQL will then start without having to go thru system database restore process.
It is still worth having your logins scripted out. Google sp_help_revlogin. This scripts them out with their passwords (encrypted) and maintains the sids.
edit: thinking about this more clearly, have the system database files available but don't slide them into place unless they are very recent, as you still want to restore from backup to get the latest data.
---------------------------------------------------------------------
June 5, 2009 at 2:34 pm
Also, for above , you would then need to alter the application to point to the new server
By the way, should you upgrade to SQL2005 or 2008, the process above would work but there would be problems with the installation afterwards.
My recommendation then would be to rename the server after the process to that of the original server (including updating server name entries in the registry).
---------------------------------------------------------------------
June 5, 2009 at 3:33 pm
Thanks a lot George for your valuable advise.
Just one thing...as we have not done this before. Are there any possible problems when we install sql server and restore on top of the new master, msdn and model databases the backups from our old server? Also should we restore in that order (Master first) or does it not matter?
Thanks,
BV
June 5, 2009 at 4:42 pm
As long as you replicate the directory structure exactly and have the correct version of SQL installed (check with select @@version) you should be fine.
Yes, you must restore the master database first, SQL server must be in single user mode to do this (Books Online and articles on the web document this process fully). After restoring the master database your user databases will show up in SQL but be marked suspect, this is because SQL is now aware these existed but the database files are not in place yet. Restoring the databases will fix this.
Test the process out on a regular basis (once or twice a year) so its second nature if you have to do it for real, and document it fully.
Good luck.
---------------------------------------------------------------------
June 5, 2009 at 4:52 pm
Thanks George!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply