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 perform. Show all posts
Showing posts with label perform. 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
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
Saturday, February 25, 2012
Restore MDF and NDF to a single DB on a new location
I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
Max
No you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
Max
No you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
Restore MDF and NDF to a single DB on a new location
I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only hav
e
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only hav
e
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
Restore MDF and NDF to a single DB on a new location
I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
--
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
--
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
Subscribe to:
Posts (Atom)