| Author |
Topic |
|
kezmaster
Starting Member
16 Posts |
 Posted - 2010-05-04 : 15:05:31
|
| As part of a BCP, I want to start with two identical databases - one on a box in one city and another on a server in a different city.My current database is huge. The bak file for a full backup is over 90 Gb (too large to send across the network). What I'd like to do is send differential /incremental (taken twice daily) backups from server A to server B over the shared network. What I cannot figure out is how to get SQL2005 to allow me to restore the small backups on server B. Currently the destination server (B) tells me that the restore cannot continue as there is no file ready to roll forward. Can someone please provide to me a list of steps on how to do this?Note that I have worked with SQL2000 for years (never tried to do this in SQL2K though) and just upgraded to 2005.Kevin W. Plath (c) JSr Programmer | Database Administrator | ContractorDLCI 6.6 | 2 ASG Petawawa | the Adga GroupeDéfense nationale | National Defence Petawawa, Canada K8H 2X3Kevin.Plath@forces.gc.caTelephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969Gouvernement du Canada | Government of Canada |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kezmaster
Starting Member
16 Posts |
Posted - 2010-05-04 : 15:32:11
|
quote:
The process hasn't changed between 2000 and 2005. It sounds like you didn't specify NORECOVERY to allow it to apply additional backups.RESTORE DATABASE WITH NORECOVERY -- fullRESTORE DATABASE WITH DIFFERENTIAL, NORECOVERY -- diffRESTORE LOG WITH NORECOVERY -- tlog...RESTORE LOG WITH RECOVERY -- final tlog
A full restore and back up again were just done - making it ready to manage.So now are you saying I'd have to restore my "delta data" with no recovery, then restore my log with no recovery and then restore my log with recovery? - Totalling 3 restores?Note: I am doing actions from the SQL2005 Server Management Studio GUI.Kevin W. Plath (c) JSr Programmer | Database Administrator | ContractorDLCI 6.6 | 2 ASG Petawawa | the Adga GroupeDéfense nationale | National Defence Petawawa, Canada K8H 2X3Kevin.Plath@forces.gc.caTelephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969Gouvernement du Canada | Government of Canada |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kezmaster
Starting Member
16 Posts |
Posted - 2010-05-04 : 16:17:04
|
quote:
The full restore must specify "WITH NORECOVERY" if you intend to add additional files. If you didn't do that, then you must perform the full restore again. You keep specifying that option until the final file, where you specify "WITH RECOVERY" which is actually the default.The GUI allows you to specify the "WITH NORECOVERY" option in the Options page.
Thanks, I can't wait to try this tomorrow - you've been great!BTW, ...is there no way to do a restore of a differential back up "with recovery"?Kevin W. Plath (c) JSr Programmer | Database Administrator | ContractorDLCI 6.6 | 2 ASG Petawawa | the Adga GroupeDéfense nationale | National Defence Petawawa, Canada K8H 2X3Kevin.Plath@forces.gc.caTelephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969Gouvernement du Canada | Government of Canada |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kezmaster
Starting Member
16 Posts |
Posted - 2010-05-06 : 09:54:17
|
| Regretfully this didn't work. There must be some initial configuration that I'm missing.Can it not be that simple to do? I simply want to take the daily new data from my live db (or test box A) and restore it to an off-site "emergency" db (or test Box B). How hard can this really be? ...this is frustrating! Perhaps I'll ask again, can someone please provide to me an all inclusive list of steps on how to do this?Also,I have in the past implemented (in a test environment), data replication between two servers in a SQL2K environment, and it worked. Is mirroring in SQL2K5 different or is it just replication improved?Kevin W. Plath (c) JSr Programmer | Database Administrator | ContractorDLCI 6.6 | 2 ASG Petawawa | the Adga GroupeDéfense nationale | National Defence Petawawa, Canada K8H 2X3Kevin.Plath@forces.gc.caTelephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969Gouvernement du Canada | Government of Canada |
|
|
|
kezmaster
Starting Member
16 Posts |
Posted - 2010-05-06 : 11:08:57
|
quote:
Also,I have in the past implemented (in a test environment), data replication between two servers in a SQL2K environment, and it worked. Is mirroring in SQL2K5 different or is it just replication improved?
Nevermind - I got the DL on mirroring and understand it better now.So going back to my original issue... ... what backup (and options) and restore (and options) steps do I need to take, to get a differential backup to restore to an off-site server? I thought I had a handle on this but ... still getting errors.Kevin W. Plath (c) JSr Programmer | Database Administrator | ContractorDLCI 6.6 | 2 ASG Petawawa | the Adga GroupeDéfense nationale | National Defence Petawawa, Canada K8H 2X3Kevin.Plath@forces.gc.caTelephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969Gouvernement du Canada | Government of Canada |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-06 : 11:12:13
|
| Perhaps you should expand on what "this didn't work" means. Did you get an error? If so, what error?Data mirroring is for high availability and not to make a copy on another server. The mirrored database is not available for queries until you failover over to it. Once you failover to it, then the other database isn't available for queries. So only one is available ever. I use transactional replication if I want two databases available for queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
kezmaster
Starting Member
16 Posts |
Posted - 2010-05-06 : 11:31:27
|
quote:
Originally posted by tkizer
Perhaps you should expand on what "this didn't work" means. Did you get an error? If so, what error?
Although I initially restored the very same backup on both boxes, the attempt at restoring the diff produced an error msg that stated... "This differential backup cannot be restored because the database has not been restored to the correct earlier state." ...and yes (on box B) I used the option "with no recovery" for the full restore and for the diff restore attempt.Wouldn't it be nice if the error msgs were a little more detailed or suggestive?Kevin W. Plath (c) JSr Programmer | Database Administrator | ContractorDLCI 6.6 | 2 ASG Petawawa | the Adga GroupeDéfense nationale | National Defence Petawawa, Canada K8H 2X3Kevin.Plath@forces.gc.caTelephone 613-687-5511 Loc. 7229 Facsimile 613-588-6969Gouvernement du Canada | Government of Canada |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
two separate boxes