January 6, 2011 at 10:09 pm
Hi,
We have 2 SQL Servers (Server A and Server B), Server A with SQL Server 2000 installed and Server B with SQL Server 2008 installed. Everyday at 8:00 a.m., we would like to get a DB image on Server A and apply to Server B, and make this process automated.
Apart from SQL replication, is there any other way to do so?
Thx
January 6, 2011 at 10:29 pm
to restore 2000 image to 2008, i think would be quiet complicated.
My personal suggestion is if this is just refreshing server B database with Server A every day, and if server A is not operation then
1) I dont think you need to refresh all the database every day(like system database and the user database which are static/not getting changed frequently).
2) For rest of the database you can choose between logshipping or mirroring.
At windows level, there is option to restore the image of existing sql to another server but I think its valid for same sql version(not sure though).
----------
Ashish
January 6, 2011 at 10:59 pm
Thx Ashish,
One user database is required to refresh from Server A to Server B only.
For logshipping, can I configure SQL Server to transfer txn log regularly (e.g. transfer every 15 minutes from Server A to B) and apply the log to the database on Server B per day only (i.e. 8:00 a.m. every day). Also for data model change (e.g. table modification/creation), will it also apply to Server B if using logshipping approach?
Thx.
January 6, 2011 at 11:11 pm
As its for one database then yes you can configure logshipping. And as per your requirement, the steps should be:-
1) Take full backup on primary and restore on secondary with standby(database will be available for read) or norecovery(database will not be available for read).
2) Configure log shipping and interval of log backup(15 min as you require)
3) Configure the folder on secondary server with proper permission to let SQL copy the log file to that folder.
4) Once log file copied to secondary server then its upto you either you want to restore it in 15 min interval or in 24 hrs interval.
In your case, I will prefer to take differential backup every 6 hrs(or later) and restore on seconday immediately instead of taking log backup every 15 min and then getting restored after a day(which will take time and any error in between need to reinitiate the restore process).
Pelase let me know if I am not clear.
----------
Ashish
January 6, 2011 at 11:19 pm
You can't set up logshipping betwee 2000 and 2008 via SSMS. You need to write scripts to do so.
January 6, 2011 at 11:34 pm
John.Liu (1/6/2011)
You can't set up logshipping betwee 2000 and 2008 via SSMS. You need to write scripts to do so.
you can register your sql 2000 instance with ssms 2008 and can configure.
Also if interested then transactional or merge replication is another option. And replication is plateform independent.
----------
Ashish
January 7, 2011 at 12:11 am
What's the size of the databases you are trying to copy ?
If that size is small, maybe a simple backup / restore operation can serve you well in stead of the overhead of other replication technologies.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2011 at 12:40 am
Thx ALZDBA,
How can I get the backup/restore automatic? thx.
January 7, 2011 at 12:58 am
Straight forward, using no rocket science, primitive:
I hope you are already producing sqlserver database backups on a regular basis !
You need to figure out if you want a point in time restore, or if a backup of around that time is ok. (which is the simplest way)
- Schedule a SQLAgent job that creates a DIFF backup at the desired time on your sql2000
- If you want to launch the resore job at you sql2008 instance directly from your sql2000 job, ad a jobstep that executes a osql script starting the recovery job on your sql2008 instance.
On the sql2008 instance you can then restore the previous fullbackup (with norecover) and restore the last diff backup.
-If you are opting for the PIT recovery, you should schedule an extra incremental log backup at the desired time on your sql2000 instance.
At your sql2008 instance you should generate a recovery script to handle all needed restore statemenst for your full backup and the needed log restores to PIT.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply