question

chitrarekhasaha avatar image
chitrarekhasaha asked

How to backup database in external drive?

We have a standalone installation of database on sqlserver 2008R2 and want ot backup the database in external drive since the size of the back up file is 18G. Used script as well as Maintenance plan and got the error, how do I grant permission so that I can store in external USB drive

Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'P:\Backups.SQL\PWNT\PWNT_backup_2018_11_29_135357_7686135.bak'. Operating system error 3(The system cannot find the path specified.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

backup
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ ·

Is the usbdrive plugged into the sql server machine or into a workstation that is connected via ssms?

0 Likes 0 ·

6 Answers

·
chitrarekhasaha avatar image
chitrarekhasaha answered

usbdrive is plugged into standalone sqlserver machine. I can manually do the backup but using the scripot or management plan create the error

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chitrarekhasaha avatar image
chitrarekhasaha answered

SqlServer Agent error Date11/29/2018 4:39:19 PM LogJob History (Fullbackup.Subplan_1) Step ID0 ServerI26130 Job NameFullbackup.Subplan_1 Step Name(Job outcome) Duration00:00:00 Sql Severity0 Sql Message ID0 Operator Emailed Operator Net sent Operator Paged Retries Attempted0

Backup error Message The job failed. The owner (I26130\advent) of job Fullbackup.Subplan_1 does not have server access.

TSQL script from maintenance plan

EXECUTE master.dbo.xp_create_subdir N'P:\Backups.SQL\\PWNT' GO BACKUP DATABASE [PWNT] TO DISK = N'P:\Backups.SQL\\PWNT\PWNT_backup_2018_11_29_164233_7410202.bak' WITH NOFORMAT, NOINIT, NAME = N'PWNT_backup_2018_11_29_164233_7400435', SKIP, REWIND, NOUNLOAD, STATS = 10

But when I wrote the script :

Backup Database PWNT To Disk = P:\Backups.SQL\PWNT.bak was successfully backing up to the external drive.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ ·

If you change the job owner to sa will the maintenance plan job succeed?

If you place your command that worked into a job step does it succeed?

0 Likes 0 ·
chitrarekhasaha avatar image
chitrarekhasaha answered

EXECUTE master.dbo.xp_create_subdir N'P:\Backups.SQL\PWNT' GO BACKUP DATABASE [PWNT] TO DISK = N'P:\Backups.SQL\PWNT\PWNT_backup_2018_11_29_164356_6648036.bak' WITH NOFORMAT, NOINIT, NAME = N'PWNT_backup_2018_11_29_164356_6648036', SKIP, REWIND, NOUNLOAD, STATS = 10

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chitrarekhasaha avatar image
chitrarekhasaha answered

Sa account is disabled but I will try will try the other option but not sure why this is an issue.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ ·

you can still have sa own the job - that's just a shortcut notation to use the SQL Agent account. You could also set the job owner to the sql agent account. At any rate, I26130\advent doesn't seem to have what it needs.

0 Likes 0 ·
chitrarekhasaha avatar image
chitrarekhasaha answered

This database also has simple recovery mode.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chitrarekhasaha avatar image
chitrarekhasaha answered

Hi KenJ, Thank you for the response and discussions and yes I26130\advent did not have the privilege to run the backup job. Security node did not have the user defined and hence the issue. Backup job is successful

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.