Please is there way to create procedure to create Backup Device by using different user account, for user account without acces to local computer and not defined in AD too, because description from MSDN - Backing Up to a File on a Network Share seems like doesn't allowing that, and as I tried without luck on my side too

I looking for different way, to create device on fly (virtuals on VMWare) another as follows:

  • to map disk area as HDD drive to Windows OS

  • map (for example) as Network drive and use this path

  • connect network drive from cmd by saving credencial to the OS

  • define a bunch of procedures (or save in SSMS) for each of DB separatelly

  • 1
    Your question is not clear. Are you asking about automating backups to a network drive? The part about "by using different user account, for use account without access to local computer and not defined in AD too" is very strange. If you have access to SQL Server, and the account SQL Server is using has access to the network drive, members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles can run the backup.
    – Hannah Vernon
    Apr 19 '13 at 13:43
  • @Max Vernon not true for 2008R2 and in the case that you'll leave to saving account & password to WinOS, this is reason why I'm asking this question, add autentification to the Backup procedure, sure standard ways are known,
    – mKorbel
    Apr 19 '13 at 13:55
  • 2
    What is "not true for 2008R2"? Like I said, if the account SQL Server is running under has access to the network, BACKUP DATABASE xxx TO DISK='\\server\share\file'; DOES work.
    – Hannah Vernon
    Apr 19 '13 at 14:08
  • does if access is stored in Win OS, for example activated from start -> search - '\\server\share\file', then popup window asked for credencials, with choice to save this credencials, my question is how to add user and password as parameters for creating Backup on fly,
    – mKorbel
    Apr 19 '13 at 15:07

If you want to backup to a network share, there are several steps you must take.

You must modify the security settings of the share to allow SQL Server access to the share. To do this you need to know the name of the account used by the SQL Server Service.

Use SQL Server Configuration Manager to determine which account SQL Server is using:

enter image description here

If the SQL Server service is using NT AUTHORITY\NETWORKSERVICE the name of the account you need to use would be the name of the machine SQL Server is running on appended with a $, for instance: MACHINE$

Once you have the account name, modify the share security by right-clicking the shared folder, clicking Properties, then going to the 'Security' tab. Give the account at least Modify rights.

enter image description here

Go to the 'Sharing' tab, and ensure the account also has at least Change rights.

enter image description here

In SQL Server, configure the backup job to use the name of the share as the location of the backups; for instance, using T-SQL you might have:

BACKUP DATABASE MyDatabase TO DISK = '\\Machine\Share\MyDatabase_db.bak'
  • +1 thanks but corresponding wiht my 2nd. of points map (for example) as Network drive and use this path
    – mKorbel
    Apr 19 '13 at 17:17
  • Don't use a network drive name like P:\ , use the actual UNC path instead, like \\Server\Share\ . Using the drive letter is problematic. For a discussion about this see dba.stackexchange.com/a/38713/10832
    – Hannah Vernon
    Apr 19 '13 at 18:04
  • I know about "only initialized HDD in Windows" could be used for backup as named drive, my reason is to hide "my" diskarea (standard and supported disk area for VMWare), otherwise all whole space is used (stolen:-) by another admins for their own purposes,
    – mKorbel
    Apr 19 '13 at 18:09
  • Sorry - I have no idea what you are trying to say.
    – Hannah Vernon
    Apr 19 '13 at 18:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.