We currently have a nightly full backup of a user database (call it UserDB) going to a backup device, call it Device_A. Every 2 hours we perform a transaction log backup to the same device, Device_A. To simplify, lets say the backup device file is called
device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb transaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there were a failure, and at the time of the failure we had the full database backup, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would not work to restore to a point in time. How would it be done?
Message posted via http://www.sqlmonster.com
since you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQLMonster.c om...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME =
> N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.sqlmonster.com
|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too boneheaded to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILELISTONLY seems to lack the full information I need to identify the the files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements) needed to restore to a poin
t in time. It seems like I need to also utilize the RESTORE HEADERONLY also, but that still does not give me the filenumber. How can I retrieve the specific filenumbers needed to encompass my most recent full backup and the subsequent log backups? I must
be missing something and cannot see or understand it.
Message posted via http://www.sqlmonster.com
|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.c om...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com
|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.c om...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com
Showing posts with label call. Show all posts
Showing posts with label call. Show all posts
Wednesday, March 28, 2012
Restore using backup device
We currently have a nightly full backup of a user database (call it UserDB)
going to a backup device, call it Device_A. Every 2 hours we perform a trans
action log backup to the same device, Device_A. To simplify, lets say the ba
ckup device file is called
device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'user
db backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb tr
ansaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there
were a failure, and at the time of the failure we had the full database back
up, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace
, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would n
ot work to restore to a point in time. How would it be done?
Message posted via http://www.droptable.comsince you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQ
droptable.com...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME =
> N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.droptable.com|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too bone
headed to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILE
LISTONLY seems to lack the full information I need to identify the the files
(the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements)
needed to restore to a poin
t in time. It seems like I need to also utilize the RESTORE HEADERONLY also,
but that still does not give me the filenumber. How can I retrieve the spec
ific filenumbers needed to encompass my most recent full backup and the subs
equent log backups? I must
be missing something and cannot see or understand it.
Message posted via http://www.droptable.com|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQ
droptable.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.droptable.com|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQ
droptable.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.droptable.com
going to a backup device, call it Device_A. Every 2 hours we perform a trans
action log backup to the same device, Device_A. To simplify, lets say the ba
ckup device file is called
device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'user
db backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb tr
ansaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there
were a failure, and at the time of the failure we had the full database back
up, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace
, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would n
ot work to restore to a point in time. How would it be done?
Message posted via http://www.droptable.comsince you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQ
droptable.com...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME =
> N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.droptable.com|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too bone
headed to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILE
LISTONLY seems to lack the full information I need to identify the the files
(the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements)
needed to restore to a poin
t in time. It seems like I need to also utilize the RESTORE HEADERONLY also,
but that still does not give me the filenumber. How can I retrieve the spec
ific filenumbers needed to encompass my most recent full backup and the subs
equent log backups? I must
be missing something and cannot see or understand it.
Message posted via http://www.droptable.com|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQ
droptable.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.droptable.com|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQ
droptable.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.droptable.com
Restore using backup device
We currently have a nightly full backup of a user database (call it UserDB) going to a backup device, call it Device_A. Every 2 hours we perform a transaction log backup to the same device, Device_A. To simplify, lets say the backup device file is called device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb transaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there were a failure, and at the time of the failure we had the full database backup, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would not work to restore to a point in time. How would it be done?
--
Message posted via http://www.sqlmonster.comsince you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQLMonster.com...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME => N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.sqlmonster.com|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too boneheaded to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILELISTONLY seems to lack the full information I need to identify the the files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements) needed to restore to a point in time. It seems like I need to also utilize the RESTORE HEADERONLY also, but that still does not give me the filenumber. How can I retrieve the specific filenumbers needed to encompass my most recent full backup and the subsequent log backups? I must be missing something and cannot see or understand it.
--
Message posted via http://www.sqlmonster.com|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.comsql
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb transaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there were a failure, and at the time of the failure we had the full database backup, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would not work to restore to a point in time. How would it be done?
--
Message posted via http://www.sqlmonster.comsince you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQLMonster.com...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME => N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.sqlmonster.com|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too boneheaded to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILELISTONLY seems to lack the full information I need to identify the the files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements) needed to restore to a point in time. It seems like I need to also utilize the RESTORE HEADERONLY also, but that still does not give me the filenumber. How can I retrieve the specific filenumbers needed to encompass my most recent full backup and the subsequent log backups? I must be missing something and cannot see or understand it.
--
Message posted via http://www.sqlmonster.com|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.comsql
Monday, March 26, 2012
restore tran log
hi;
under what situation will we call for these different actions:
1) restore tran-log (made since previous full bck) in sequence after lastest
back up is restored
2) restore only the latest tran-log (made since previous full bck) after
latest full bck up is restoredI do not think it's possible to just apply the latest transaction log, if
there had been other transaction log backups made since the last full
backup, unless the NO_TRUNCATE option was used. If this was the case, it
would be equivalent to performing a differential backup.
Regards
Ray
"pk" <pk@.> wrote in message news:OSa7NWWEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> hi;
> under what situation will we call for these different actions:
> 1) restore tran-log (made since previous full bck) in sequence after
lastest
> back up is restored
> 2) restore only the latest tran-log (made since previous full bck) after
> latest full bck up is restored
>|||Ray is correct, you can not skip past restoring one tran log and then
restore a more recent log...SQL checks and requires that logs be restored in
the proper order..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:OSa7NWWEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> hi;
> under what situation will we call for these different actions:
> 1) restore tran-log (made since previous full bck) in sequence after
lastest
> back up is restored
> 2) restore only the latest tran-log (made since previous full bck) after
> latest full bck up is restored
>
under what situation will we call for these different actions:
1) restore tran-log (made since previous full bck) in sequence after lastest
back up is restored
2) restore only the latest tran-log (made since previous full bck) after
latest full bck up is restoredI do not think it's possible to just apply the latest transaction log, if
there had been other transaction log backups made since the last full
backup, unless the NO_TRUNCATE option was used. If this was the case, it
would be equivalent to performing a differential backup.
Regards
Ray
"pk" <pk@.> wrote in message news:OSa7NWWEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> hi;
> under what situation will we call for these different actions:
> 1) restore tran-log (made since previous full bck) in sequence after
lastest
> back up is restored
> 2) restore only the latest tran-log (made since previous full bck) after
> latest full bck up is restored
>|||Ray is correct, you can not skip past restoring one tran log and then
restore a more recent log...SQL checks and requires that logs be restored in
the proper order..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:OSa7NWWEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> hi;
> under what situation will we call for these different actions:
> 1) restore tran-log (made since previous full bck) in sequence after
lastest
> back up is restored
> 2) restore only the latest tran-log (made since previous full bck) after
> latest full bck up is restored
>
Tuesday, March 20, 2012
Restore readonly filegroup sqlserver 2000
Hi,
I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:
Take a full backup of TestDatabase
Restore full backup as TestDatabaseDemo
Run long-running data-operations on TestDatabaseDemo
Take a backup of TestDatabaseDemo
Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.
Suppose we arrived at step 5, I am executing the following commands:
Code Snippet
--make full backup of TestDatabase
backup database TestDatabase to DISK='G:\temp\testdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G:\temp\testdatabasedemo.bak' with init
backup log TestDatabase to DISK='g:\temp\testdatabase.log'
--restore secondary filegroup from TestDatabasedemo-backup
restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G:\temp\testdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G:\temp\TestDatabase_Data_secondary.ndf', NORECOVERY
--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G:\temp\testdatabase.bak'
with move 'TestDatabase_Data' to 'G:\temp\TestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin
restore log TestDatabase FROM DISK='g:\temp\testdatabase.log' with recovery
I get the following error:
The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.
When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement
I get error:
The database cannot be recovered because the files have been restored to inconsistent points in time.
How i can restore the read-only filegroup correctly?
Hi Stijn,
Simple answer is that you can't do that. The SECONDARY filegroup is now at a different (more recent) point in time than the rest of the TestDatabase - exactly like the final error message says. What you're essentially trying to do is mix-n-match filegroups from different databases.
Thanks
|||Hi Paul,
Thanks for the answer! I was thinking the mix-n-match would work, because that filegroup is readonly for the database, so applying the transaction log would be good enough for the other (read-write) filegroup, since there couldn't be any inserts/updates/deletes on the secondary filegroup. Which technique is used on very large databases to update a read-only filegroup? Using dts to copy objects from a test-database to the production database? Or is there a more efficient way?
Restore readonly filegroup sqlserver 2000
Hi,
I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:
Take a full backup of TestDatabase
Restore full backup as TestDatabaseDemo
Run long-running data-operations on TestDatabaseDemo
Take a backup of TestDatabaseDemo
Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.
Suppose we arrived at step 5, I am executing the following commands:
Code Snippet
--make full backup of TestDatabase
backup database TestDatabase to DISK='G:\temp\testdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G:\temp\testdatabasedemo.bak' with init
backup log TestDatabase to DISK='g:\temp\testdatabase.log'
--restore secondary filegroup from TestDatabasedemo-backup
restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G:\temp\testdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G:\temp\TestDatabase_Data_secondary.ndf', NORECOVERY
--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G:\temp\testdatabase.bak'
with move 'TestDatabase_Data' to 'G:\temp\TestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin
restore log TestDatabase FROM DISK='g:\temp\testdatabase.log' with recovery
I get the following error:
The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.
When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement
I get error:
The database cannot be recovered because the files have been restored to inconsistent points in time.
How i can restore the read-only filegroup correctly?
Hi Stijn,
Simple answer is that you can't do that. The SECONDARY filegroup is now at a different (more recent) point in time than the rest of the TestDatabase - exactly like the final error message says. What you're essentially trying to do is mix-n-match filegroups from different databases.
Thanks
|||Hi Paul,
Thanks for the answer! I was thinking the mix-n-match would work, because that filegroup is readonly for the database, so applying the transaction log would be good enough for the other (read-write) filegroup, since there couldn't be any inserts/updates/deletes on the secondary filegroup. Which technique is used on very large databases to update a read-only filegroup? Using dts to copy objects from a test-database to the production database? Or is there a more efficient way?
Subscribe to:
Posts (Atom)