I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
T
Technically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
sql
Showing posts with label drive. Show all posts
Showing posts with label drive. Show all posts
Monday, March 26, 2012
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server database
s
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system
.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server database
s
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system
.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
Restore through drive in other server
Hi all,
Does SQL Server 2000 allow restore through drive in other server?
This driver is in the Network.
Thanks for your help,
Alejandra
You can restore from a backup located on another server by specifying a UNC
path. For example:
RESTORE DATABASE MyDatabase
FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
Note that the SQL Server service needs to be running under a domain account
with permissions to the share.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> Hi all,
> Does SQL Server 2000 allow restore through drive in other server?
> This driver is in the Network.
> Thanks for your help,
> Alejandra
|||Hi Dan,
I had this error:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
In log:
2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
'\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
error = 5(Access is denied.).
Any idea?
thanks
"Dan Guzman" wrote:
> You can restore from a backup located on another server by specifying a UNC
> path. For example:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> Note that the SQL Server service needs to be running under a domain account
> with permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
This article covers backups, but the same requirements apply to restores.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
|||Since you are using the administrative share (d$), the SQL Server account
needs to be a member of the administrators group on the server containing
the backup. Alternatively, you can create a new share on that server and
grant the SQL account permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
|||D$ is usually an administrative share, meaning you have to be in the
Administrator's group on the target box (in this case server2).
If your security people won't let you be an administrator on the box (as
opposed to in SQL Server), then make sure your account has read privilges on
the file/directory. You may need a non-administrative share.
Joseph R.P. Maloney, CSP,CCP,CDP
"Alejandra Cornejo" wrote:
[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
|||Hi all,
I had success with RESTORE.
But I didn't understand why after restart SQLServer the databases couldn't
open becase they were in "SUSPECT" state.
Any Idea?
Thanks
"jrpm" wrote:
[vbcol=seagreen]
> D$ is usually an administrative share, meaning you have to be in the
> Administrator's group on the target box (in this case server2).
> If your security people won't let you be an administrator on the box (as
> opposed to in SQL Server), then make sure your account has read privilges on
> the file/directory. You may need a non-administrative share.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Alejandra Cornejo" wrote:
Does SQL Server 2000 allow restore through drive in other server?
This driver is in the Network.
Thanks for your help,
Alejandra
You can restore from a backup located on another server by specifying a UNC
path. For example:
RESTORE DATABASE MyDatabase
FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
Note that the SQL Server service needs to be running under a domain account
with permissions to the share.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> Hi all,
> Does SQL Server 2000 allow restore through drive in other server?
> This driver is in the Network.
> Thanks for your help,
> Alejandra
|||Hi Dan,
I had this error:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
In log:
2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
'\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
error = 5(Access is denied.).
Any idea?
thanks
"Dan Guzman" wrote:
> You can restore from a backup located on another server by specifying a UNC
> path. For example:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> Note that the SQL Server service needs to be running under a domain account
> with permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
This article covers backups, but the same requirements apply to restores.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
|||Since you are using the administrative share (d$), the SQL Server account
needs to be a member of the administrators group on the server containing
the backup. Alternatively, you can create a new share on that server and
grant the SQL account permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
|||D$ is usually an administrative share, meaning you have to be in the
Administrator's group on the target box (in this case server2).
If your security people won't let you be an administrator on the box (as
opposed to in SQL Server), then make sure your account has read privilges on
the file/directory. You may need a non-administrative share.
Joseph R.P. Maloney, CSP,CCP,CDP
"Alejandra Cornejo" wrote:
[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
|||Hi all,
I had success with RESTORE.
But I didn't understand why after restart SQLServer the databases couldn't
open becase they were in "SUSPECT" state.
Any Idea?
Thanks
"jrpm" wrote:
[vbcol=seagreen]
> D$ is usually an administrative share, meaning you have to be in the
> Administrator's group on the target box (in this case server2).
> If your security people won't let you be an administrator on the box (as
> opposed to in SQL Server), then make sure your account has read privilges on
> the file/directory. You may need a non-administrative share.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Alejandra Cornejo" wrote:
Restore through drive in other server
Hi all,
Does SQL Server 2000 allow restore through drive in other server?
This driver is in the Network.
Thanks for your help,
AlejandraYou can restore from a backup located on another server by specifying a UNC
path. For example:
RESTORE DATABASE MyDatabase
FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
Note that the SQL Server service needs to be running under a domain account
with permissions to the share.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> Hi all,
> Does SQL Server 2000 allow restore through drive in other server?
> This driver is in the Network.
> Thanks for your help,
> Alejandra|||Hi Dan,
I had this error:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
In log:
2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
'\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
error = 5(Access is denied.).
Any idea?
thanks
"Dan Guzman" wrote:
> You can restore from a backup located on another server by specifying a UNC
> path. For example:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> Note that the SQL Server service needs to be running under a domain account
> with permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> > Hi all,
> >
> > Does SQL Server 2000 allow restore through drive in other server?
> >
> > This driver is in the Network.
> >
> > Thanks for your help,
> > Alejandra
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
This article covers backups, but the same requirements apply to restores.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>> You can restore from a backup located on another server by specifying a
>> UNC
>> path. For example:
>> RESTORE DATABASE MyDatabase
>> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
>> Note that the SQL Server service needs to be running under a domain
>> account
>> with permissions to the share.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote
>> in
>> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>> > Hi all,
>> >
>> > Does SQL Server 2000 allow restore through drive in other server?
>> >
>> > This driver is in the Network.
>> >
>> > Thanks for your help,
>> > Alejandra
>>|||Since you are using the administrative share (d$), the SQL Server account
needs to be a member of the administrators group on the server containing
the backup. Alternatively, you can create a new share on that server and
grant the SQL account permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>> You can restore from a backup located on another server by specifying a
>> UNC
>> path. For example:
>> RESTORE DATABASE MyDatabase
>> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
>> Note that the SQL Server service needs to be running under a domain
>> account
>> with permissions to the share.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote
>> in
>> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>> > Hi all,
>> >
>> > Does SQL Server 2000 allow restore through drive in other server?
>> >
>> > This driver is in the Network.
>> >
>> > Thanks for your help,
>> > Alejandra
>>|||D$ is usually an administrative share, meaning you have to be in the
Administrator's group on the target box (in this case server2).
If your security people won't let you be an administrator on the box (as
opposed to in SQL Server), then make sure your account has read privilges on
the file/directory. You may need a non-administrative share.
Joseph R.P. Maloney, CSP,CCP,CDP
"Alejandra Cornejo" wrote:
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
> > You can restore from a backup located on another server by specifying a UNC
> > path. For example:
> >
> > RESTORE DATABASE MyDatabase
> > FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> >
> > Note that the SQL Server service needs to be running under a domain account
> > with permissions to the share.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> > message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> > > Hi all,
> > >
> > > Does SQL Server 2000 allow restore through drive in other server?
> > >
> > > This driver is in the Network.
> > >
> > > Thanks for your help,
> > > Alejandra
> >
> >
> >|||Hi all,
I had success with RESTORE.
But I didn't understand why after restart SQLServer the databases couldn't
open becase they were in "SUSPECT" state.
Any Idea?
Thanks
"jrpm" wrote:
> D$ is usually an administrative share, meaning you have to be in the
> Administrator's group on the target box (in this case server2).
> If your security people won't let you be an administrator on the box (as
> opposed to in SQL Server), then make sure your account has read privilges on
> the file/directory. You may need a non-administrative share.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Alejandra Cornejo" wrote:
> > Hi Dan,
> >
> > I had this error:
> > Server: Msg 3201, Level 16, State 2, Line 1
> > Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> > error or device off-line. See the SQL Server error log for more details.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > In log:
> > 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> > '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> > error = 5(Access is denied.).
> >
> > Any idea?
> >
> > thanks
> >
> >
> > "Dan Guzman" wrote:
> >
> > > You can restore from a backup located on another server by specifying a UNC
> > > path. For example:
> > >
> > > RESTORE DATABASE MyDatabase
> > > FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> > >
> > > Note that the SQL Server service needs to be running under a domain account
> > > with permissions to the share.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> > > message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> > > > Hi all,
> > > >
> > > > Does SQL Server 2000 allow restore through drive in other server?
> > > >
> > > > This driver is in the Network.
> > > >
> > > > Thanks for your help,
> > > > Alejandra
> > >
> > >
> > >
Does SQL Server 2000 allow restore through drive in other server?
This driver is in the Network.
Thanks for your help,
AlejandraYou can restore from a backup located on another server by specifying a UNC
path. For example:
RESTORE DATABASE MyDatabase
FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
Note that the SQL Server service needs to be running under a domain account
with permissions to the share.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> Hi all,
> Does SQL Server 2000 allow restore through drive in other server?
> This driver is in the Network.
> Thanks for your help,
> Alejandra|||Hi Dan,
I had this error:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
In log:
2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
'\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
error = 5(Access is denied.).
Any idea?
thanks
"Dan Guzman" wrote:
> You can restore from a backup located on another server by specifying a UNC
> path. For example:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> Note that the SQL Server service needs to be running under a domain account
> with permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> > Hi all,
> >
> > Does SQL Server 2000 allow restore through drive in other server?
> >
> > This driver is in the Network.
> >
> > Thanks for your help,
> > Alejandra
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
This article covers backups, but the same requirements apply to restores.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>> You can restore from a backup located on another server by specifying a
>> UNC
>> path. For example:
>> RESTORE DATABASE MyDatabase
>> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
>> Note that the SQL Server service needs to be running under a domain
>> account
>> with permissions to the share.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote
>> in
>> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>> > Hi all,
>> >
>> > Does SQL Server 2000 allow restore through drive in other server?
>> >
>> > This driver is in the Network.
>> >
>> > Thanks for your help,
>> > Alejandra
>>|||Since you are using the administrative share (d$), the SQL Server account
needs to be a member of the administrators group on the server containing
the backup. Alternatively, you can create a new share on that server and
grant the SQL account permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>> You can restore from a backup located on another server by specifying a
>> UNC
>> path. For example:
>> RESTORE DATABASE MyDatabase
>> FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
>> Note that the SQL Server service needs to be running under a domain
>> account
>> with permissions to the share.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote
>> in
>> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>> > Hi all,
>> >
>> > Does SQL Server 2000 allow restore through drive in other server?
>> >
>> > This driver is in the Network.
>> >
>> > Thanks for your help,
>> > Alejandra
>>|||D$ is usually an administrative share, meaning you have to be in the
Administrator's group on the target box (in this case server2).
If your security people won't let you be an administrator on the box (as
opposed to in SQL Server), then make sure your account has read privilges on
the file/directory. You may need a non-administrative share.
Joseph R.P. Maloney, CSP,CCP,CDP
"Alejandra Cornejo" wrote:
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
> > You can restore from a backup located on another server by specifying a UNC
> > path. For example:
> >
> > RESTORE DATABASE MyDatabase
> > FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> >
> > Note that the SQL Server service needs to be running under a domain account
> > with permissions to the share.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> > message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> > > Hi all,
> > >
> > > Does SQL Server 2000 allow restore through drive in other server?
> > >
> > > This driver is in the Network.
> > >
> > > Thanks for your help,
> > > Alejandra
> >
> >
> >|||Hi all,
I had success with RESTORE.
But I didn't understand why after restart SQLServer the databases couldn't
open becase they were in "SUSPECT" state.
Any Idea?
Thanks
"jrpm" wrote:
> D$ is usually an administrative share, meaning you have to be in the
> Administrator's group on the target box (in this case server2).
> If your security people won't let you be an administrator on the box (as
> opposed to in SQL Server), then make sure your account has read privilges on
> the file/directory. You may need a non-administrative share.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Alejandra Cornejo" wrote:
> > Hi Dan,
> >
> > I had this error:
> > Server: Msg 3201, Level 16, State 2, Line 1
> > Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> > error or device off-line. See the SQL Server error log for more details.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > In log:
> > 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> > '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> > error = 5(Access is denied.).
> >
> > Any idea?
> >
> > thanks
> >
> >
> > "Dan Guzman" wrote:
> >
> > > You can restore from a backup located on another server by specifying a UNC
> > > path. For example:
> > >
> > > RESTORE DATABASE MyDatabase
> > > FROM DISK='\\MyOtherServer\MyBackups\MyDatabase.bak'
> > >
> > > Note that the SQL Server service needs to be running under a domain account
> > > with permissions to the share.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> > > message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> > > > Hi all,
> > > >
> > > > Does SQL Server 2000 allow restore through drive in other server?
> > > >
> > > > This driver is in the Network.
> > > >
> > > > Thanks for your help,
> > > > Alejandra
> > >
> > >
> > >
Restore through drive in other server
Hi all,
Does SQL Server 2000 allow restore through drive in other server?
This driver is in the Network.
Thanks for your help,
AlejandraYou can restore from a backup located on another server by specifying a UNC
path. For example:
RESTORE DATABASE MyDatabase
FROM DISK='\\MyOtherServer\MyBackups\MyDataba
se.bak'
Note that the SQL Server service needs to be running under a domain account
with permissions to the share.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> Hi all,
> Does SQL Server 2000 allow restore through drive in other server?
> This driver is in the Network.
> Thanks for your help,
> Alejandra|||Hi Dan,
I had this error:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
In log:
2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
'\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
error = 5(Access is denied.).
Any idea?
thanks
"Dan Guzman" wrote:
> You can restore from a backup located on another server by specifying a UN
C
> path. For example:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\MyOtherServer\MyBackups\MyDataba
se.bak'
> Note that the SQL Server service needs to be running under a domain accoun
t
> with permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
This article covers backups, but the same requirements apply to restores.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>|||Since you are using the administrative share (d$), the SQL Server account
needs to be a member of the administrators group on the server containing
the backup. Alternatively, you can create a new share on that server and
grant the SQL account permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>|||D$ is usually an administrative share, meaning you have to be in the
Administrator's group on the target box (in this case server2).
If your security people won't let you be an administrator on the box (as
opposed to in SQL Server), then make sure your account has read privilges on
the file/directory. You may need a non-administrative share.
Joseph R.P. Maloney, CSP,CCP,CDP
"Alejandra Cornejo" wrote:
[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>|||Hi all,
I had success with RESTORE.
But I didn't understand why after restart SQLServer the databases couldn't
open becase they were in "SUSPECT" state.
Any Idea?
Thanks
"jrpm" wrote:
[vbcol=seagreen]
> D$ is usually an administrative share, meaning you have to be in the
> Administrator's group on the target box (in this case server2).
> If your security people won't let you be an administrator on the box (as
> opposed to in SQL Server), then make sure your account has read privilges
on
> the file/directory. You may need a non-administrative share.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Alejandra Cornejo" wrote:
>
Does SQL Server 2000 allow restore through drive in other server?
This driver is in the Network.
Thanks for your help,
AlejandraYou can restore from a backup located on another server by specifying a UNC
path. For example:
RESTORE DATABASE MyDatabase
FROM DISK='\\MyOtherServer\MyBackups\MyDataba
se.bak'
Note that the SQL Server service needs to be running under a domain account
with permissions to the share.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
> Hi all,
> Does SQL Server 2000 allow restore through drive in other server?
> This driver is in the Network.
> Thanks for your help,
> Alejandra|||Hi Dan,
I had this error:
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
In log:
2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
'\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
error = 5(Access is denied.).
Any idea?
thanks
"Dan Guzman" wrote:
> You can restore from a backup located on another server by specifying a UN
C
> path. For example:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\MyOtherServer\MyBackups\MyDataba
se.bak'
> Note that the SQL Server service needs to be running under a domain accoun
t
> with permissions to the share.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alejandra Cornejo" <Alejandra Cornejo@.discussions.microsoft.com> wrote in
> message news:00CD0253-9C2C-47A6-8C29-949C3B9606F9@.microsoft.com...
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
This article covers backups, but the same requirements apply to restores.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>|||Since you are using the administrative share (d$), the SQL Server account
needs to be a member of the administrators group on the server containing
the backup. Alternatively, you can create a new share on that server and
grant the SQL account permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alejandra Cornejo" <AlejandraCornejo@.discussions.microsoft.com> wrote in
message news:71DBE4D4-95EB-401A-BBDB-F39783E68E42@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>|||D$ is usually an administrative share, meaning you have to be in the
Administrator's group on the target box (in this case server2).
If your security people won't let you be an administrator on the box (as
opposed to in SQL Server), then make sure your account has read privilges on
the file/directory. You may need a non-administrative share.
Joseph R.P. Maloney, CSP,CCP,CDP
"Alejandra Cornejo" wrote:
[vbcol=seagreen]
> Hi Dan,
> I had this error:
> Server: Msg 3201, Level 16, State 2, Line 1
> Cannot open backup device '\\server2\d$\Mssql\Backup\DB_1309.bak'. Device
> error or device off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> In log:
> 2005-11-03 12:10:53.74 spid255 BackupDiskFile::OpenMedia: Backup device
> '\\server2\d$\Mssql\Backup\DB_1309.bak' failed to open. Operating system
> error = 5(Access is denied.).
> Any idea?
> thanks
>
> "Dan Guzman" wrote:
>|||Hi all,
I had success with RESTORE.
But I didn't understand why after restart SQLServer the databases couldn't
open becase they were in "SUSPECT" state.
Any Idea?
Thanks
"jrpm" wrote:
[vbcol=seagreen]
> D$ is usually an administrative share, meaning you have to be in the
> Administrator's group on the target box (in this case server2).
> If your security people won't let you be an administrator on the box (as
> opposed to in SQL Server), then make sure your account has read privilges
on
> the file/directory. You may need a non-administrative share.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Alejandra Cornejo" wrote:
>
Wednesday, March 21, 2012
Restore SQL database from a network drive
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
You must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
You must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
Restore SQL database from a network drive
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'
.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I u
se
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'
.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I u
se
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
Restore SQL database from a network drive
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>sql
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>sql
Tuesday, March 20, 2012
Restore question
While doing a reorganize on Friday, the drive filled up, and the operation
failed with an error. The database on the next full backup went from 1.5 gig
to 3 gig in size. (full backup occurs every 24 hours).
I have tran-logs created every hour and a full backup from the day before
(Thursday). Can I restore the Thursday backup and then restore all tran-logs
(Thursday, Friday, Saturday, Sunday, Monday, and Tuesday) and expect to have
all my data or can I only restore up to the point of my next full backup
(Saturday)?
Thank you
G.Hi,
Why do you need to restore the database? Because your current database is
still available. Based on your mail I feel that your
Backup file size has grown from 1.5 GB to 3 GB.
I feel that this growth is because of the Reorginization activity. I think
the backup file size can be solved by just shrinking the Log file.
How to shrink the log file:
1. Backup the Transaction log (Use Backup Log command)
2. Use dbcc shrink file to shrink the file
See the below link for shrinking the transaction log file.
http://support.microsoft.com/defaul...b;EN-US;q272318
Thanks
Hari
MCDBA
"Ric Griffy" <alakevue.at@.tampabay.rr.com> wrote in message
news:QA78c.333946$Po1.285683@.twister.tampabay.rr.com...
> While doing a reorganize on Friday, the drive filled up, and the operation
> failed with an error. The database on the next full backup went from 1.5
gig
> to 3 gig in size. (full backup occurs every 24 hours).
> I have tran-logs created every hour and a full backup from the day before
> (Thursday). Can I restore the Thursday backup and then restore all
tran-logs
> (Thursday, Friday, Saturday, Sunday, Monday, and Tuesday) and expect to
have
> all my data or can I only restore up to the point of my next full backup
> (Saturday)?
> Thank you
> G.
>
>|||I agree with Hari that nothing from your description indicates that you need
to do a restore.
To answer the direct question: Yes, you can skip restoring a database
backup. Performing a database backup does not break the chain of log
backups. This is one of the reasons why the log *is not* truncated when you
perform a database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ric Griffy" <alakevue.at@.tampabay.rr.com> wrote in message
news:QA78c.333946$Po1.285683@.twister.tampabay.rr.com...
> While doing a reorganize on Friday, the drive filled up, and the operation
> failed with an error. The database on the next full backup went from 1.5
gig
> to 3 gig in size. (full backup occurs every 24 hours).
> I have tran-logs created every hour and a full backup from the day before
> (Thursday). Can I restore the Thursday backup and then restore all
tran-logs
> (Thursday, Friday, Saturday, Sunday, Monday, and Tuesday) and expect to
have
> all my data or can I only restore up to the point of my next full backup
> (Saturday)?
> Thank you
> G.
>
>
failed with an error. The database on the next full backup went from 1.5 gig
to 3 gig in size. (full backup occurs every 24 hours).
I have tran-logs created every hour and a full backup from the day before
(Thursday). Can I restore the Thursday backup and then restore all tran-logs
(Thursday, Friday, Saturday, Sunday, Monday, and Tuesday) and expect to have
all my data or can I only restore up to the point of my next full backup
(Saturday)?
Thank you
G.Hi,
Why do you need to restore the database? Because your current database is
still available. Based on your mail I feel that your
Backup file size has grown from 1.5 GB to 3 GB.
I feel that this growth is because of the Reorginization activity. I think
the backup file size can be solved by just shrinking the Log file.
How to shrink the log file:
1. Backup the Transaction log (Use Backup Log command)
2. Use dbcc shrink file to shrink the file
See the below link for shrinking the transaction log file.
http://support.microsoft.com/defaul...b;EN-US;q272318
Thanks
Hari
MCDBA
"Ric Griffy" <alakevue.at@.tampabay.rr.com> wrote in message
news:QA78c.333946$Po1.285683@.twister.tampabay.rr.com...
> While doing a reorganize on Friday, the drive filled up, and the operation
> failed with an error. The database on the next full backup went from 1.5
gig
> to 3 gig in size. (full backup occurs every 24 hours).
> I have tran-logs created every hour and a full backup from the day before
> (Thursday). Can I restore the Thursday backup and then restore all
tran-logs
> (Thursday, Friday, Saturday, Sunday, Monday, and Tuesday) and expect to
have
> all my data or can I only restore up to the point of my next full backup
> (Saturday)?
> Thank you
> G.
>
>|||I agree with Hari that nothing from your description indicates that you need
to do a restore.
To answer the direct question: Yes, you can skip restoring a database
backup. Performing a database backup does not break the chain of log
backups. This is one of the reasons why the log *is not* truncated when you
perform a database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ric Griffy" <alakevue.at@.tampabay.rr.com> wrote in message
news:QA78c.333946$Po1.285683@.twister.tampabay.rr.com...
> While doing a reorganize on Friday, the drive filled up, and the operation
> failed with an error. The database on the next full backup went from 1.5
gig
> to 3 gig in size. (full backup occurs every 24 hours).
> I have tran-logs created every hour and a full backup from the day before
> (Thursday). Can I restore the Thursday backup and then restore all
tran-logs
> (Thursday, Friday, Saturday, Sunday, Monday, and Tuesday) and expect to
have
> all my data or can I only restore up to the point of my next full backup
> (Saturday)?
> Thank you
> G.
>
>
Wednesday, March 7, 2012
Restore MSSQL Database, lost LDF-File
Hi.
I've following problem:
- Customer played with his logical drives in a CPQ smart array
- He played too much: he lost the drive with the LDF-File
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
In fact the mode is not wired: what shall he do other when
most important file is missing... :o
- The problem for me now is:
> How can I get the Enterprise Manager or Query Analyzer
up and running again to start a restore?
PS: The restore is to be done from a tape-library running
Veritas Backup Exec + SQL-Agent
PPS: Error-log says (what I already know):
2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:\DEVLOG1\DEVLOG1.ldf' may be incorrect.
Thanks in advance for assistance.
Regards,
Carsten Loefflerdid you try to connect with osql?|||Sounds like someone played with the default database of sa and BUILTIN\ADMINISTRATORS. That's bad. You can see if any of the local administrators can connect to the SQL Server with WndowsNT authentication, but it sounds like you have already tried that.|||If nobody can connect to SQL Server, maybe you want to run rebuildm, then restore the last good master database and other databases as needed.|||Hi Everyone,
thanks for the assistance.
Brett:
No, I didnt try it with OSQL. But thats a good idea. Any hints so far
before I go and investigate myself too much??
MCrowley:
Yip. You're right and I didnt mention it. Yes - I tried it with "sa" and
I tried it with all the others I knew that they were there.
The thing that confuses me: master-DB is still there and running
and didnt get touched...
Tomh:
Mmmh. Do you think I have to do it anyway. I think that master-DB
is up and running.
Regards,
Carsten Loeffler|||The thing that confuses me: master-DB is still there and running
and didnt get touched...
When you log into SQL Server, you always go to your default database first thing. If it is not there, then you get the error "Can not open default database". Has the same thing happened to BUITLIN\ADMINISTRATORS, or can a local Windows admin log in with Windows Authentication?|||Hi.
I've following problem:
<snip>
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
</snip>
Since we know MCrowley is right, and if the statement above is true, then **all** users were defaulted to the database where the ldf is missing.
If I'm reading the quoted part right, not a single user can log in. If that is the case, then master will have to be restored. And since you have to be able to log in to rebuild master, rebuildm will be needed to get back to a place where a system admin can login. So maybe rebuildm, restore database where .ldf is missing, them restore master.|||And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.|||And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.
I am gonna bet all the whisky lying in my almirah it will be possible to login using BUILTIN\ADMINISTRATORS. It just seems that the guy is not aware of the account.
Suggestion : Try logging in with the radio button set to Windows Authentictation while you are logged in with a Windows System Administrator account ...
I've following problem:
- Customer played with his logical drives in a CPQ smart array
- He played too much: he lost the drive with the LDF-File
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
In fact the mode is not wired: what shall he do other when
most important file is missing... :o
- The problem for me now is:
> How can I get the Enterprise Manager or Query Analyzer
up and running again to start a restore?
PS: The restore is to be done from a tape-library running
Veritas Backup Exec + SQL-Agent
PPS: Error-log says (what I already know):
2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:\DEVLOG1\DEVLOG1.ldf' may be incorrect.
Thanks in advance for assistance.
Regards,
Carsten Loefflerdid you try to connect with osql?|||Sounds like someone played with the default database of sa and BUILTIN\ADMINISTRATORS. That's bad. You can see if any of the local administrators can connect to the SQL Server with WndowsNT authentication, but it sounds like you have already tried that.|||If nobody can connect to SQL Server, maybe you want to run rebuildm, then restore the last good master database and other databases as needed.|||Hi Everyone,
thanks for the assistance.
Brett:
No, I didnt try it with OSQL. But thats a good idea. Any hints so far
before I go and investigate myself too much??
MCrowley:
Yip. You're right and I didnt mention it. Yes - I tried it with "sa" and
I tried it with all the others I knew that they were there.
The thing that confuses me: master-DB is still there and running
and didnt get touched...
Tomh:
Mmmh. Do you think I have to do it anyway. I think that master-DB
is up and running.
Regards,
Carsten Loeffler|||The thing that confuses me: master-DB is still there and running
and didnt get touched...
When you log into SQL Server, you always go to your default database first thing. If it is not there, then you get the error "Can not open default database". Has the same thing happened to BUITLIN\ADMINISTRATORS, or can a local Windows admin log in with Windows Authentication?|||Hi.
I've following problem:
<snip>
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
</snip>
Since we know MCrowley is right, and if the statement above is true, then **all** users were defaulted to the database where the ldf is missing.
If I'm reading the quoted part right, not a single user can log in. If that is the case, then master will have to be restored. And since you have to be able to log in to rebuild master, rebuildm will be needed to get back to a place where a system admin can login. So maybe rebuildm, restore database where .ldf is missing, them restore master.|||And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.|||And ->immediately<- reset sa's default database back to master. As well as BUILTIN\ADMINISTRATORS.
I am gonna bet all the whisky lying in my almirah it will be possible to login using BUILTIN\ADMINISTRATORS. It just seems that the guy is not aware of the account.
Suggestion : Try logging in with the radio button set to Windows Authentictation while you are logged in with a Windows System Administrator account ...
Monday, February 20, 2012
Restore master db to new server with different drive paths
I am trying to restore a a system to another server with different paths. I can restore the master db ok but when itry to start it up using sqlservr -c -m etc i get a device activation error on the model db because it is still looking to the old path. is
there any way around this.
If not what are the implications of keeping the base installed master db on the new server and attaching all the other databases.
Thanks
Archie
Have a look at
http://support.microsoft.com/default...;en-us;Q224071
"Archie" <Archie@.discussions.microsoft.com> wrote in message
news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I am trying to restore a a system to another server with different paths.
I can restore the master db ok but when itry to start it up using
sqlservr -c -m etc i get a device activation error on the model db because
it is still looking to the old path. is there any way around this.
> If not what are the implications of keeping the base installed master db
on the new server and attaching all the other databases.
> Thanks
|||Thanks for reply
Got it resolved, wasn't thinking at first got roun dby using the t3068 flag and changing.
Thanks
"Uri Dimant" wrote:
> Archie
> Have a look at
> http://support.microsoft.com/default...;en-us;Q224071
>
> "Archie" <Archie@.discussions.microsoft.com> wrote in message
> news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I can restore the master db ok but when itry to start it up using
> sqlservr -c -m etc i get a device activation error on the model db because
> it is still looking to the old path. is there any way around this.
> on the new server and attaching all the other databases.
>
>
there any way around this.
If not what are the implications of keeping the base installed master db on the new server and attaching all the other databases.
Thanks
Archie
Have a look at
http://support.microsoft.com/default...;en-us;Q224071
"Archie" <Archie@.discussions.microsoft.com> wrote in message
news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I am trying to restore a a system to another server with different paths.
I can restore the master db ok but when itry to start it up using
sqlservr -c -m etc i get a device activation error on the model db because
it is still looking to the old path. is there any way around this.
> If not what are the implications of keeping the base installed master db
on the new server and attaching all the other databases.
> Thanks
|||Thanks for reply
Got it resolved, wasn't thinking at first got roun dby using the t3068 flag and changing.
Thanks
"Uri Dimant" wrote:
> Archie
> Have a look at
> http://support.microsoft.com/default...;en-us;Q224071
>
> "Archie" <Archie@.discussions.microsoft.com> wrote in message
> news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I can restore the master db ok but when itry to start it up using
> sqlservr -c -m etc i get a device activation error on the model db because
> it is still looking to the old path. is there any way around this.
> on the new server and attaching all the other databases.
>
>
Restore master db to new server with different drive paths
I am trying to restore a a system to another server with different paths. I can restore the master db ok but when itry to start it up using sqlservr -c -m etc i get a device activation error on the model db because it is still looking to the old path. is there any way around this.
If not what are the implications of keeping the base installed master db on the new server and attaching all the other databases.
ThanksArchie
Have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071&ID=kb;en-us;Q224071
"Archie" <Archie@.discussions.microsoft.com> wrote in message
news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I am trying to restore a a system to another server with different paths.
I can restore the master db ok but when itry to start it up using
sqlservr -c -m etc i get a device activation error on the model db because
it is still looking to the old path. is there any way around this.
> If not what are the implications of keeping the base installed master db
on the new server and attaching all the other databases.
> Thanks|||Thanks for reply
Got it resolved, wasn't thinking at first got roun dby using the t3068 flag and changing.
Thanks
"Uri Dimant" wrote:
> Archie
> Have a look at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071&ID=kb;en-us;Q224071
>
> "Archie" <Archie@.discussions.microsoft.com> wrote in message
> news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> > I am trying to restore a a system to another server with different paths.
> I can restore the master db ok but when itry to start it up using
> sqlservr -c -m etc i get a device activation error on the model db because
> it is still looking to the old path. is there any way around this.
> >
> > If not what are the implications of keeping the base installed master db
> on the new server and attaching all the other databases.
> >
> > Thanks
>
>
If not what are the implications of keeping the base installed master db on the new server and attaching all the other databases.
ThanksArchie
Have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071&ID=kb;en-us;Q224071
"Archie" <Archie@.discussions.microsoft.com> wrote in message
news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I am trying to restore a a system to another server with different paths.
I can restore the master db ok but when itry to start it up using
sqlservr -c -m etc i get a device activation error on the model db because
it is still looking to the old path. is there any way around this.
> If not what are the implications of keeping the base installed master db
on the new server and attaching all the other databases.
> Thanks|||Thanks for reply
Got it resolved, wasn't thinking at first got roun dby using the t3068 flag and changing.
Thanks
"Uri Dimant" wrote:
> Archie
> Have a look at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071&ID=kb;en-us;Q224071
>
> "Archie" <Archie@.discussions.microsoft.com> wrote in message
> news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> > I am trying to restore a a system to another server with different paths.
> I can restore the master db ok but when itry to start it up using
> sqlservr -c -m etc i get a device activation error on the model db because
> it is still looking to the old path. is there any way around this.
> >
> > If not what are the implications of keeping the base installed master db
> on the new server and attaching all the other databases.
> >
> > Thanks
>
>
Restore master db to new server with different drive paths
I am trying to restore a a system to another server with different paths. I
can restore the master db ok but when itry to start it up using sqlservr -c
-m etc i get a device activation error on the model db because it is still l
ooking to the old path. is
there any way around this.
If not what are the implications of keeping the base installed master db on
the new server and attaching all the other databases.
ThanksArchie
Have a look at
1" target="_blank">http://support.microsoft.com/defaul...Q22407
1
"Archie" <Archie@.discussions.microsoft.com> wrote in message
news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I am trying to restore a a system to another server with different paths.
I can restore the master db ok but when itry to start it up using
sqlservr -c -m etc i get a device activation error on the model db because
it is still looking to the old path. is there any way around this.
> If not what are the implications of keeping the base installed master db
on the new server and attaching all the other databases.
> Thanks|||Thanks for reply
Got it resolved, wasn't thinking at first got roun dby using the t3068 flag
and changing.
Thanks
"Uri Dimant" wrote:
> Archie
> Have a look at
> 071" target="_blank">http://support.microsoft.com/defaul...Q224
071
>
> "Archie" <Archie@.discussions.microsoft.com> wrote in message
> news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I can restore the master db ok but when itry to start it up using
> sqlservr -c -m etc i get a device activation error on the model db because
> it is still looking to the old path. is there any way around this.
> on the new server and attaching all the other databases.
>
>
can restore the master db ok but when itry to start it up using sqlservr -c
-m etc i get a device activation error on the model db because it is still l
ooking to the old path. is
there any way around this.
If not what are the implications of keeping the base installed master db on
the new server and attaching all the other databases.
ThanksArchie
Have a look at
1" target="_blank">http://support.microsoft.com/defaul...Q22407
1
"Archie" <Archie@.discussions.microsoft.com> wrote in message
news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I am trying to restore a a system to another server with different paths.
I can restore the master db ok but when itry to start it up using
sqlservr -c -m etc i get a device activation error on the model db because
it is still looking to the old path. is there any way around this.
> If not what are the implications of keeping the base installed master db
on the new server and attaching all the other databases.
> Thanks|||Thanks for reply
Got it resolved, wasn't thinking at first got roun dby using the t3068 flag
and changing.
Thanks
"Uri Dimant" wrote:
> Archie
> Have a look at
> 071" target="_blank">http://support.microsoft.com/defaul...Q224
071
>
> "Archie" <Archie@.discussions.microsoft.com> wrote in message
> news:52B86D59-3CCF-4986-838F-B1AE3D1F5D38@.microsoft.com...
> I can restore the master db ok but when itry to start it up using
> sqlservr -c -m etc i get a device activation error on the model db because
> it is still looking to the old path. is there any way around this.
> on the new server and attaching all the other databases.
>
>
Restore MASTER
Hi there,
My RAID data drive failed, and I need to restore the MASTER DB. When I
put the server into single user mode (sqlservr -m ) and launch
Enterprise Manager, or Query Analyzer, I can't log into to perform the
restore. It informs me that the server is running in single user mode
(which it is) and that only one admin can be connected.. I have even
attempt to connect using a named SQL user.
How can I overcome this problem?Make sure that the SQLServerAgent service is not running.
"Rowan Prior" wrote:
> Hi there,
> My RAID data drive failed, and I need to restore the MASTER DB. When I
> put the server into single user mode (sqlservr -m ) and launch
> Enterprise Manager, or Query Analyzer, I can't log into to perform the
> restore. It informs me that the server is running in single user mode
> (which it is) and that only one admin can be connected.. I have even
> attempt to connect using a named SQL user.
> How can I overcome this problem?
>
My RAID data drive failed, and I need to restore the MASTER DB. When I
put the server into single user mode (sqlservr -m ) and launch
Enterprise Manager, or Query Analyzer, I can't log into to perform the
restore. It informs me that the server is running in single user mode
(which it is) and that only one admin can be connected.. I have even
attempt to connect using a named SQL user.
How can I overcome this problem?Make sure that the SQLServerAgent service is not running.
"Rowan Prior" wrote:
> Hi there,
> My RAID data drive failed, and I need to restore the MASTER DB. When I
> put the server into single user mode (sqlservr -m ) and launch
> Enterprise Manager, or Query Analyzer, I can't log into to perform the
> restore. It informs me that the server is running in single user mode
> (which it is) and that only one admin can be connected.. I have even
> attempt to connect using a named SQL user.
> How can I overcome this problem?
>
Subscribe to:
Posts (Atom)