Dear friends,
I do transaction log backup 3 times a day 11:30 am, 05:00
pm, 07:00 pm. My question is
1. If I restore transaction log, Do I need restore all
transaction log or only the latest transaction log.
thx."kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:00ef01c378e2$2f125b20$a001280a@.phx.gbl...
> I do transaction log backup 3 times a day 11:30 am, 05:00
> pm, 07:00 pm. My question is
> 1. If I restore transaction log, Do I need restore all
> transaction log or only the latest transaction log.
> 1. If I restore transaction log, Do I need restore all
> transaction log or only the latest transaction log.
Since the transaction log records each change in sequence, you'll need to
restore all the transaction log backups (in sequence).|||"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vm2j3l3e5sa44b@.corp.supernews.com...
> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> news:00ef01c378e2$2f125b20$a001280a@.phx.gbl...
> >
> > I do transaction log backup 3 times a day 11:30 am, 05:00
> > pm, 07:00 pm. My question is
> >
> > 1. If I restore transaction log, Do I need restore all
> > transaction log or only the latest transaction log.
> > 1. If I restore transaction log, Do I need restore all
> > transaction log or only the latest transaction log.
> Since the transaction log records each change in sequence, you'll need to
> restore all the transaction log backups (in sequence).
Mr Cambell is absolutely correct. However, if when you backup each log you
specify the option 'with no_truncate' then you would only need to restore
the last log backup. The drawback of this is that your log file will grow
and grow, so you might want to consider immediately before your full backup
backing up the log with the default of 'with truncate'|||> However, if when you backup each log you
> specify the option 'with no_truncate' then you would only need to restore
> the last log backup.
Note that BOL, for the NO_TRUNCATE, parameter say:
"Allows backing up the log in situations where the database is damaged."
Based on that, I would say that using the NO_TRUNCATE option for other purposes isn't supported (as
it isn't documented). Personally, I'd prefer if MS either renamed this option, or documented
explicitly whether it is supported to use for such backup scenario.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"bob simms" <bob_simms@.hotmail.com> wrote in message news:OuvE15QeDHA.1736@.TK2MSFTNGP12.phx.gbl...
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vm2j3l3e5sa44b@.corp.supernews.com...
> > "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> > news:00ef01c378e2$2f125b20$a001280a@.phx.gbl...
> > >
> > > I do transaction log backup 3 times a day 11:30 am, 05:00
> > > pm, 07:00 pm. My question is
> > >
> > > 1. If I restore transaction log, Do I need restore all
> > > transaction log or only the latest transaction log.
> > > 1. If I restore transaction log, Do I need restore all
> > > transaction log or only the latest transaction log.
> >
> > Since the transaction log records each change in sequence, you'll need to
> > restore all the transaction log backups (in sequence).
> Mr Cambell is absolutely correct. However, if when you backup each log you
> specify the option 'with no_truncate' then you would only need to restore
> the last log backup. The drawback of this is that your log file will grow
> and grow, so you might want to consider immediately before your full backup
> backing up the log with the default of 'with truncate'
>
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Wednesday, March 28, 2012
restore transaction log
dear all,
I want to restore transaction log to other server using transact-SQL.
can we restore serveral transaction log files at the same time ?
anybody have experience on this one.
thanks
Use the "WITH NO RECOVERY" option after each restore to allow subsequent restores. The "WITH STANDBY" option allows the database to be inspected (read only) after eah restore. Use the "WITH RECOVERY" option on the final restore to make the database read/write.
You need to make sure each log is restored in the order in which it was created. Further, you need to ensure the transaction log backup matches the full backup i.e. : you can't skip log backups, and if you truncated the log at any point, then the sequence is broken and you would need to start again with another full backup.
|||hi rod,
I have tries your suggestion but I still have some problem.
I restore my full backup to other server using no recovery option and then I restore the transaction log. which on the last transcation log I use recovery option.
that part its work perfectly.
the problem is when I want to restore another transcation log there is an error that said :
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
we do daily restore transaction log to other server.
regards,
-dedys
|||
Ok, now I understand what you are trying to do.
Basically you can't do what you trying. Once you've restored a log "with recovery" you cannot restore another log at a later point. You would need to take another full backup.
What you should probably do is setup transaction log shipping but select the option to leave the database in a read only state.This allows you to read the database between log restores, however, if you are using the database when the log restore is scheduled, then either the resore will fail, or you can select the option to terminate current connections before the restore.
Does this make sense ? Let me know if you need further help on setting up log shipping.
Hope this helps.
|||hi rod,
yes actually I want to set log shipping.
but I can't set it cause my production server is using sql 2000 and my staging server is using sql 2005.
so I plan to do it manually using job.
if you said that we can't add more transaction log after using with recovery.
so what is the best option for my case.
is there a third party software to do this ?
thanks
-dedys
Monday, March 26, 2012
restore to a specific state
Dear all,
I'm using SQL server 2000.
The last backup made was on 02/Jul/2007.
Today is 24/Jul/2007: 2 days ago, some majors changes (insert/update/delete)
were made on the database. and I want to rollback these transactions (and
only those made in the 2 days).
Is there any code, or something to do that can help me?
Thank you in advance.
jouj
Perform transaction log backup. Then restore your full backup made on
02/Jul/2007 and use just created transaction log backup by restoring it with
option STOP AT (this will allow you to limit the number of restored
transactions just to the date and time you need).
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> Dear all,
> I'm using SQL server 2000.
> The last backup made was on 02/Jul/2007.
> Today is 24/Jul/2007: 2 days ago, some majors changes
> (insert/update/delete)
> were made on the database. and I want to rollback these transactions (and
> only those made in the 2 days).
> Is there any code, or something to do that can help me?
> Thank you in advance.
> jouj
|||Hi,
I followed your steps and on the last one:
RESTORE LOG mydb
FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
I'm getting this error message:
[
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
]
Regards,
jouj
"Pawel Potasinski" wrote:
> Perform transaction log backup. Then restore your full backup made on
> 02/Jul/2007 and use just created transaction log backup by restoring it with
> option STOP AT (this will allow you to limit the number of restored
> transactions just to the date and time you need).
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>
>
|||1. Backup your transaction log with NORECOVERY option (this will put
database in Restoring state).
2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
AT options.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...[vbcol=seagreen]
> Hi,
> I followed your steps and on the last one:
> RESTORE LOG mydb
> FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> I'm getting this error message:
> [
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> ]
> Regards,
> jouj
> "Pawel Potasinski" wrote:
|||Dear Mr. Pawel,
Thank you for your help but it did'nt work for me.
I'm getting this message:
The log in this backup set begins at LSN 86445000000038300001, which is too
late to apply to the database. An earlier log backup that includes LSN
72624000011844600001 can be restored.
So I think there was an old backup for the log file made after my backup of
02/07/2007 and the actual log file does not start from the backup time.
Thank you for your efforts,
Regards,
jouj
--
G.Haddad
"Pawel Potasinski" wrote:
> 1. Backup your transaction log with NORECOVERY option (this will put
> database in Restoring state).
> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
> 3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
> AT options.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
>
>
|||This should encourage you to set some solid backup strategy for your
databases.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:B5442BF8-1D64-4583-A939-BB43F87C22CC@.microsoft.com...[vbcol=seagreen]
> Dear Mr. Pawel,
> Thank you for your help but it did'nt work for me.
> I'm getting this message:
> The log in this backup set begins at LSN 86445000000038300001, which is
> too
> late to apply to the database. An earlier log backup that includes LSN
> 72624000011844600001 can be restored.
> So I think there was an old backup for the log file made after my backup
> of
> 02/07/2007 and the actual log file does not start from the backup time.
> Thank you for your efforts,
> Regards,
> jouj
> --
> --
> G.Haddad
> --
>
> "Pawel Potasinski" wrote:
sql
I'm using SQL server 2000.
The last backup made was on 02/Jul/2007.
Today is 24/Jul/2007: 2 days ago, some majors changes (insert/update/delete)
were made on the database. and I want to rollback these transactions (and
only those made in the 2 days).
Is there any code, or something to do that can help me?
Thank you in advance.
jouj
Perform transaction log backup. Then restore your full backup made on
02/Jul/2007 and use just created transaction log backup by restoring it with
option STOP AT (this will allow you to limit the number of restored
transactions just to the date and time you need).
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> Dear all,
> I'm using SQL server 2000.
> The last backup made was on 02/Jul/2007.
> Today is 24/Jul/2007: 2 days ago, some majors changes
> (insert/update/delete)
> were made on the database. and I want to rollback these transactions (and
> only those made in the 2 days).
> Is there any code, or something to do that can help me?
> Thank you in advance.
> jouj
|||Hi,
I followed your steps and on the last one:
RESTORE LOG mydb
FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
I'm getting this error message:
[
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
]
Regards,
jouj
"Pawel Potasinski" wrote:
> Perform transaction log backup. Then restore your full backup made on
> 02/Jul/2007 and use just created transaction log backup by restoring it with
> option STOP AT (this will allow you to limit the number of restored
> transactions just to the date and time you need).
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>
>
|||1. Backup your transaction log with NORECOVERY option (this will put
database in Restoring state).
2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
AT options.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...[vbcol=seagreen]
> Hi,
> I followed your steps and on the last one:
> RESTORE LOG mydb
> FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> I'm getting this error message:
> [
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> ]
> Regards,
> jouj
> "Pawel Potasinski" wrote:
|||Dear Mr. Pawel,
Thank you for your help but it did'nt work for me.
I'm getting this message:
The log in this backup set begins at LSN 86445000000038300001, which is too
late to apply to the database. An earlier log backup that includes LSN
72624000011844600001 can be restored.
So I think there was an old backup for the log file made after my backup of
02/07/2007 and the actual log file does not start from the backup time.
Thank you for your efforts,
Regards,
jouj
--
G.Haddad
"Pawel Potasinski" wrote:
> 1. Backup your transaction log with NORECOVERY option (this will put
> database in Restoring state).
> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
> 3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
> AT options.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
>
>
|||This should encourage you to set some solid backup strategy for your
databases.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:B5442BF8-1D64-4583-A939-BB43F87C22CC@.microsoft.com...[vbcol=seagreen]
> Dear Mr. Pawel,
> Thank you for your help but it did'nt work for me.
> I'm getting this message:
> The log in this backup set begins at LSN 86445000000038300001, which is
> too
> late to apply to the database. An earlier log backup that includes LSN
> 72624000011844600001 can be restored.
> So I think there was an old backup for the log file made after my backup
> of
> 02/07/2007 and the actual log file does not start from the backup time.
> Thank you for your efforts,
> Regards,
> jouj
> --
> --
> G.Haddad
> --
>
> "Pawel Potasinski" wrote:
sql
restore to a specific state
Dear all,
I'm using SQL server 2000.
The last backup made was on 02/Jul/2007.
Today is 24/Jul/2007: 2 days ago, some majors changes (insert/update/delete)
were made on the database. and I want to rollback these transactions (and
only those made in the 2 days).
Is there any code, or something to do that can help me?
Thank you in advance.
joujPerform transaction log backup. Then restore your full backup made on
02/Jul/2007 and use just created transaction log backup by restoring it with
option STOP AT (this will allow you to limit the number of restored
transactions just to the date and time you need).
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> Dear all,
> I'm using SQL server 2000.
> The last backup made was on 02/Jul/2007.
> Today is 24/Jul/2007: 2 days ago, some majors changes
> (insert/update/delete)
> were made on the database. and I want to rollback these transactions (and
> only those made in the 2 days).
> Is there any code, or something to do that can help me?
> Thank you in advance.
> jouj|||Hi,
I followed your steps and on the last one:
RESTORE LOG mydb
FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
I'm getting this error message:
[
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
]
Regards,
jouj
"Pawel Potasinski" wrote:
> Perform transaction log backup. Then restore your full backup made on
> 02/Jul/2007 and use just created transaction log backup by restoring it wi
th
> option STOP AT (this will allow you to limit the number of restored
> transactions just to the date and time you need).
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>
>|||1. Backup your transaction log with NORECOVERY option (this will put
database in Restoring state).
2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
AT options.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...[vbcol=seagreen]
> Hi,
> I followed your steps and on the last one:
> RESTORE LOG mydb
> FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> I'm getting this error message:
> [
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> ]
> Regards,
> jouj
> "Pawel Potasinski" wrote:
>|||Dear Mr. Pawel,
Thank you for your help but it did'nt work for me.
I'm getting this message:
The log in this backup set begins at LSN 86445000000038300001, which is too
late to apply to the database. An earlier log backup that includes LSN
72624000011844600001 can be restored.
So I think there was an old backup for the log file made after my backup of
02/07/2007 and the actual log file does not start from the backup time.
Thank you for your efforts,
Regards,
jouj
--
G.Haddad
--
"Pawel Potasinski" wrote:
> 1. Backup your transaction log with NORECOVERY option (this will put
> database in Restoring state).
> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLAC
E
> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option
)
> 3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
> AT options.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
>
>|||This should encourage you to set some solid backup strategy for your
databases.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:B5442BF8-1D64-4583-A939-BB43F87C22CC@.microsoft.com...[vbcol=seagreen]
> Dear Mr. Pawel,
> Thank you for your help but it did'nt work for me.
> I'm getting this message:
> The log in this backup set begins at LSN 86445000000038300001, which is
> too
> late to apply to the database. An earlier log backup that includes LSN
> 72624000011844600001 can be restored.
> So I think there was an old backup for the log file made after my backup
> of
> 02/07/2007 and the actual log file does not start from the backup time.
> Thank you for your efforts,
> Regards,
> jouj
> --
> --
> G.Haddad
> --
>
> "Pawel Potasinski" wrote:
>
I'm using SQL server 2000.
The last backup made was on 02/Jul/2007.
Today is 24/Jul/2007: 2 days ago, some majors changes (insert/update/delete)
were made on the database. and I want to rollback these transactions (and
only those made in the 2 days).
Is there any code, or something to do that can help me?
Thank you in advance.
joujPerform transaction log backup. Then restore your full backup made on
02/Jul/2007 and use just created transaction log backup by restoring it with
option STOP AT (this will allow you to limit the number of restored
transactions just to the date and time you need).
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> Dear all,
> I'm using SQL server 2000.
> The last backup made was on 02/Jul/2007.
> Today is 24/Jul/2007: 2 days ago, some majors changes
> (insert/update/delete)
> were made on the database. and I want to rollback these transactions (and
> only those made in the 2 days).
> Is there any code, or something to do that can help me?
> Thank you in advance.
> jouj|||Hi,
I followed your steps and on the last one:
RESTORE LOG mydb
FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
I'm getting this error message:
[
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
]
Regards,
jouj
"Pawel Potasinski" wrote:
> Perform transaction log backup. Then restore your full backup made on
> 02/Jul/2007 and use just created transaction log backup by restoring it wi
th
> option STOP AT (this will allow you to limit the number of restored
> transactions just to the date and time you need).
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>
>|||1. Backup your transaction log with NORECOVERY option (this will put
database in Restoring state).
2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
AT options.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...[vbcol=seagreen]
> Hi,
> I followed your steps and on the last one:
> RESTORE LOG mydb
> FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> I'm getting this error message:
> [
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> ]
> Regards,
> jouj
> "Pawel Potasinski" wrote:
>|||Dear Mr. Pawel,
Thank you for your help but it did'nt work for me.
I'm getting this message:
The log in this backup set begins at LSN 86445000000038300001, which is too
late to apply to the database. An earlier log backup that includes LSN
72624000011844600001 can be restored.
So I think there was an old backup for the log file made after my backup of
02/07/2007 and the actual log file does not start from the backup time.
Thank you for your efforts,
Regards,
jouj
--
G.Haddad
--
"Pawel Potasinski" wrote:
> 1. Backup your transaction log with NORECOVERY option (this will put
> database in Restoring state).
> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLAC
E
> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option
)
> 3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
> AT options.
> --
> Regards
> Pawel Potasinski
>
> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
>
>|||This should encourage you to set some solid backup strategy for your
databases.
Regards
Pawel Potasinski
Uytkownik "jouj" <jouj@.discussions.microsoft.com> napisa w wiadomoci
news:B5442BF8-1D64-4583-A939-BB43F87C22CC@.microsoft.com...[vbcol=seagreen]
> Dear Mr. Pawel,
> Thank you for your help but it did'nt work for me.
> I'm getting this message:
> The log in this backup set begins at LSN 86445000000038300001, which is
> too
> late to apply to the database. An earlier log backup that includes LSN
> 72624000011844600001 can be restored.
> So I think there was an old backup for the log file made after my backup
> of
> 02/07/2007 and the actual log file does not start from the backup time.
> Thank you for your efforts,
> Regards,
> jouj
> --
> --
> G.Haddad
> --
>
> "Pawel Potasinski" wrote:
>
restore to a specific state
Dear all,
I'm using SQL server 2000.
The last backup made was on 02/Jul/2007.
Today is 24/Jul/2007: 2 days ago, some majors changes (insert/update/delete)
were made on the database. and I want to rollback these transactions (and
only those made in the 2 days).
Is there any code, or something to do that can help me?
Thank you in advance.
joujPerform transaction log backup. Then restore your full backup made on
02/Jul/2007 and use just created transaction log backup by restoring it with
option STOP AT (this will allow you to limit the number of restored
transactions just to the date and time you need).
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> Dear all,
> I'm using SQL server 2000.
> The last backup made was on 02/Jul/2007.
> Today is 24/Jul/2007: 2 days ago, some majors changes
> (insert/update/delete)
> were made on the database. and I want to rollback these transactions (and
> only those made in the 2 days).
> Is there any code, or something to do that can help me?
> Thank you in advance.
> jouj|||Hi,
I followed your steps and on the last one:
RESTORE LOG mydb
FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
I'm getting this error message:
[
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
]
Regards,
jouj
"Pawel Potasinski" wrote:
> Perform transaction log backup. Then restore your full backup made on
> 02/Jul/2007 and use just created transaction log backup by restoring it with
> option STOP AT (this will allow you to limit the number of restored
> transactions just to the date and time you need).
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> > Dear all,
> > I'm using SQL server 2000.
> > The last backup made was on 02/Jul/2007.
> >
> > Today is 24/Jul/2007: 2 days ago, some majors changes
> > (insert/update/delete)
> > were made on the database. and I want to rollback these transactions (and
> > only those made in the 2 days).
> > Is there any code, or something to do that can help me?
> >
> > Thank you in advance.
> > jouj
>
>|||1. Backup your transaction log with NORECOVERY option (this will put
database in Restoring state).
2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
AT options.
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
> Hi,
> I followed your steps and on the last one:
> RESTORE LOG mydb
> FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> I'm getting this error message:
> [
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> ]
> Regards,
> jouj
> "Pawel Potasinski" wrote:
>> Perform transaction log backup. Then restore your full backup made on
>> 02/Jul/2007 and use just created transaction log backup by restoring it
>> with
>> option STOP AT (this will allow you to limit the number of restored
>> transactions just to the date and time you need).
>> --
>> Regards
>> Pawel Potasinski
>>
>> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>> > Dear all,
>> > I'm using SQL server 2000.
>> > The last backup made was on 02/Jul/2007.
>> >
>> > Today is 24/Jul/2007: 2 days ago, some majors changes
>> > (insert/update/delete)
>> > were made on the database. and I want to rollback these transactions
>> > (and
>> > only those made in the 2 days).
>> > Is there any code, or something to do that can help me?
>> >
>> > Thank you in advance.
>> > jouj
>>|||Dear Mr. Pawel,
Thank you for your help but it did'nt work for me.
I'm getting this message:
The log in this backup set begins at LSN 86445000000038300001, which is too
late to apply to the database. An earlier log backup that includes LSN
72624000011844600001 can be restored.
So I think there was an old backup for the log file made after my backup of
02/07/2007 and the actual log file does not start from the backup time.
Thank you for your efforts,
Regards,
jouj
--
--
G.Haddad
--
"Pawel Potasinski" wrote:
> 1. Backup your transaction log with NORECOVERY option (this will put
> database in Restoring state).
> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
> 3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
> AT options.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
> > Hi,
> >
> > I followed your steps and on the last one:
> >
> > RESTORE LOG mydb
> > FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> > WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> >
> > I'm getting this error message:
> > [
> > The preceding restore operation did not specify WITH NORECOVERY or WITH
> > STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> > STANDBY for all but the final step.
> > ]
> >
> > Regards,
> > jouj
> > "Pawel Potasinski" wrote:
> >
> >> Perform transaction log backup. Then restore your full backup made on
> >> 02/Jul/2007 and use just created transaction log backup by restoring it
> >> with
> >> option STOP AT (this will allow you to limit the number of restored
> >> transactions just to the date and time you need).
> >>
> >> --
> >> Regards
> >> Pawel Potasinski
> >>
> >>
> >> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> >> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> >> > Dear all,
> >> > I'm using SQL server 2000.
> >> > The last backup made was on 02/Jul/2007.
> >> >
> >> > Today is 24/Jul/2007: 2 days ago, some majors changes
> >> > (insert/update/delete)
> >> > were made on the database. and I want to rollback these transactions
> >> > (and
> >> > only those made in the 2 days).
> >> > Is there any code, or something to do that can help me?
> >> >
> >> > Thank you in advance.
> >> > jouj
> >>
> >>
> >>
>
>|||This should encourage you to set some solid backup strategy for your
databases.
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:B5442BF8-1D64-4583-A939-BB43F87C22CC@.microsoft.com...
> Dear Mr. Pawel,
> Thank you for your help but it did'nt work for me.
> I'm getting this message:
> The log in this backup set begins at LSN 86445000000038300001, which is
> too
> late to apply to the database. An earlier log backup that includes LSN
> 72624000011844600001 can be restored.
> So I think there was an old backup for the log file made after my backup
> of
> 02/07/2007 and the actual log file does not start from the backup time.
> Thank you for your efforts,
> Regards,
> jouj
> --
> --
> G.Haddad
> --
>
> "Pawel Potasinski" wrote:
>> 1. Backup your transaction log with NORECOVERY option (this will put
>> database in Restoring state).
>> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and
>> REPLACE
>> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY
>> option)
>> 3. Restore transaction log backup created in step 1 witn RECOVERY and
>> STOP
>> AT options.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
>> > Hi,
>> >
>> > I followed your steps and on the last one:
>> >
>> > RESTORE LOG mydb
>> > FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
>> > WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
>> >
>> > I'm getting this error message:
>> > [
>> > The preceding restore operation did not specify WITH NORECOVERY or WITH
>> > STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or
>> > WITH
>> > STANDBY for all but the final step.
>> > ]
>> >
>> > Regards,
>> > jouj
>> > "Pawel Potasinski" wrote:
>> >
>> >> Perform transaction log backup. Then restore your full backup made on
>> >> 02/Jul/2007 and use just created transaction log backup by restoring
>> >> it
>> >> with
>> >> option STOP AT (this will allow you to limit the number of restored
>> >> transactions just to the date and time you need).
>> >>
>> >> --
>> >> Regards
>> >> Pawel Potasinski
>> >>
>> >>
>> >> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w
>> >> wiadomo?ci
>> >> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>> >> > Dear all,
>> >> > I'm using SQL server 2000.
>> >> > The last backup made was on 02/Jul/2007.
>> >> >
>> >> > Today is 24/Jul/2007: 2 days ago, some majors changes
>> >> > (insert/update/delete)
>> >> > were made on the database. and I want to rollback these transactions
>> >> > (and
>> >> > only those made in the 2 days).
>> >> > Is there any code, or something to do that can help me?
>> >> >
>> >> > Thank you in advance.
>> >> > jouj
>> >>
>> >>
>> >>
>>
I'm using SQL server 2000.
The last backup made was on 02/Jul/2007.
Today is 24/Jul/2007: 2 days ago, some majors changes (insert/update/delete)
were made on the database. and I want to rollback these transactions (and
only those made in the 2 days).
Is there any code, or something to do that can help me?
Thank you in advance.
joujPerform transaction log backup. Then restore your full backup made on
02/Jul/2007 and use just created transaction log backup by restoring it with
option STOP AT (this will allow you to limit the number of restored
transactions just to the date and time you need).
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> Dear all,
> I'm using SQL server 2000.
> The last backup made was on 02/Jul/2007.
> Today is 24/Jul/2007: 2 days ago, some majors changes
> (insert/update/delete)
> were made on the database. and I want to rollback these transactions (and
> only those made in the 2 days).
> Is there any code, or something to do that can help me?
> Thank you in advance.
> jouj|||Hi,
I followed your steps and on the last one:
RESTORE LOG mydb
FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
I'm getting this error message:
[
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
]
Regards,
jouj
"Pawel Potasinski" wrote:
> Perform transaction log backup. Then restore your full backup made on
> 02/Jul/2007 and use just created transaction log backup by restoring it with
> option STOP AT (this will allow you to limit the number of restored
> transactions just to the date and time you need).
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> > Dear all,
> > I'm using SQL server 2000.
> > The last backup made was on 02/Jul/2007.
> >
> > Today is 24/Jul/2007: 2 days ago, some majors changes
> > (insert/update/delete)
> > were made on the database. and I want to rollback these transactions (and
> > only those made in the 2 days).
> > Is there any code, or something to do that can help me?
> >
> > Thank you in advance.
> > jouj
>
>|||1. Backup your transaction log with NORECOVERY option (this will put
database in Restoring state).
2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
AT options.
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
> Hi,
> I followed your steps and on the last one:
> RESTORE LOG mydb
> FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> I'm getting this error message:
> [
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> ]
> Regards,
> jouj
> "Pawel Potasinski" wrote:
>> Perform transaction log backup. Then restore your full backup made on
>> 02/Jul/2007 and use just created transaction log backup by restoring it
>> with
>> option STOP AT (this will allow you to limit the number of restored
>> transactions just to the date and time you need).
>> --
>> Regards
>> Pawel Potasinski
>>
>> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>> > Dear all,
>> > I'm using SQL server 2000.
>> > The last backup made was on 02/Jul/2007.
>> >
>> > Today is 24/Jul/2007: 2 days ago, some majors changes
>> > (insert/update/delete)
>> > were made on the database. and I want to rollback these transactions
>> > (and
>> > only those made in the 2 days).
>> > Is there any code, or something to do that can help me?
>> >
>> > Thank you in advance.
>> > jouj
>>|||Dear Mr. Pawel,
Thank you for your help but it did'nt work for me.
I'm getting this message:
The log in this backup set begins at LSN 86445000000038300001, which is too
late to apply to the database. An earlier log backup that includes LSN
72624000011844600001 can be restored.
So I think there was an old backup for the log file made after my backup of
02/07/2007 and the actual log file does not start from the backup time.
Thank you for your efforts,
Regards,
jouj
--
--
G.Haddad
--
"Pawel Potasinski" wrote:
> 1. Backup your transaction log with NORECOVERY option (this will put
> database in Restoring state).
> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and REPLACE
> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY option)
> 3. Restore transaction log backup created in step 1 witn RECOVERY and STOP
> AT options.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
> > Hi,
> >
> > I followed your steps and on the last one:
> >
> > RESTORE LOG mydb
> > FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
> > WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
> >
> > I'm getting this error message:
> > [
> > The preceding restore operation did not specify WITH NORECOVERY or WITH
> > STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> > STANDBY for all but the final step.
> > ]
> >
> > Regards,
> > jouj
> > "Pawel Potasinski" wrote:
> >
> >> Perform transaction log backup. Then restore your full backup made on
> >> 02/Jul/2007 and use just created transaction log backup by restoring it
> >> with
> >> option STOP AT (this will allow you to limit the number of restored
> >> transactions just to the date and time you need).
> >>
> >> --
> >> Regards
> >> Pawel Potasinski
> >>
> >>
> >> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
> >> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
> >> > Dear all,
> >> > I'm using SQL server 2000.
> >> > The last backup made was on 02/Jul/2007.
> >> >
> >> > Today is 24/Jul/2007: 2 days ago, some majors changes
> >> > (insert/update/delete)
> >> > were made on the database. and I want to rollback these transactions
> >> > (and
> >> > only those made in the 2 days).
> >> > Is there any code, or something to do that can help me?
> >> >
> >> > Thank you in advance.
> >> > jouj
> >>
> >>
> >>
>
>|||This should encourage you to set some solid backup strategy for your
databases.
--
Regards
Pawel Potasinski
U¿ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:B5442BF8-1D64-4583-A939-BB43F87C22CC@.microsoft.com...
> Dear Mr. Pawel,
> Thank you for your help but it did'nt work for me.
> I'm getting this message:
> The log in this backup set begins at LSN 86445000000038300001, which is
> too
> late to apply to the database. An earlier log backup that includes LSN
> 72624000011844600001 can be restored.
> So I think there was an old backup for the log file made after my backup
> of
> 02/07/2007 and the actual log file does not start from the backup time.
> Thank you for your efforts,
> Regards,
> jouj
> --
> --
> G.Haddad
> --
>
> "Pawel Potasinski" wrote:
>> 1. Backup your transaction log with NORECOVERY option (this will put
>> database in Restoring state).
>> 2. Restore full backup (created on 02/Jul/2007) with NORECOVERY and
>> REPLACE
>> options. <-- THIS IS WHAT YOU DID WRONG (did not specify NORECOVERY
>> option)
>> 3. Restore transaction log backup created in step 1 witn RECOVERY and
>> STOP
>> AT options.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:62D1A0E0-D972-403C-BC39-356BCD56BAAC@.microsoft.com...
>> > Hi,
>> >
>> > I followed your steps and on the last one:
>> >
>> > RESTORE LOG mydb
>> > FROM Disk = 'F:\BACKUP\mydbLogfile24_07_2007.bak'
>> > WITH RECOVERY, STOPAT = 'Jul 23, 2007 12:00 AM'
>> >
>> > I'm getting this error message:
>> > [
>> > The preceding restore operation did not specify WITH NORECOVERY or WITH
>> > STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or
>> > WITH
>> > STANDBY for all but the final step.
>> > ]
>> >
>> > Regards,
>> > jouj
>> > "Pawel Potasinski" wrote:
>> >
>> >> Perform transaction log backup. Then restore your full backup made on
>> >> 02/Jul/2007 and use just created transaction log backup by restoring
>> >> it
>> >> with
>> >> option STOP AT (this will allow you to limit the number of restored
>> >> transactions just to the date and time you need).
>> >>
>> >> --
>> >> Regards
>> >> Pawel Potasinski
>> >>
>> >>
>> >> U?ytkownik "jouj" <jouj@.discussions.microsoft.com> napisa3 w
>> >> wiadomo?ci
>> >> news:C419271B-0404-48F9-A973-41AB4F0D964F@.microsoft.com...
>> >> > Dear all,
>> >> > I'm using SQL server 2000.
>> >> > The last backup made was on 02/Jul/2007.
>> >> >
>> >> > Today is 24/Jul/2007: 2 days ago, some majors changes
>> >> > (insert/update/delete)
>> >> > were made on the database. and I want to rollback these transactions
>> >> > (and
>> >> > only those made in the 2 days).
>> >> > Is there any code, or something to do that can help me?
>> >> >
>> >> > Thank you in advance.
>> >> > jouj
>> >>
>> >>
>> >>
>>
Friday, March 23, 2012
RESTORE Terminated abnormally
Dear all,
How can I restore from a SQL backup file but only the data part? When I
restore my backup file in SQL 2000 Server, the restore process stops and
said "TErminating abnormally" I am wondering the problem can be the
transaction log part in the backup file. So, is it possible to restore only
the data part?
Thanks,
ACAllen
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
BACKUP DATABASE mywind
TO DISK ='d:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
CREATE TABLE t2 (id int) ON sales
GO
BACKUP LOG mywind TO DISK='d:\mywind.dmp'
WITH NOINIT
RESTORE FILELISTONLY FROM DISK='d:\mywind.dmp'
GO
RESTORE HEADERONLY FROM DISK='d:\mywind.dmp'
GO
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='d:\mywind.dmp'
WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'd:\mw2.pri',
MOVE 'mywind_log' TO 'd:\mw2.log',
MOVE 'mywind_data_2' TO 'd:\mw2.dat2'
GO
RESTORE LOG mywind_part
FROM DISK = 'g:\mywind.dmp'
WITH FILE = 2,RECOVERY
GO
Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
Here is the result:
--
0
Notice that t1 is not accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Here is the resulting message:
The query processor is unable to produce a plan because
the table 'mywind_part..t1' is marked OFFLINE.
"Allen Cheng" <acheng@.hk.eclipsecomputing.com> wrote in message
news:bf5q4d$6g0$1@.hfc.pacific.net.hk...
> Dear all,
> How can I restore from a SQL backup file but only the data part? When I
> restore my backup file in SQL 2000 Server, the restore process stops and
> said "TErminating abnormally" I am wondering the problem can be the
> transaction log part in the backup file. So, is it possible to restore
only
> the data part?
> Thanks,
> AC
>|||Let me give you another option:
sp_attach_single_file_db
All you need is the path to the mdf file.
You can always browse to the mdf file through Windows Explorer and then drag
and drop the file into an open START / RUN dialog box to get the full path
to the mdf easily.
"Allen Cheng" <acheng@.hk.eclipsecomputing.com> wrote in message
news:bf5q4d$6g0$1@.hfc.pacific.net.hk...
> Dear all,
> How can I restore from a SQL backup file but only the data part? When I
> restore my backup file in SQL 2000 Server, the restore process stops and
> said "TErminating abnormally" I am wondering the problem can be the
> transaction log part in the backup file. So, is it possible to restore
only
> the data part?
> Thanks,
> AC
>
How can I restore from a SQL backup file but only the data part? When I
restore my backup file in SQL 2000 Server, the restore process stops and
said "TErminating abnormally" I am wondering the problem can be the
transaction log part in the backup file. So, is it possible to restore only
the data part?
Thanks,
ACAllen
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
BACKUP DATABASE mywind
TO DISK ='d:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
CREATE TABLE t2 (id int) ON sales
GO
BACKUP LOG mywind TO DISK='d:\mywind.dmp'
WITH NOINIT
RESTORE FILELISTONLY FROM DISK='d:\mywind.dmp'
GO
RESTORE HEADERONLY FROM DISK='d:\mywind.dmp'
GO
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='d:\mywind.dmp'
WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'd:\mw2.pri',
MOVE 'mywind_log' TO 'd:\mw2.log',
MOVE 'mywind_data_2' TO 'd:\mw2.dat2'
GO
RESTORE LOG mywind_part
FROM DISK = 'g:\mywind.dmp'
WITH FILE = 2,RECOVERY
GO
Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
Here is the result:
--
0
Notice that t1 is not accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Here is the resulting message:
The query processor is unable to produce a plan because
the table 'mywind_part..t1' is marked OFFLINE.
"Allen Cheng" <acheng@.hk.eclipsecomputing.com> wrote in message
news:bf5q4d$6g0$1@.hfc.pacific.net.hk...
> Dear all,
> How can I restore from a SQL backup file but only the data part? When I
> restore my backup file in SQL 2000 Server, the restore process stops and
> said "TErminating abnormally" I am wondering the problem can be the
> transaction log part in the backup file. So, is it possible to restore
only
> the data part?
> Thanks,
> AC
>|||Let me give you another option:
sp_attach_single_file_db
All you need is the path to the mdf file.
You can always browse to the mdf file through Windows Explorer and then drag
and drop the file into an open START / RUN dialog box to get the full path
to the mdf easily.
"Allen Cheng" <acheng@.hk.eclipsecomputing.com> wrote in message
news:bf5q4d$6g0$1@.hfc.pacific.net.hk...
> Dear all,
> How can I restore from a SQL backup file but only the data part? When I
> restore my backup file in SQL 2000 Server, the restore process stops and
> said "TErminating abnormally" I am wondering the problem can be the
> transaction log part in the backup file. So, is it possible to restore
only
> the data part?
> Thanks,
> AC
>
Monday, March 12, 2012
Restore Problem - Error 5243
Dear All,
SQL : SQL Express 2005
I having a problem when trying to restore a database. there were around 10 databases, but one is giving the following error and restore fails, will be great if someone helps..
I also tried taking the backup to another site having SQL 2005 Standard edition, the same error comes.
--
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server '.\SQL2K5'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8. (Microsoft SQL Server, Error: 5243)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=5243&LinkId=20476
BUTTONS:
OK
I also installed Service Pack, no luck..HELP!!!
HI,
Refer http://support.microsoft.com/kb/916086 which has workaround and hot fix for the problem.
Hemantgiri S. Goswami
|||Hi,
Thanks for the reply. I went through that already and hence I applied the service pack 1. NO LUCK!!!
Good you pointed the KB link, I am just little confused.It says about "Creating and drop temporary tables...." and what I am doing is Restoring the DB from a backup..Does it mean while restoring temporary tables are created ?
Time is running out, HELP!!
No reply on this from Microsoft Support team!!! where are u guys?
Regards,
Restore Problem - Error 5243
Dear All,
SQL : SQL Express 2005
I having a problem when trying to restore a database. there were around 10 databases, but one is giving the following error and restore fails, will be great if someone helps..
I also tried taking the backup to another site having SQL 2005 Standard edition, the same error comes.
--
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server '.\SQL2K5'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8. (Microsoft SQL Server, Error: 5243)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=5243&LinkId=20476
BUTTONS:
OK
I also installed Service Pack, no luck..HELP!!!
HI,
Refer http://support.microsoft.com/kb/916086 which has workaround and hot fix for the problem.
Hemantgiri S. Goswami
|||Hi,
Thanks for the reply. I went through that already and hence I applied the service pack 1. NO LUCK!!!
Good you pointed the KB link, I am just little confused.It says about "Creating and drop temporary tables...." and what I am doing is Restoring the DB from a backup..Does it mean while restoring temporary tables are created ?
Time is running out, HELP!!
No reply on this from Microsoft Support team!!! where are u guys?
Regards,
Friday, March 9, 2012
Restore of Publication Database caused error
Dear All,
We are currently using Transactional Replication for a Warm Standby.
We needed to perform a restore of out Publication Database last night and
now we are getting the error :-
DATE/TIME:07/04/2005 09:50:29
DESCRIPTION:Error: 14151, Severity: 18, State: 1
Replication-Replication Transaction-Log Reader Subsystem: agent
INVEST1-ParisProduction-12 failed. The process could not execute
'sp_repldone/sp_replcounters' on 'INVEST1'.
COMMENT:One of the replications has failed, sort it out
JOB RUN:(None)
My normal fix for this would be to re-synchronise, but as the publication is
rather big I was wondering if there was a better way of doing it.
Thanks
Peter
try sp_replrestart or sp_repldone (EXEC sp_repldone @.xactid = NULL,
@.xact_segno = NULL, @.numtrans = 0, @.time = 0, @.reset = 1)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Thanks Guys,
Peter
"Peter Nolan" wrote:
> Dear All,
> We are currently using Transactional Replication for a Warm Standby.
> We needed to perform a restore of out Publication Database last night and
> now we are getting the error :-
> DATE/TIME:07/04/2005 09:50:29
> DESCRIPTION:Error: 14151, Severity: 18, State: 1
> Replication-Replication Transaction-Log Reader Subsystem: agent
> INVEST1-ParisProduction-12 failed. The process could not execute
> 'sp_repldone/sp_replcounters' on 'INVEST1'.
> COMMENT:One of the replications has failed, sort it out
> JOB RUN:(None)
> My normal fix for this would be to re-synchronise, but as the publication is
> rather big I was wondering if there was a better way of doing it.
> Thanks
> Peter
We are currently using Transactional Replication for a Warm Standby.
We needed to perform a restore of out Publication Database last night and
now we are getting the error :-
DATE/TIME:07/04/2005 09:50:29
DESCRIPTION:Error: 14151, Severity: 18, State: 1
Replication-Replication Transaction-Log Reader Subsystem: agent
INVEST1-ParisProduction-12 failed. The process could not execute
'sp_repldone/sp_replcounters' on 'INVEST1'.
COMMENT:One of the replications has failed, sort it out
JOB RUN:(None)
My normal fix for this would be to re-synchronise, but as the publication is
rather big I was wondering if there was a better way of doing it.
Thanks
Peter
try sp_replrestart or sp_repldone (EXEC sp_repldone @.xactid = NULL,
@.xact_segno = NULL, @.numtrans = 0, @.time = 0, @.reset = 1)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Thanks Guys,
Peter
"Peter Nolan" wrote:
> Dear All,
> We are currently using Transactional Replication for a Warm Standby.
> We needed to perform a restore of out Publication Database last night and
> now we are getting the error :-
> DATE/TIME:07/04/2005 09:50:29
> DESCRIPTION:Error: 14151, Severity: 18, State: 1
> Replication-Replication Transaction-Log Reader Subsystem: agent
> INVEST1-ParisProduction-12 failed. The process could not execute
> 'sp_repldone/sp_replcounters' on 'INVEST1'.
> COMMENT:One of the replications has failed, sort it out
> JOB RUN:(None)
> My normal fix for this would be to re-synchronise, but as the publication is
> rather big I was wondering if there was a better way of doing it.
> Thanks
> Peter
Wednesday, March 7, 2012
Restore MSSQL7 Backup data to MSSQL2000
Dear all,
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this? Thanks
Rdgs
Ellis
Hi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this? Thanks
> Rdgs
> Ellis
>
|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to[vbcol=seagreen]
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
Do
>
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this? Thanks
Rdgs
Ellis
Hi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this? Thanks
> Rdgs
> Ellis
>
|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to[vbcol=seagreen]
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
Do
>
Restore MSSQL7 Backup data to MSSQL2000
Dear all,
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this' Thanks
Rdgs
EllisHi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this' Thanks
> Rdgs
> Ellis
>|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> > Dear all,
> > I want to transfer data from SQL7 to SQL2000, so I backup the data
and
> > restore it in SQL2000. But it fails to do so due to different version.
Do
> > everyone know how to do this' Thanks
> >
> > Rdgs
> > Ellis
> >
> >
>
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this' Thanks
Rdgs
EllisHi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this' Thanks
> Rdgs
> Ellis
>|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> > Dear all,
> > I want to transfer data from SQL7 to SQL2000, so I backup the data
and
> > restore it in SQL2000. But it fails to do so due to different version.
Do
> > everyone know how to do this' Thanks
> >
> > Rdgs
> > Ellis
> >
> >
>
Restore MSSQL7 Backup data to MSSQL2000
Dear all,
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this' Thanks
Rdgs
EllisHi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this' Thanks
> Rdgs
> Ellis
>|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
Do[vbcol=seagreen]
>
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this' Thanks
Rdgs
EllisHi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this' Thanks
> Rdgs
> Ellis
>|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
Do[vbcol=seagreen]
>
Monday, February 20, 2012
restore master database: no restart
Dear all,
I'm working on MS SQL 2000 SP3.
I'm trying to switch to a new server.
When I tried to restore the master database, the server wouldn't re-start.
so I tried another way, that is to restore user-databases and transfer the
users-accounts (DTS) but it seems that the users cannot connect to the new
server.(no permissions).
I prefere to have a success "restore master database".
what am I missing?
Thanks in advance
jouj
http://support.microsoft.com/default...n-us;Q314546#9 -- Move
Databases between computers running by sql server
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> Dear all,
> I'm working on MS SQL 2000 SP3.
> I'm trying to switch to a new server.
> When I tried to restore the master database, the server wouldn't re-start.
> so I tried another way, that is to restore user-databases and transfer the
> users-accounts (DTS) but it seems that the users cannot connect to the new
> server.(no permissions).
> I prefere to have a success "restore master database".
> what am I missing?
> Thanks in advance
> jouj
>
|||Many thanks
jouj
"Uri Dimant" wrote:
> http://support.microsoft.com/default...n-us;Q314546#9 -- Move
> Databases between computers running by sql server
>
>
>
>
>
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
>
>
I'm working on MS SQL 2000 SP3.
I'm trying to switch to a new server.
When I tried to restore the master database, the server wouldn't re-start.
so I tried another way, that is to restore user-databases and transfer the
users-accounts (DTS) but it seems that the users cannot connect to the new
server.(no permissions).
I prefere to have a success "restore master database".
what am I missing?
Thanks in advance
jouj
http://support.microsoft.com/default...n-us;Q314546#9 -- Move
Databases between computers running by sql server
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> Dear all,
> I'm working on MS SQL 2000 SP3.
> I'm trying to switch to a new server.
> When I tried to restore the master database, the server wouldn't re-start.
> so I tried another way, that is to restore user-databases and transfer the
> users-accounts (DTS) but it seems that the users cannot connect to the new
> server.(no permissions).
> I prefere to have a success "restore master database".
> what am I missing?
> Thanks in advance
> jouj
>
|||Many thanks
jouj
"Uri Dimant" wrote:
> http://support.microsoft.com/default...n-us;Q314546#9 -- Move
> Databases between computers running by sql server
>
>
>
>
>
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
>
>
restore master database: no restart
Dear all,
I'm working on MS SQL 2000 SP3.
I'm trying to switch to a new server.
When I tried to restore the master database, the server wouldn't re-start.
so I tried another way, that is to restore user-databases and transfer the
users-accounts (DTS) but it seems that the users cannot connect to the new
server.(no permissions).
I prefere to have a success "restore master database".
what am I missing'
Thanks in advance
joujhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 -- Move
Databases between computers running by sql server
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> Dear all,
> I'm working on MS SQL 2000 SP3.
> I'm trying to switch to a new server.
> When I tried to restore the master database, the server wouldn't re-start.
> so I tried another way, that is to restore user-databases and transfer the
> users-accounts (DTS) but it seems that the users cannot connect to the new
> server.(no permissions).
> I prefere to have a success "restore master database".
> what am I missing'
> Thanks in advance
> jouj
>|||Many thanks
jouj
"Uri Dimant" wrote:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 -- Move
> Databases between computers running by sql server
>
>
>
>
>
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> > Dear all,
> > I'm working on MS SQL 2000 SP3.
> > I'm trying to switch to a new server.
> > When I tried to restore the master database, the server wouldn't re-start.
> >
> > so I tried another way, that is to restore user-databases and transfer the
> > users-accounts (DTS) but it seems that the users cannot connect to the new
> > server.(no permissions).
> >
> > I prefere to have a success "restore master database".
> >
> > what am I missing'
> >
> > Thanks in advance
> > jouj
> >
> >
>
>
I'm working on MS SQL 2000 SP3.
I'm trying to switch to a new server.
When I tried to restore the master database, the server wouldn't re-start.
so I tried another way, that is to restore user-databases and transfer the
users-accounts (DTS) but it seems that the users cannot connect to the new
server.(no permissions).
I prefere to have a success "restore master database".
what am I missing'
Thanks in advance
joujhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 -- Move
Databases between computers running by sql server
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> Dear all,
> I'm working on MS SQL 2000 SP3.
> I'm trying to switch to a new server.
> When I tried to restore the master database, the server wouldn't re-start.
> so I tried another way, that is to restore user-databases and transfer the
> users-accounts (DTS) but it seems that the users cannot connect to the new
> server.(no permissions).
> I prefere to have a success "restore master database".
> what am I missing'
> Thanks in advance
> jouj
>|||Many thanks
jouj
"Uri Dimant" wrote:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 -- Move
> Databases between computers running by sql server
>
>
>
>
>
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> > Dear all,
> > I'm working on MS SQL 2000 SP3.
> > I'm trying to switch to a new server.
> > When I tried to restore the master database, the server wouldn't re-start.
> >
> > so I tried another way, that is to restore user-databases and transfer the
> > users-accounts (DTS) but it seems that the users cannot connect to the new
> > server.(no permissions).
> >
> > I prefere to have a success "restore master database".
> >
> > what am I missing'
> >
> > Thanks in advance
> > jouj
> >
> >
>
>
restore master database: no restart
Dear all,
I'm working on MS SQL 2000 SP3.
I'm trying to switch to a new server.
When I tried to restore the master database, the server wouldn't re-start.
so I tried another way, that is to restore user-databases and transfer the
users-accounts (DTS) but it seems that the users cannot connect to the new
server.(no permissions).
I prefere to have a success "restore master database".
what am I missing'
Thanks in advance
joujhttp://support.microsoft.com/defaul...en-us;Q314546#9 -- Move
Databases between computers running by sql server
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> Dear all,
> I'm working on MS SQL 2000 SP3.
> I'm trying to switch to a new server.
> When I tried to restore the master database, the server wouldn't re-start.
> so I tried another way, that is to restore user-databases and transfer the
> users-accounts (DTS) but it seems that the users cannot connect to the new
> server.(no permissions).
> I prefere to have a success "restore master database".
> what am I missing'
> Thanks in advance
> jouj
>|||Many thanks
jouj
"Uri Dimant" wrote:
> http://support.microsoft.com/defaul...en-us;Q314546#9 -- Mo
ve
> Databases between computers running by sql server
>
>
>
>
>
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
>
>
I'm working on MS SQL 2000 SP3.
I'm trying to switch to a new server.
When I tried to restore the master database, the server wouldn't re-start.
so I tried another way, that is to restore user-databases and transfer the
users-accounts (DTS) but it seems that the users cannot connect to the new
server.(no permissions).
I prefere to have a success "restore master database".
what am I missing'
Thanks in advance
joujhttp://support.microsoft.com/defaul...en-us;Q314546#9 -- Move
Databases between computers running by sql server
"jouj" <jouj@.discussions.microsoft.com> wrote in message
news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
> Dear all,
> I'm working on MS SQL 2000 SP3.
> I'm trying to switch to a new server.
> When I tried to restore the master database, the server wouldn't re-start.
> so I tried another way, that is to restore user-databases and transfer the
> users-accounts (DTS) but it seems that the users cannot connect to the new
> server.(no permissions).
> I prefere to have a success "restore master database".
> what am I missing'
> Thanks in advance
> jouj
>|||Many thanks
jouj
"Uri Dimant" wrote:
> http://support.microsoft.com/defaul...en-us;Q314546#9 -- Mo
ve
> Databases between computers running by sql server
>
>
>
>
>
>
> "jouj" <jouj@.discussions.microsoft.com> wrote in message
> news:82161742-1B9D-45C5-ABFF-BDF5E384148B@.microsoft.com...
>
>
Restore master database with upgrade from SQL7 to SQL2000
Dear all,
I would like to copy all DB's (incl. master) from a machine running SQL7 to another machine running SQL2000.
I was thinking about a backup/restore method, but I have a problem with 'master'. I get this error message:
The backup of the system database on device T:\BACKUPS\DataBkp\MASTERBKP cannot be restored because it was created by a different version of the server (117441473) than this server (134218112).
How can I upgrade my backup file so that SQL2000 can read it?
Thanks in advance.If I understand you want to port you SQL Server 7.0 environment to a SQL Server 2000 environment and retain your SQL Server 7.0 environment as well.
This may be the long way but how about the following steps:
1) Install SQL Server 7.0 (A) on the soon to be SQL Server 2000 first.
2) Move the databases from the original SQL Server 7 (B) over
3) Upgrade SQL Server 7.0 (A) to 2000|||Right!
It seems to be the only solution. And I would have had the same problem with msdb and model (see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264474)
Thanks for your help!
--marc
I would like to copy all DB's (incl. master) from a machine running SQL7 to another machine running SQL2000.
I was thinking about a backup/restore method, but I have a problem with 'master'. I get this error message:
The backup of the system database on device T:\BACKUPS\DataBkp\MASTERBKP cannot be restored because it was created by a different version of the server (117441473) than this server (134218112).
How can I upgrade my backup file so that SQL2000 can read it?
Thanks in advance.If I understand you want to port you SQL Server 7.0 environment to a SQL Server 2000 environment and retain your SQL Server 7.0 environment as well.
This may be the long way but how about the following steps:
1) Install SQL Server 7.0 (A) on the soon to be SQL Server 2000 first.
2) Move the databases from the original SQL Server 7 (B) over
3) Upgrade SQL Server 7.0 (A) to 2000|||Right!
It seems to be the only solution. And I would have had the same problem with msdb and model (see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264474)
Thanks for your help!
--marc
Subscribe to:
Posts (Atom)