June 12, 2009 at 2:19 pm
Hi
I am using Computer Associates Arcserve Backup software , with WIndows SQL agent to backup a Web based database hosted on SQL.
I have a requirement to carry out a Disaster Recovery of the SQL Server onto new similar hardware as part of a business continuity exercise. The new server will be identical spec and will have the same server name.
After I have used the CA Arcserve DR process and all my data has been restored to my system and data drive - I then proceed to remove the SQL Server 2000 and then proceed to re-install SQL Server 2000 to the same original directory on the same drive. I then restore the Databases from tape to the original locations - but when I then try to access the application it fails.
I am new to SQL - can someone explain what I am doing wrong - or better still is there a step by step approach I should be following - I am sure I am not the only one who has had to carry out this task.
Thank you.
June 12, 2009 at 5:11 pm
Hi there,
I'm a little confused on the procedure you have used
After I have used the CA Arcserve DR process and all my data has been restored to my system and data drive - I then proceed to remove the SQL Server 2000 and then proceed to re-install SQL Server 2000 to the same original directory on the same drive. I then restore the Databases from tape to the original locations
This means that you have restored from aa Arcserve DR disk, uninstall and install SQL 2000 and then restore the DBs? what about the CA SQL Agent ?
What do you mean with this ?
but when I then try to access the application it fails.
If you take a backup using the CA SQL Agent, then you must restore it using a CA SQL Agent. Can you give us more details ?
Thanks,
Alejandro
Alejandro Pelc
June 13, 2009 at 4:06 am
auxinet07 (6/12/2009)
I have a requirement to carry out a Disaster Recovery of the SQL Server onto new similar hardware as part of a business continuity exercise. The new server will be identical spec and will have the same server name.
The question here is what will you do when your server fails? Are you planning to restore backups to the new server?
You have better high availability methods. I suggest you understand the concepts and implement the best that suits your requirement.
1. Log shipping
2. Replication
3. Clustering
Log shipping is fairly easy to implement(You can even use the secondary server for reporting).
Also what is the backup strategy and recovery model of your database?
After I have used the CA Arcserve DR process and all my data has been restored to my system and data drive - I then proceed to remove the SQL Server 2000 and then proceed to re-install SQL Server 2000 to the same original directory on the same drive. I then restore the Databases from tape to the original locations - but when I then try to access the application it fails.
Didnt get what you actually did here? Did you install SQL Server 2000 on the new server? Make sure IP address is same as old one. and i hope ur old server is disconnected (Assuming it failed)
I am new to SQL - can someone explain what I am doing wrong - or better still is there a step by step approach I should be following - I am sure I am not the only one who has had to carry out this task.
Instead of jumping to solution, i suggest you plan out your disaster recovery first. How much data can your company afford to lose in case of a disaster? Backup strategy? etc...
[/quote]
June 13, 2009 at 6:35 am
Alejandro Pelc (6/12/2009)
Hi there,I'm a little confused on the procedure you have used
After I have used the CA Arcserve DR process and all my data has been restored to my system and data drive - I then proceed to remove the SQL Server 2000 and then proceed to re-install SQL Server 2000 to the same original directory on the same drive. I then restore the Databases from tape to the original locations
This means that you have restored from aa Arcserve DR disk, uninstall and install SQL 2000 and then restore the DBs? what about the CA SQL Agent ?
What do you mean with this ?
I use the built in backup facilities within SQL Server 2000 to create a backup of the databases - I am assuming this is only backing up the system databases and not the user databases ? Remember I am new to SQL - can you confirm what is actually backed up by the built in backup facility within SQL Server 2000 ? The Arcserve backup routine then backups the entire drive c: and d: and then using the arcserve SQL for Windows agent presumably backups the SQL Server including the user and system databases ? Within CA Arcserve we also have the option set create a DR Session.
Using the backup tape to DR to a new server (off-line in a staged environment)
I use the Arcserve DR process to Disaster Recovery the two drives c: and d: to a new SQL server (the old server is still on-line - this is only a mock exercise). Please note that I have infact created a staged environment of all my Servers (my DC, Exchange and File Server) - I now in effect have a replica of my live environment off-site and not connected to the live environment - same IP addresses and same names etc.
Once I have disaster recovery the server drives c: and d: for the SQL server - I then take off the version of SQL that has been restored using the DR process which uses the CA Arcserve SQL agent and install a fresh copy of SQL server 2000 using the same destination directory structure as per the old drive ( I have read some articles via google which suggests this is the correct approach ?????) , after I have reinstalled SQL Server 2000 again - I then restore the backups of the system database etc to their original location - at this point the SQL Server is running. Please note that after the server is dr'ed, the original restored version of SQL Server 2000 fails to start - this is why I take off the old instance and re-install a new version of the SQL Server 2000!
but when I then try to access the application it fails.
If you take a backup using the CA SQL Agent, then you must restore it using a CA SQL Agent. Can you give us more details ?
PLease see response above. I am obviously doing something wrong.....
Thanks,
Alejandro
June 13, 2009 at 6:36 am
Thank you for your unhelpful post. If you read my updated post - you will see that I have succesfully DR'Ed my exchange, file server and DC - so obviously I understand the concept of disaster recovery. I just need assistance with SQL.
June 13, 2009 at 6:37 am
The message previously about unhelpful post relates to the message left by Pradeep.
Thanks.
June 13, 2009 at 6:55 am
Thanks for the feedback 🙂
1. your updated post came after my reply.
Anyways,
I use the built in backup facilities within SQL Server 2000 to create a backup of the databases - I am assuming this is only backing up the system databases and not the user databases ? Remember I am new to SQL - can you confirm what is actually backed up by the built in backup facility within SQL Server 2000 ?
You can backup all databases using SQL Server 2000 builtin backup facility.
Complete syntax of backup is here:
http://msdn.microsoft.com/en-us/library/aa225964(SQL.80).aspx
I don't use CA Arcserve utility(Am assuming it backs up entire hard drive, i may be wrong here). however, this is the approach you can apply.
1. install sql 2000 on the DR Server
2. Restore the user databases on the DR Server
Complete syntax for restoration is here:
http://msdn.microsoft.com/en-us/library/aa238405.aspx
There will be few issues that you will need to take care of, Logins and Jobs. You need to create a script on production server for logins and jobs and apply them on the target server.
June 13, 2009 at 7:37 am
Thanks Pradeep - this is more like the help I am after.
I have had a look at the MSDN link - excuse me for my SQL ignorance - how do I execute the commands show in the link - it looks like a script of some kind ?
June 13, 2009 at 7:52 am
suppose one of your user's database's name is db1
Open SQL Server Enterprise Manager on the source server. Click on Tools->SQL Query Analyzer.
Copy the statement mentioned below for and run it. (Press F5)
1. Find the location of database and log files of the source database
SP_HELPDB 'DB1'
You'll see different files with complete path. Make sure these path exist on the target server.
Once u've noted the file locations, you run next statement to take actual backup
To backup this database on the source server, you write something like this.
backup database DB1 to disk='d:\backups\db1.bak'
** You can choose appropriate path here. and the folder must exist (here d:\backup folder)
You can fire this command in the query analyzer.
YOu will see a confirmation on the query analyzer and You'll see the file DB1.BAK on the backup folder.
Copy this file on the DR server.
On the DR Server, you run this statement.
Restore database DB1 from disk='d:\backup\db1.BAK'
Here again the path relates to the backup file path on the target server.
You will get confirmation message saying your database has been successfully restored.
You see the database in the left pane of the Enterprise Manager on the target server.
** I assume you've already installed SQL Server 2000 on the target server.
Do let us know if you face any glitches in running above.
June 13, 2009 at 10:42 am
Thank you Pradeep - this is the exact advice I am after. Can I please ask you to provide me with some information abou the system databases - do I need to back these up from my Source server and restore them to the DR server - if so how do I go about doing this and how many system databases are there.
Also, there are several tables within the application are all these tables (some of which includes the users logins and passwords) saved to a single database- is there an additional command I need to run to synchronise the account login data ....?
I appreciate your help and apologise for my comment about unhelpful post - you obviously know your stuff and I am very grateful for your assistance.
Thank you.
June 13, 2009 at 11:02 am
There are in all 4 system databases in sql 2000 (Master, model, msdb and tempdb)
as far as system databases are concerned, you cant backup tempdb, so just leave it.
As far as model is concerned, it just acts as a template for any new database that is created. so u can leave this as well. If you know if any default configuration of model db that was altered, you can redo those changes on target server.
MSDB holds all your SQL Server Agent related information, various jobs, backup/restore histories etc. You can script out various jobs and apply those scripts on the DR server MSDB databases that will re-create all jobs. No need to restore database.
Master - Stores server wide configurations and user logins. You can restore it but its bit tricky and requires some sql expert to do it.
In Sql Server there are 2 level of authentication.
1. Login - that allows u to access server as a whole.
2. user id - this allows you to access a particular database depending on the type of privilages that particular account has. for every user id in the database, there must be an associated Login (stored in master), user IDs are stored in databases and so u'll still find them on a restored server(target)
When u restore a database, all objects and data is restored on the target db, the most common problem that u may encounter is orphaned user (database has user id) but there is no associated login. Such user IDs without logins are called orphaned users. You can find a list of orphaned users by running this
sp_change_users_login 'report'.
http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx[/url] will help u to fix this issue.
-------------------------------
I see you're trying to restore your database on the DR server so that in case of disaster, you can bring back the DR server; however the catch here is all modifications made to the database after ur last backup will be lost. You can use log-shipping which keeps on moving modifications on the target database automatically at a specific duration(defined by you, may be few minutes), so the amount of data loss becomes minimal in case of disaster. There are other methods for DR of SQL Server that i mentioned in my 1st post. you may want to discuss this with your sql guys and data owners and discuss best strategy 🙂
--------------------------------
and ya, i didn't mind your comments at all;-)
Cheers,
June 13, 2009 at 12:35 pm
Thanks Pradeep - can I come back to you for further assistance if I came across problems - I will try out the steps you have outlined on Monday.
Unfort. I don't have any SQL expertise in my team - hence I am reliant on SQL experts such as yourself to help me.
Thanks again.
June 13, 2009 at 10:07 pm
auxinet07 (6/13/2009)
Thanks Pradeep - can I come back to you for further assistance if I came across problems - I will try out the steps you have outlined on Monday.Unfort. I don't have any SQL expertise in my team - hence I am reliant on SQL experts such as yourself to help me.
Thanks again.
Am glad i could be of some help. You can come here anytime and will find hundreds of experts to help you out.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply