Backup to Network Drive: sp

Showing posts with label sp_addumpdevice. Show all posts

Sunday, March 11, 2012

Backward Compatability in Yukon using backup devices?

My peers have informed me that I should no longer code my backup routine
using a backup alias via sp_addumpdevice because it is 1) Provided only for
backward compatability and 2) Will not be supported in the future.
Should I not be using a backup alias for these reasons? Yukon provides the
commands but no where have I seen where it's only there to support backward
compatability and that it is targeted to be phased out. Can someone show me
this?
Thanks.
-CqlboyI know nothing of dump devices going away any time soon.
--
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
> My peers have informed me that I should no longer code my backup routine
> using a backup alias via sp_addumpdevice because it is 1) Provided only
> for
> backward compatability and 2) Will not be supported in the future.
> Should I not be using a backup alias for these reasons? Yukon provides
> the
> commands but no where have I seen where it's only there to support
> backward
> compatability and that it is targeted to be phased out. Can someone show
> me
> this?
> Thanks.
> -Cqlboy|||Thanx. Thought so. My fellow peers
are misinformed.
-Cqlboy
"Andrew J. Kelly" wrote:
> I know nothing of dump devices going away any time soon.
> --
> Andrew J. Kelly SQL MVP
>
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
> > My peers have informed me that I should no longer code my backup routine
> > using a backup alias via sp_addumpdevice because it is 1) Provided only
> > for
> > backward compatability and 2) Will not be supported in the future.
> >
> > Should I not be using a backup alias for these reasons? Yukon provides
> > the
> > commands but no where have I seen where it's only there to support
> > backward
> > compatability and that it is targeted to be phased out. Can someone show
> > me
> > this?
> >
> > Thanks.
> >
> > -Cqlboy
>
>|||I'm with Andrew. Ask your colleagues for a reference. It is easy to make claims you can't back up...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:B4044417-68A3-401F-B1AF-7BCE37AE1BC9@.microsoft.com...
> Thanx. Thought so. My fellow peers
> are misinformed.
> -Cqlboy
> "Andrew J. Kelly" wrote:
>> I know nothing of dump devices going away any time soon.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
>> news:4332439A-418D-450F-BD94-5579374C40F3@.microsoft.com...
>> > My peers have informed me that I should no longer code my backup routine
>> > using a backup alias via sp_addumpdevice because it is 1) Provided only
>> > for
>> > backward compatability and 2) Will not be supported in the future.
>> >
>> > Should I not be using a backup alias for these reasons? Yukon provides
>> > the
>> > commands but no where have I seen where it's only there to support
>> > backward
>> > compatability and that it is targeted to be phased out. Can someone show
>> > me
>> > this?
>> >
>> > Thanks.
>> >
>> > -Cqlboy
>>

Thursday, March 8, 2012

backups...

Hello all I have this T-SQL string.
Use master
Exec sp_addumpdevice 'disk', 'dbEvents_backup',
'\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
Backup Database dbEvents to dbEvents_backup
Exec sp_dropdevice 'dbEvents_backup'
I have two questions
When the backup runs it isn't writing over the existing backup. It
seems to be appending the information.
Secondly, is it this an effective approach in:
adding the dumpdevice
backing it up
and then deleting the device
thanksvncntj@.hotmail.com wrote:
> Hello all I have this T-SQL string.
>
> Use master
> Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> Backup Database dbEvents to dbEvents_backup
> Exec sp_dropdevice 'dbEvents_backup'
>
> I have two questions
> When the backup runs it isn't writing over the existing backup. It
> seems to be appending the information.
> Secondly, is it this an effective approach in:
> adding the dumpdevice
> backing it up
> and then deleting the device
> thanks
>
You can skip the creation/deletion of the backup device by using TO DISK
in your backup command. To overwrite the existing backup, add WITH INIT
to the backup command. See Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--020001090101040705090406
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
vncntj@.hotmail.com wrote:
> Hello all I have this T-SQL string.
>
> Use master
> Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> Backup Database dbEvents to dbEvents_backup
> Exec sp_dropdevice 'dbEvents_backup'
>
> I have two questions
> When the backup runs it isn't writing over the existing backup. It
> seems to be appending the information.
> Secondly, is it this an effective approach in:
> adding the dumpdevice
> backing it up
> and then deleting the device
> thanks
>
Hi
Like mentioned by Tracy, you can skip creation of the device by backing
up to disk, but I wonder why you drop the device after every backup if
it's the same device you add again next time?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--020001090101040705090406
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:vncntj@.hotmail.com">vncntj@.hotmail.com</a> wrote:
<blockquote
cite="mid1152217677.737502.210350@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Hello all I have this T-SQL string.
Use master
Exec sp_addumpdevice 'disk', 'dbEvents_backup',
'\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
Backup Database dbEvents to dbEvents_backup
Exec sp_dropdevice 'dbEvents_backup'
I have two questions
When the backup runs it isn't writing over the existing backup. It
seems to be appending the information.
Secondly, is it this an effective approach in:
adding the dumpdevice
backing it up
and then deleting the device
thanks
</pre>
</blockquote>
Hi 
 
Like mentioned by Tracy, you can skip creation of the device by backing
up to disk, but I wonder why you drop the device after every backup if
it's the same device you add again next time? 
 
 
-- 
Regards 
Steen Schlüter Persson 
Databaseadministrator / Systemadministrator 

</body>
</html>
--020001090101040705090406--|||YOU GUYS ROCK!!!!!!!!!!
Steen Persson (DK) wrote:
> vncntj@.hotmail.com wrote:
> > Hello all I have this T-SQL string.
> >
> >
> > Use master
> > Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> > '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> > Backup Database dbEvents to dbEvents_backup
> > Exec sp_dropdevice 'dbEvents_backup'
> >
> >
> > I have two questions
> > When the backup runs it isn't writing over the existing backup. It
> > seems to be appending the information.
> >
> > Secondly, is it this an effective approach in:
> >
> > adding the dumpdevice
> > backing it up
> > and then deleting the device
> >
> > thanks
> >
> >
> Hi
> Like mentioned by Tracy, you can skip creation of the device by backing
> up to disk, but I wonder why you drop the device after every backup if
> it's the same device you add again next time?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
> --020001090101040705090406
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 1287
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:vncntj@.hotmail.com">=vncntj@.hotmail.com</a> wrote:
> <blockquote
> cite=3D"mid1152217677.737502.210350@.m73g2000cwd.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">Hello all I have this T-SQL string.
>
> Use master
> Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> Backup Database dbEvents to dbEvents_backup
> Exec sp_dropdevice 'dbEvents_backup'
>
> I have two questions
> When the backup runs it isn't writing over the existing backup. It
> seems to be appending the information.
> Secondly, is it this an effective approach in:
> adding the dumpdevice
> backing it up
> and then deleting the device
> thanks
> </pre>
> </blockquote>
> Hi 
> 
> Like mentioned by Tracy, you can skip creation of the device by backing
> up to disk, but I wonder why you drop the device after every backup if
> it's the same device you add again next time? 
> 
> 
> -- 
> Regards 
> Steen Schlüter Persson 
> Databaseadministrator / Systemadministrator 
> 
> </body>
> </html>
> > --020001090101040705090406--

Friday, February 10, 2012

Backup to Network Resource from a Server Machine Service Account

SQL Server 2000 SP4
Trying to backup to a network resource when SQL uses the machine service
account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
work because of permissions.
Fairly new to DBA stuff so please have patience with me.
How can you do this?
Regards,
Richard.
You would need to have SQL Server using a service account that has permissions on that share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.
|||I don't think Windows security works like that:
Services Using The System Account
Services that use the system account start in the system context (without
credentials). In Windows NT 3.5 and later, Windows NT services with no
credentials (no domain name, user name, or password) that attempt to connect
to network resources are denied access because they have no credentials and
they are using a null session. For additional information, click the article
number below to view the article in the Microsoft Knowledge Base:
from: http://support.microsoft.com/kb/132679
Essentially the local system account has no credentials so cannot
athenticate to anything on the network.
Is there anyway round this?
"Tibor Karaszi" wrote:

> You would need to have SQL Server using a service account that has permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
>
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.
|||>I don't think Windows security works like that:
I don't see in what way what I said differs from the text you quoted.

> Is there anyway round this?
Not AFAIK...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...[vbcol=seagreen]
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to connect
> to network resources are denied access because they have no credentials and
> they are using a null session. For additional information, click the article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
|||As Tibor pointed out you need an account to run SQL Server Service under
that has the appropriate permissions to that share. A LOCAL account has no
permissions outside that box. So change it to a Domain account with the
appropriate permissions.
Andrew J. Kelly SQL MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...[vbcol=seagreen]
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to
> connect
> to network resources are denied access because they have no credentials
> and
> they are using a null session. For additional information, click the
> article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
|||Hello,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write
access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.

Backup to Network Resource from a Server Machine Service Account

SQL Server 2000 SP4
Trying to backup to a network resource when SQL uses the machine service
account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
work because of permissions.
Fairly new to DBA stuff so please have patience with me.
How can you do this?
Regards,
Richard.You would need to have SQL Server using a service account that has permissions on that share.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.|||I don't think Windows security works like that:
Services Using The System Account
Services that use the system account start in the system context (without
credentials). In Windows NT 3.5 and later, Windows NT services with no
credentials (no domain name, user name, or password) that attempt to connect
to network resources are denied access because they have no credentials and
they are using a null session. For additional information, click the article
number below to view the article in the Microsoft Knowledge Base:
from: http://support.microsoft.com/kb/132679
Essentially the local system account has no credentials so cannot
athenticate to anything on the network.
Is there anyway round this?
"Tibor Karaszi" wrote:
> You would need to have SQL Server using a service account that has permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> > SQL Server 2000 SP4
> >
> > Trying to backup to a network resource when SQL uses the machine service
> > account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
> > '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
> > work because of permissions.
> >
> > Fairly new to DBA stuff so please have patience with me.
> >
> > How can you do this?
> >
> > Regards,
> > Richard.
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.|||>I don't think Windows security works like that:
I don't see in what way what I said differs from the text you quoted.
> Is there anyway round this?
Not AFAIK...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to connect
> to network resources are denied access because they have no credentials and
> they are using a null session. For additional information, click the article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
>> You would need to have SQL Server using a service account that has permissions on that share.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
>> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
>> > SQL Server 2000 SP4
>> >
>> > Trying to backup to a network resource when SQL uses the machine service
>> > account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
>> > '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
>> > work because of permissions.
>> >
>> > Fairly new to DBA stuff so please have patience with me.
>> >
>> > How can you do this?
>> >
>> > Regards,
>> > Richard.
>>|||As Tibor pointed out you need an account to run SQL Server Service under
that has the appropriate permissions to that share. A LOCAL account has no
permissions outside that box. So change it to a Domain account with the
appropriate permissions.
--
Andrew J. Kelly SQL MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to
> connect
> to network resources are denied access because they have no credentials
> and
> they are using a null session. For additional information, click the
> article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
>> You would need to have SQL Server using a service account that has
>> permissions on that share.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
>> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
>> > SQL Server 2000 SP4
>> >
>> > Trying to backup to a network resource when SQL uses the machine
>> > service
>> > account. Obviously something like EXEC sp_addumpdevice 'disk',
>> > 'device_name',
>> > '\\filer_name\share_name\path\file_name.ext' would be used but this
>> > doesn't
>> > work because of permissions.
>> >
>> > Fairly new to DBA stuff so please have patience with me.
>> >
>> > How can you do this?
>> >
>> > Regards,
>> > Richard.
>>|||Hello,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write
access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.

Backup to Network Resource from a Server Machine Service Account

backup to network drive

I'm trying to create a job to backup to a network drive. The following SQL does not execute successfully:
USE master
EXEC sp_addumpdevice 'disk', 'Thurs6', '\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
BACKUP LOG Mock8 TO Thurs6 WITH INIT
However, if I change the destination file to one located on the SQL server, it executes fine. I've also tried this using a mapped drive letter, and that doesn't work either. Any thoughts?
Backups are done under the account that sql server runs under and not the
account you are logged on as. So if SQL Server is not running under a domain
account with access to that share it can not see it.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||It could be that the account used by SQL Server doesn't have permissions to
that share. Can you post the exact error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||Hi,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
USE master
go
EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
go
BACKUP LOG Mock8 TO Thurs6 WITH INIT
Thanks
Hari
MCDBA
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?

Backup to NAS - Permissions

I am trying to backup my database to a NAS device. I have added a dump device
successfully:
[USE master
EXEC sp_addumpdevice 'disk', 'mydb_bu_nas', '\\nas1\dbbackups\mydb_bu.BAK']
...but when I try to run the backup:
[use mydb
BACKUP DATABASE mydb TO mydb_bu_nas]
...I get the error:
[Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or device
off-line. See the SQL Server error log for more details.]
The event log shows the following details:
[18204 :
BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
failed to create. Operating system error = 5(Access is denied.).]
The MSSQLSERVER Service is running under the local system account. I assume
that this is the user that would need permissions to write to the NAS. If I
change the MSSQLSERVER Service to run as a Domain User are there any other
implications that I should be aware of?
Backups are always done under the account that SQL Server service uses and
must have rights to the share on the NAS. You should use a Domain account
that has the minimum permissions to do the things you need done and no more.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6BE37CF7-54A1-4DE4-A71B-DE5F58A991FD@.microsoft.com...
>I am trying to backup my database to a NAS device. I have added a dump
>device
> successfully:
> [USE master
> EXEC sp_addumpdevice 'disk', 'mydb_bu_nas',
> '\\nas1\dbbackups\mydb_bu.BAK']
> ..but when I try to run the backup:
> [use mydb
> BACKUP DATABASE mydb TO mydb_bu_nas]
> ..I get the error:
> [Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or device
> off-line. See the SQL Server error log for more details.]
> The event log shows the following details:
> [18204 :
> BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
> failed to create. Operating system error = 5(Access is denied.).]
> The MSSQLSERVER Service is running under the local system account. I
> assume
> that this is the user that would need permissions to write to the NAS. If
> I
> change the MSSQLSERVER Service to run as a Domain User are there any other
> implications that I should be aware of?
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default...b;en-us;555128
The requirements are the same whether you are backing up to a NAS device or
a Windows file share.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6BE37CF7-54A1-4DE4-A71B-DE5F58A991FD@.microsoft.com...
>I am trying to backup my database to a NAS device. I have added a dump
>device
> successfully:
> [USE master
> EXEC sp_addumpdevice 'disk', 'mydb_bu_nas',
> '\\nas1\dbbackups\mydb_bu.BAK']
> ..but when I try to run the backup:
> [use mydb
> BACKUP DATABASE mydb TO mydb_bu_nas]
> ..I get the error:
> [Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or device
> off-line. See the SQL Server error log for more details.]
> The event log shows the following details:
> [18204 :
> BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
> failed to create. Operating system error = 5(Access is denied.).]
> The MSSQLSERVER Service is running under the local system account. I
> assume
> that this is the user that would need permissions to write to the NAS. If
> I
> change the MSSQLSERVER Service to run as a Domain User are there any other
> implications that I should be aware of?
>

Backup to NAS - Permissions

I am trying to backup my database to a NAS device. I have added a dump devic
e
successfully:
[USE master
EXEC sp_addumpdevice 'disk', 'mydb_bu_nas', '\\nas1\dbbackups\mydb_bu.BAK']
..but when I try to run the backup:
[use mydb
BACKUP DATABASE mydb TO mydb_bu_nas]
..I get the error:
[Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or devic
e
off-line. See the SQL Server error log for more details.]
The event log shows the following details:
[18204 :
BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
failed to create. Operating system error = 5(Access is denied.).]
The MSSQLSERVER Service is running under the local system account. I assume
that this is the user that would need permissions to write to the NAS. If I
change the MSSQLSERVER Service to run as a Domain User are there any other
implications that I should be aware of?Backups are always done under the account that SQL Server service uses and
must have rights to the share on the NAS. You should use a Domain account
that has the minimum permissions to do the things you need done and no more.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6BE37CF7-54A1-4DE4-A71B-DE5F58A991FD@.microsoft.com...
>I am trying to backup my database to a NAS device. I have added a dump
>device
> successfully:
> [USE master
> EXEC sp_addumpdevice 'disk', 'mydb_bu_nas',
> '\\nas1\dbbackups\mydb_bu.BAK']
> ..but when I try to run the backup:
> [use mydb
> BACKUP DATABASE mydb TO mydb_bu_nas]
> ..I get the error:
> [Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or dev
ice
> off-line. See the SQL Server error log for more details.]
> The event log shows the following details:
> [18204 :
> BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
> failed to create. Operating system error = 5(Access is denied.).]
> The MSSQLSERVER Service is running under the local system account. I
> assume
> that this is the user that would need permissions to write to the NAS. If
> I
> change the MSSQLSERVER Service to run as a Domain User are there any other
> implications that I should be aware of?
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/defaul...kb;en-us;555128
The requirements are the same whether you are backing up to a NAS device or
a Windows file share.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6BE37CF7-54A1-4DE4-A71B-DE5F58A991FD@.microsoft.com...
>I am trying to backup my database to a NAS device. I have added a dump
>device
> successfully:
> [USE master
> EXEC sp_addumpdevice 'disk', 'mydb_bu_nas',
> '\\nas1\dbbackups\mydb_bu.BAK']
> ..but when I try to run the backup:
> [use mydb
> BACKUP DATABASE mydb TO mydb_bu_nas]
> ..I get the error:
> [Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or dev
ice
> off-line. See the SQL Server error log for more details.]
> The event log shows the following details:
> [18204 :
> BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
> failed to create. Operating system error = 5(Access is denied.).]
> The MSSQLSERVER Service is running under the local system account. I
> assume
> that this is the user that would need permissions to write to the NAS. If
> I
> change the MSSQLSERVER Service to run as a Domain User are there any other
> implications that I should be aware of?
>

Backup to NAS - Permissions

I am trying to backup my database to a NAS device. I have added a dump device
successfully:
[USE master
EXEC sp_addumpdevice 'disk', 'mydb_bu_nas', '\\nas1\dbbackups\mydb_bu.BAK']
..but when I try to run the backup:
[use mydb
BACKUP DATABASE mydb TO mydb_bu_nas]
..I get the error:
[Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or device
off-line. See the SQL Server error log for more details.]
The event log shows the following details:
[18204 :
BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
failed to create. Operating system error = 5(Access is denied.).]
The MSSQLSERVER Service is running under the local system account. I assume
that this is the user that would need permissions to write to the NAS. If I
change the MSSQLSERVER Service to run as a Domain User are there any other
implications that I should be aware of?Backups are always done under the account that SQL Server service uses and
must have rights to the share on the NAS. You should use a Domain account
that has the minimum permissions to do the things you need done and no more.
--
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6BE37CF7-54A1-4DE4-A71B-DE5F58A991FD@.microsoft.com...
>I am trying to backup my database to a NAS device. I have added a dump
>device
> successfully:
> [USE master
> EXEC sp_addumpdevice 'disk', 'mydb_bu_nas',
> '\\nas1\dbbackups\mydb_bu.BAK']
> ..but when I try to run the backup:
> [use mydb
> BACKUP DATABASE mydb TO mydb_bu_nas]
> ..I get the error:
> [Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or device
> off-line. See the SQL Server error log for more details.]
> The event log shows the following details:
> [18204 :
> BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
> failed to create. Operating system error = 5(Access is denied.).]
> The MSSQLSERVER Service is running under the local system account. I
> assume
> that this is the user that would need permissions to write to the NAS. If
> I
> change the MSSQLSERVER Service to run as a Domain User are there any other
> implications that I should be aware of?
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
The requirements are the same whether you are backing up to a NAS device or
a Windows file share.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6BE37CF7-54A1-4DE4-A71B-DE5F58A991FD@.microsoft.com...
>I am trying to backup my database to a NAS device. I have added a dump
>device
> successfully:
> [USE master
> EXEC sp_addumpdevice 'disk', 'mydb_bu_nas',
> '\\nas1\dbbackups\mydb_bu.BAK']
> ..but when I try to run the backup:
> [use mydb
> BACKUP DATABASE mydb TO mydb_bu_nas]
> ..I get the error:
> [Cannot open backup device 'digitaldraw_bu_nas_2'. Device error or device
> off-line. See the SQL Server error log for more details.]
> The event log shows the following details:
> [18204 :
> BackupDiskFile::CreateMedia: Backup device '\\nas1\dbbackups\mydb_bu.BAK'
> failed to create. Operating system error = 5(Access is denied.).]
> The MSSQLSERVER Service is running under the local system account. I
> assume
> that this is the user that would need permissions to write to the NAS. If
> I
> change the MSSQLSERVER Service to run as a Domain User are there any other
> implications that I should be aware of?
>

Sunday, March 11, 2012

Backward Compatability in Yukon using backup devices?

Backward Compatability in Yukon using backup devices?

Backward Compatability in Yukon using backup devices?

Thursday, March 8, 2012

backups...

backups...

Friday, February 10, 2012

Backup to Network Resource from a Server Machine Service Account

Backup to Network Resource from a Server Machine Service Account

Backup to Network Resource from a Server Machine Service Account

backup to network drive

Backup to NAS - Permissions

Backup to NAS - Permissions

Backup to NAS - Permissions

Backup to Network Drive

Blog Archive

About Me