Restore: Cannot Open Backup Device

I am attempting to restore a database onto "Server_A" from a backup file
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgent
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via droptable.com" wrote:

> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAgent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file on
> Server_B, it seems there is something not set up right on SQL Server. Please
> help.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
>
|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:[vbcol=seagreen]
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>[quoted text clipped - 13 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAgent
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file on
>Server_B, it seems there is something not set up right on SQL Server. Please
>help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Robert,

> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article/...025/14025.html
AMB
"Robert Richards via droptable.com" wrote:

> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
>
sql

Restore: Cannot Open Backup Device

I am attempting to restore a database onto "Server_A" from a backup file
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backup
file.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgen
t
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backu
pfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile
.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via droptable.com" wrote:

> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[back
upfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAg
ent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[bac
kupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfi
le.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file
on
> Server_B, it seems there is something not set up right on SQL Server. Plea
se
> help.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
>|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:[vbcol=seagreen]
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>
>[quoted text clipped - 13 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backu
pfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAge
nt
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[back
upfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfil
e.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file o
n
>Server_B, it seems there is something not set up right on SQL Server. Pleas
e
>help.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Robert,

> The account has full control on "\\server_b\e$\backups". As a check, I UNC
'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article...4025/14025.html
AMB
"Robert Richards via droptable.com" wrote:

> The account has full control on "\\server_b\e$\backups". As a check, I UNC
'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
>

Restore: Cannot Open Backup Device

I am attempting to restore a database onto "Server_A" from a backup file
located on a disk from a different server, "Server_B". My T-SQL is the
following:
restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
'
1. I have a domain account set up, and the domain account is running SQLAgent
on Server_A. This service has been stopped and restarted, and even
MSSQLServer service has been stopped and restarted.
2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
bak]'.
3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
' I get returned "Access is Denied"
What am I missing? Since I can successfully UNC to the directory and file on
Server_B, it seems there is something not set up right on SQL Server. Please
help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1You need to give read / write permission on the shared folder
"\\server_b\e$\backups" to the account used by sql server and sql agent
services.
AMB
"Robert Richards via SQLMonster.com" wrote:
> I am attempting to restore a database onto "Server_A" from a backup file
> located on a disk from a different server, "Server_B". My T-SQL is the
> following:
> restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
> '
> 1. I have a domain account set up, and the domain account is running SQLAgent
> on Server_A. This service has been stopped and restarted, and even
> MSSQLServer service has been stopped and restarted.
> 2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
> bak]'.
> 3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
> ' I get returned "Access is Denied"
> What am I missing? Since I can successfully UNC to the directory and file on
> Server_B, it seems there is something not set up right on SQL Server. Please
> help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
>|||The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
over to the directory and created a text file, just to test.
Alejandro Mesa wrote:
>You need to give read / write permission on the shared folder
>"\\server_b\e$\backups" to the account used by sql server and sql agent
>services.
>AMB
>> I am attempting to restore a database onto "Server_A" from a backup file
>> located on a disk from a different server, "Server_B". My T-SQL is the
>[quoted text clipped - 13 lines]
>> Server_B, it seems there is something not set up right on SQL Server. Please
>> help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||RESOLUTION:
Not only did I have to have the domain account running SQLAgent, but I also
had to have the domain account running MSSQLServer on Server_A.
Robert Richards wrote:
>I am attempting to restore a database onto "Server_A" from a backup file
>located on a disk from a different server, "Server_B". My T-SQL is the
>following:
>restore database [dbname] from disk = '\\server_b\e$\backups\[backupfile.bak]
>'
>1. I have a domain account set up, and the domain account is running SQLAgent
>on Server_A. This service has been stopped and restarted, and even
>MSSQLServer service has been stopped and restarted.
>2. From Server_A I can UNC successfully to '\\server_b\e$\backups\[backupfile.
>bak]'.
>3. When I run master..xp_cmdshell 'dir \\server_b\e$\backups\[backupfile.bak]
>' I get returned "Access is Denied"
>What am I missing? Since I can successfully UNC to the directory and file on
>Server_B, it seems there is something not set up right on SQL Server. Please
>help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Robert,
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
I am not talking about your account, I am talking about the domain account
used by sql server and sql agent services.
Why can't I backup/restore my SQL Server database to a share on another
server?
http://www.windowsitpro.com/Article/ArticleID/14025/14025.html
AMB
"Robert Richards via SQLMonster.com" wrote:
> The account has full control on "\\server_b\e$\backups". As a check, I UNC'd
> over to the directory and created a text file, just to test.
> Alejandro Mesa wrote:
> >You need to give read / write permission on the shared folder
> >"\\server_b\e$\backups" to the account used by sql server and sql agent
> >services.
> >
> >AMB
> >
> >> I am attempting to restore a database onto "Server_A" from a backup file
> >> located on a disk from a different server, "Server_B". My T-SQL is the
> >[quoted text clipped - 13 lines]
> >> Server_B, it seems there is something not set up right on SQL Server. Please
> >> help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
>

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.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

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

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

restore two databases from one device

Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!
Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!
|||Darn NT!!!

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.[vbcol=seagreen]
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
from[vbcol=seagreen]
and
>
>.
>
sql

restore two databases from one device

Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?

>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:275f201c46381$aa08f020$a501280a@.phx
.gbl...
from[vbcol=seagreen]
and[vbcol=seagreen]
>
>.
>

restore two databases from one device

Is it possible to have two restores running at the same
time restoring two databases to two different servers from
the same backup device? For example, I have server A and
server B. I want to restore pubs to both A and B at the
same time from last night's backup device. Thanks!Hi,
A Single file can be opened for reading by only one process.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:275f201c46381$aa08f020$a501280a@.phx.gbl...
> Is it possible to have two restores running at the same
> time restoring two databases to two different servers from
> the same backup device? For example, I have server A and
> server B. I want to restore pubs to both A and B at the
> same time from last night's backup device. Thanks!|||Darn NT!!!
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>|||No, files can be opened either shared or exclusive.
But sorry, I don't know which mode SQL does.
Why not just try it?
>--Original Message--
>Hi,
>A Single file can be opened for reading by only one
process.
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:275f201c46381$aa08f020$a501280a@.phx.gbl...
>> Is it possible to have two restores running at the same
>> time restoring two databases to two different servers
from
>> the same backup device? For example, I have server A
and
>> server B. I want to restore pubs to both A and B at the
>> same time from last night's backup device. Thanks!
>
>.
>

restore question

I use sql2000/sp3 in w2k/sp3,if i can
restore a database from a net device direct(not copy the
bak file to local disk)?I think you want to do something like this
RESTORE DATABASE FROM DISK = '\\ServerName\ShareName\FileName'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"peng" <zhoupeng1971@.yahoo.com> wrote in message
news:022d01c35cbd$68fde570$a001280a@.phx.gbl...
> I use sql2000/sp3 in w2k/sp3,if i can
> restore a database from a net device direct(not copy the
> bak file to local disk)?|||Hi Peng,
Make sure that the SQL Server Startup service account has the permission to
access the network for this to work.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Restore problem... "The media family on device '%ls' is incorrectly formed"

A client keeps sending me .BAK files. A couple have worked, but I
have gotten this error on many:
Error 3241 Severity 16 State 1
The media family on device '%ls' is incorrectly formed. SQL Server
cannot process this media family.
If I view the contents of the .BAK (in Enterprise Manager), it says it
is INCOMPLETE.
This indicates that maybe the backup was bad. However, since they have
sent me so many bad ones, my question is why are these backups turning
out bad?
Some details:
-The client is using SQL Server 2000 SP4, so am I
-I am running Win2K Server, they are running Win 2003 server.
-I looked at KB 297104 which deals with this error, but that is for
pre-SP4.
-When I run a restore verifyonly, I get the same error
Since I am off-site and can't go to the client site, any ideas on what
they may be doing wrong or what we can do for me to get a legitimate
copy of this database on my server? I am going crazy with these files
that won't load and don't know what to tell my client.have them run a verify before sending them to you. This should guarantee a
valid backup.
--
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Mike C" <michaeljc70@.hotmail.com> wrote in message
news:1187736521.741462.231380@.l22g2000prc.googlegroups.com...
>A client keeps sending me .BAK files. A couple have worked, but I
> have gotten this error on many:
> Error 3241 Severity 16 State 1
> The media family on device '%ls' is incorrectly formed. SQL Server
> cannot process this media family.
> If I view the contents of the .BAK (in Enterprise Manager), it says it
> is INCOMPLETE.
> This indicates that maybe the backup was bad. However, since they have
> sent me so many bad ones, my question is why are these backups turning
> out bad?
> Some details:
> -The client is using SQL Server 2000 SP4, so am I
> -I am running Win2K Server, they are running Win 2003 server.
> -I looked at KB 297104 which deals with this error, but that is for
> pre-SP4.
> -When I run a restore verifyonly, I get the same error
> Since I am off-site and can't go to the client site, any ideas on what
> they may be doing wrong or what we can do for me to get a legitimate
> copy of this database on my server? I am going crazy with these files
> that won't load and don't know what to tell my client.
>|||Hello,
First of all tell your client to do a restore with verify only or header only
to test, on their side, the correctness of the backup. If it's possible, tell
the client to do a full restore of the backup.
Second, if the first worked out well, send back the media to the client and
tell them to try the above process.
It is possible that between different copies of the backup file it becomes
corrupt. If the transfer of the backup file it is done over wan this is
something sure. A second possibility it is this file is part of a multivolume
backup. Sql Server has the option to backup a database to more than one file
to speed up the backup process.
Hope this helps you solve the problem.
I recommend you reading this too
http://www.dbforums.com/showthread.php?t=682406.
Cheers.
Mike C wrote:
>A client keeps sending me .BAK files. A couple have worked, but I
>have gotten this error on many:
>Error 3241 Severity 16 State 1
>The media family on device '%ls' is incorrectly formed. SQL Server
>cannot process this media family.
>If I view the contents of the .BAK (in Enterprise Manager), it says it
>is INCOMPLETE.
>This indicates that maybe the backup was bad. However, since they have
>sent me so many bad ones, my question is why are these backups turning
>out bad?
>Some details:
>-The client is using SQL Server 2000 SP4, so am I
>-I am running Win2K Server, they are running Win 2003 server.
>-I looked at KB 297104 which deals with this error, but that is for
>pre-SP4.
>-When I run a restore verifyonly, I get the same error
>Since I am off-site and can't go to the client site, any ideas on what
>they may be doing wrong or what we can do for me to get a legitimate
>copy of this database on my server? I am going crazy with these files
>that won't load and don't know what to tell my client.|||On Aug 22, 5:08 am, "flooriin" <u36842@.uwe> wrote:
> Hello,
> First of all tell your client to do arestorewith verify only or header only
> to test, on their side, the correctness of the backup. If it's possible, tell
> the client to do a fullrestoreof the backup.
> Second, if the first worked out well, send back themediato the client and
> tell them to try the above process.
> It is possible that between different copies of the backup file it becomes
> corrupt. If the transfer of the backup file it is done over wan this is
> something sure. A second possibility it is this file is part of a multivolume
> backup. Sql Server has the option to backup a database to more than one file
> to speed up the backup process.
> Hope this helps you solve theproblem.
> I recommend you reading this toohttp://www.dbforums.com/showthread.php?t=682406.
> Cheers.
> Mike C wrote:
> >A client keeps sending me .BAK files. A couple have worked, but I
> >have gotten this error on many:
> >Error 3241 Severity 16 State 1
> >Themediafamily on device '%ls' is incorrectly formed. SQL Server
> >cannot process thismediafamily.
> >If I view the contents of the .BAK (in Enterprise Manager), it says it
> >is INCOMPLETE.
> >This indicates that maybe the backup was bad. However, since they have
> >sent me so many bad ones, my question is why are these backups turning
> >out bad?
> >Some details:
> >-The client is using SQL Server 2000 SP4, so am I
> >-I am running Win2K Server, they are running Win 2003 server.
> >-I looked at KB 297104 which deals with this error, but that is for
> >pre-SP4.
> >-When I run arestoreverifyonly, I get the same error
> >Since I am off-site and can't go to the client site, any ideas on what
> >they may be doing wrong or what we can do for me to get a legitimate
> >copy of this database on my server? I am going crazy with these files
> >that won't load and don't know what to tell my client.
The client said they did a verify when they did the backup. I will
have them check to see if RESTORE VERIFYONLY works. Assuming the file
is good, I can only think of 2 unlikely issues:
-The file is becoming corrupt when they zip it up. Since it is bigger
than a DVD, that is the only way to get it to me.
-Running W2K Server vs. W2003 Server is making some dofference.|||On Aug 22, 5:08 am, "flooriin" <u36842@.uwe> wrote:
> Hello,
> First of all tell your client to do a restore with verify only or header only
> to test, on their side, the correctness of the backup. If it's possible, tell
> the client to do a full restore of the backup.
> Second, if the first worked out well, send back the media to the client and
> tell them to try the above process.
> It is possible that between different copies of the backup file it becomes
> corrupt. If the transfer of the backup file it is done over wan this is
> something sure. A second possibility it is this file is part of a multivolume
> backup. Sql Server has the option to backup a database to more than one file
> to speed up the backup process.
> Hope this helps you solve the problem.
> I recommend you reading this toohttp://www.dbforums.com/showthread.php?t=682406.
> Cheers.
> Mike C wrote:
> >A client keeps sending me .BAK files. A couple have worked, but I
> >have gotten this error on many:
> >Error 3241 Severity 16 State 1
> >The media family on device '%ls' is incorrectly formed. SQL Server
> >cannot process this media family.
> >If I view the contents of the .BAK (in Enterprise Manager), it says it
> >is INCOMPLETE.
> >This indicates that maybe the backup was bad. However, since they have
> >sent me so many bad ones, my question is why are these backups turning
> >out bad?
> >Some details:
> >-The client is using SQL Server 2000 SP4, so am I
> >-I am running Win2K Server, they are running Win 2003 server.
> >-I looked at KB 297104 which deals with this error, but that is for
> >pre-SP4.
> >-When I run a restore verifyonly, I get the same error
> >Since I am off-site and can't go to the client site, any ideas on what
> >they may be doing wrong or what we can do for me to get a legitimate
> >copy of this database on my server? I am going crazy with these files
> >that won't load and don't know what to tell my client.
The restore verifyonly or backuponly works at the client site. I
verified versions. They are using Sql Server 2000 SP3 (plus a
cumulative patch) while I am running SP4. I can try and go back to
SP3, but that seems unlikely culprit (more likely if their version
were higher than mine). Any other ideas?|||On Aug 22, 5:08 am, "flooriin" <u36842@.uwe> wrote:
> Hello,
> First of all tell your client to do a restore with verify only or header only
> to test, on their side, the correctness of the backup. If it's possible, tell
> the client to do a full restore of the backup.
> Second, if the first worked out well, send back the media to the client and
> tell them to try the above process.
> It is possible that between different copies of the backup file it becomes
> corrupt. If the transfer of the backup file it is done over wan this is
> something sure. A second possibility it is this file is part of a multivolume
> backup. Sql Server has the option to backup a database to more than one file
> to speed up the backup process.
> Hope this helps you solve the problem.
> I recommend you reading this toohttp://www.dbforums.com/showthread.php?t=682406.
> Cheers.
> Mike C wrote:
> >A client keeps sending me .BAK files. A couple have worked, but I
> >have gotten this error on many:
> >Error 3241 Severity 16 State 1
> >The media family on device '%ls' is incorrectly formed. SQL Server
> >cannot process this media family.
> >If I view the contents of the .BAK (in Enterprise Manager), it says it
> >is INCOMPLETE.
> >This indicates that maybe the backup was bad. However, since they have
> >sent me so many bad ones, my question is why are these backups turning
> >out bad?
> >Some details:
> >-The client is using SQL Server 2000 SP4, so am I
> >-I am running Win2K Server, they are running Win 2003 server.
> >-I looked at KB 297104 which deals with this error, but that is for
> >pre-SP4.
> >-When I run a restore verifyonly, I get the same error
> >Since I am off-site and can't go to the client site, any ideas on what
> >they may be doing wrong or what we can do for me to get a legitimate
> >copy of this database on my server? I am going crazy with these files
> >that won't load and don't know what to tell my client.
Do you think me being on w2K and them on W2003 server makes a
difference?|||I do not believe the problem arise because of different Windows versions. I
think it's more about database and the process to backup and deliver the
database. You have said that are cases when the backup was restored
successfully. What is different from your current situation? I am more
concern about different Sql Server version. I know about a bug in SP3 when a
differential database backup could miss to save some extents ... but you are
talking about full backup.
Read this too
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=167366&p=2
Maybe it will help you. Read the last post and try out the utility told
there.
Cheers.
Mike C wrote:
>> Hello,
>[quoted text clipped - 40 lines]
>> >copy of this database on my server? I am going crazy with these files
>> >that won't load and don't know what to tell my client.
>Do you think me being on w2K and them on W2003 server makes a
>difference?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||On Aug 24, 6:25 am, "flooriin via SQLMonster.com" <u36842@.uwe> wrote:
> I do not believe the problem arise because of different Windows versions. I
> think it's more about database and the process to backup and deliver the
> database. You have said that are cases when the backup was restored
> successfully. What is different from your current situation? I am more
> concern about different Sql Server version. I know about a bug in SP3 when a
> differential database backup could miss to save some extents ... but you are
> talking about full backup.
> Read this toohttp://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&mes...
> Maybe it will help you. Read the last post and try out the utility told
> there.
> Cheers.
> Mike C wrote:
> >> Hello,
> >[quoted text clipped - 40 lines]
> >> >copy of this database on my server? I am going crazy with these files
> >> >that won't load and don't know what to tell my client.
> >Do you think me being on w2K and them on W2003 server makes a
> >difference?
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
I installed SQL Server 2000 MSDE on an XP box and I was able to
restore the backup. That means the file is fine. I am just going to
work with that since I don't need any of the enterprise features.
This is a short project and it isn't worth spending 100 hrs trying to
figure out what the problem is.

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.ukI think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx
.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.uk
I think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

Restore problem - anyone seen this error before?

I am having a problem restoring a database (SQL2000).
from a .bak file device. When I attempt a restore I get
the following message
"The media set for database xxx has 3 family members but
only 1 are provided. All memebers must be provided.
Restore database is terminating abnormally"
The .bak file has been zipped and unzipped with winzip9.
Could this have corrupted the file?
Regards
Neil Gibson
ngibson(revovethis)@.mclgroup.co.ukI think you may have made the database backup to multiple files. You need
to find the other 2 backup files to be able to restore the database.
Look in the msdb..backupmediafamily table on the server you made the backup
to find the names of the other 2 files if you don't know them.
<plug> If you need small backups, give MiniSQLBackup a spin. </plug>
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Neil Gibson" <anonymous@.discussions.microsoft.com> wrote in message
news:17b8a01c4494d$a81ed980$a001280a@.phx.gbl...
> I am having a problem restoring a database (SQL2000).
> from a .bak file device. When I attempt a restore I get
> the following message
> "The media set for database xxx has 3 family members but
> only 1 are provided. All memebers must be provided.
> Restore database is terminating abnormally"
> The .bak file has been zipped and unzipped with winzip9.
> Could this have corrupted the file?
> Regards
> Neil Gibson
> ngibson(revovethis)@.mclgroup.co.uk

restore master problem

When i try to restore master db with
sqlservr -c -m -T4022
ISQL -E , restore database master from device='d:\dump\masterdump.dat'
restore fail with message
Error: 3151, Severity: 21, State: 0.
Error: 9001, Severity: 21, State: 1.
and sqlserver terminated..
I have try this.
1. backup a new masterdump from other sqlserver b and and restore to server
a .. same problem
2. take this masterdump from server b and restore to other server, sucessful.
3. backup master from sqlserver(a) and restore to sqlserver(a) succesful.
i using sqlserver sp3
win2003 standard edition..
any suggestion?Hi,
Did you try rebuilding the master with rebuildm utlility and then try to
restore again?
--
Danijel Novak
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:3184264F-DC6F-4A12-B8B9-E7F8C508B14A@.microsoft.com...
> When i try to restore master db with
> sqlservr -c -m -T4022
> ISQL -E , restore database master from device='d:\dump\masterdump.dat'
> restore fail with message
> Error: 3151, Severity: 21, State: 0.
> Error: 9001, Severity: 21, State: 1.
> and sqlserver terminated..
> I have try this.
> 1. backup a new masterdump from other sqlserver b and and restore to
> server
> a .. same problem
> 2. take this masterdump from server b and restore to other server,
> sucessful.
> 3. backup master from sqlserver(a) and restore to sqlserver(a) succesful.
> i using sqlserver sp3
> win2003 standard edition..
> any suggestion?
>

restore master problem

When i try to restore master db with
sqlservr -c -m -T4022
ISQL -E , restore database master from device='d:\dump\masterdump.dat'
restore fail with message
Error: 3151, Severity: 21, State: 0.
Error: 9001, Severity: 21, State: 1.
and sqlserver terminated..
I have try this.
1. backup a new masterdump from other sqlserver b and and restore to server
a .. same problem
2. take this masterdump from server b and restore to other server, sucessfu
l.
3. backup master from sqlserver(a) and restore to sqlserver(a) succesful.
i using sqlserver sp3
win2003 standard edition..
any suggestion?Hi,
Did you try rebuilding the master with rebuildm utlility and then try to
restore again?
Danijel Novak
"soonyu" <soonyu@.discussions.microsoft.com> wrote in message
news:3184264F-DC6F-4A12-B8B9-E7F8C508B14A@.microsoft.com...
> When i try to restore master db with
> sqlservr -c -m -T4022
> ISQL -E , restore database master from device='d:\dump\masterdump.dat'
> restore fail with message
> Error: 3151, Severity: 21, State: 0.
> Error: 9001, Severity: 21, State: 1.
> and sqlserver terminated..
> I have try this.
> 1. backup a new masterdump from other sqlserver b and and restore to
> server
> a .. same problem
> 2. take this masterdump from server b and restore to other server,
> sucessful.
> 3. backup master from sqlserver(a) and restore to sqlserver(a) succesful.
> i using sqlserver sp3
> win2003 standard edition..
> any suggestion?
>