April 2, 2008 at 10:59 am
Hi there,
I tried searching the forums here just incase this question has been posted in the past, but the search function doesnt seem to work very well. I am using SQL Server 2005 Enterprise.
Currently I am taking full nightly backups of all databases on my server. In the event of a disaster that my whole server blows up and needs to be entirely rebuilt, how would I go about doing this?
This is what I think needs to be done:
Acquire new server
Install OS
Install SQL Server
Restore System databases from backups
Restore User databases from backups
Is this all that needs to be done?
Should System databases be restored before User databases, or vice-versa?
Will this restore all the server level logins, maintenance plans, jobs, etc.?
April 3, 2008 at 3:34 am
Hi,
You're certainly on the right track. Be sure to build your new server to be as close to the current server as possible - ie same drives, same OS/SPs same SQL Server edition/SPs. If drives letters will be different, you'll need to restore with the WITH MOVE option (Check BOL for RESTORE syntax). Make sure all shares and permissions are replicated too. Search BOL on 'Backing Up and Restoring System Databases' - that'll give you a good starting point - and yes, restore system DBs before user DBs. Restoring msdb will bring all your jobs across. You may also need to map orphaned DB users to logins - check out sp_change_users logins in BOL. Hope that helps to get you started.
Cath
April 3, 2008 at 5:09 pm
great! thanks for the reply.
One related problem that I am having...
I have created a 2nd instance on my test server.
I am trying to restore the backup of the master database of the 1st instance onto the 2nd instance, it keeps crashing the instance and am not able to start it back up again.
When restoring a master database, does name of the destination instance have to match the name of the source instance where the backup was taken?
What I am trying to mimic here is a scenario where my entire server dies, and I have to restore everything to a standby server (i guess in a real scenario, the standby would take the same name...).
April 4, 2008 at 12:23 am
Hi,
I've restored master in the scenario you suggest, ie a new server with the old server's name, but never tried to restore to a different named instance, so I don't know. Would it be feasible for you to script out the logins/permissions?
Cath
April 4, 2008 at 10:12 am
I never had a crash. Might be that a backup file is bad or some event handlers (autostart jobs) that reference the other instance, so if other instance is not reachable, it freezes for some time. When restoring a user database, the instance name doesn't matter, so I guess the same for master database.
RESTORE DATABASE Mydb FROM DISK = N'fullpath\Mydb'
WITH MOVE 'Mydb_Data' TO 'path\Mydb.mdf',
MOVE 'Mydb_Log' TO 'path\Mydb.ldf'
April 9, 2008 at 2:09 pm
all suggestions are good, but I did not see anyone making the suggestion to have redundancy by having another server and having a standby database or for that matter replication of the database will also work.
"We never plan to Fail, We just fail to plan":)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply