0

I am scheduling a auto backup job plan in SQL Server 2014 Management Studio through a management plan. I can easily schedule job plan in same server but I want to store database .bak files on another server, but the problem is inside backup database task tool there is one tab destination.

Back Up Database Task-> Destination

  • Create a backup file for every database
  • Create a sub directory for each database (here there is no option for select another server directory).

Is there any way I can take directly auto backup to another server through Management Studio?

user good
  • 107
  • 2
  • 9

1 Answers1

0

Presumably your auto backup location is a named drive (e.g. 'E:\'... on the same server.

Have you tried creating a linked server and executing the backup by sproc script? Obviously, the File location cannot be 'E:...' any more, but will have to be the network location e.g. \\SERVER01\e$\.....

Edit:

You can try executing this by script as such:

    BACKUP DATABASE [Test] TO  DISK = N'\\Server\c$\folderpath' WITH NOFORMAT, NOINIT,  NAME = N'Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

You may run into an access denied error. You need to set up access for the SQL Service Service account to write to whatever folder you need it to, please see here for more info:

Cannot open backup device. Operating System error 5

Community
  • 1
  • 1
TJB
  • 787
  • 1
  • 8
  • 29