How to backup database in external drive?

Question

Question by chitrarekhasaha · Nov 29, 2018 at 09:39 PM · backup

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.

0 Show 1

10 |1200 characters needed characters left characters exceeded

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

KenJ · Nov 29, 2018 at 10:22 PM 0

Share

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

Answer 1 · 2018-11-29T22:27:19Z

Answer by chitrarekhasaha · Nov 29, 2018 at 10:27 PM

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

0 · Share

10 |1200 characters needed characters left characters exceeded

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

Answer 2 · 2018-11-29T23:01:10Z

Answer by chitrarekhasaha · Nov 29, 2018 at 11:01 PM

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.

0 Show 1 · Share

10 |1200 characters needed characters left characters exceeded

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

KenJ · Nov 30, 2018 at 01:34 PM 0

Share

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?

Answer 3 · 2018-11-29T23:01:54Z

Answer by chitrarekhasaha · Nov 29, 2018 at 11:01 PM

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

0 · Share

10 |1200 characters needed characters left characters exceeded

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

Answer 4 · 2018-11-30T14:16:51Z

Answer by chitrarekhasaha · Nov 30, 2018 at 02:16 PM

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

0 Show 1 · Share

10 |1200 characters needed characters left characters exceeded

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

KenJ · Nov 30, 2018 at 04:58 PM 0

Share

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.

Answer 5 · 2018-11-30T14:23:13Z

Answer by chitrarekhasaha · Nov 30, 2018 at 02:23 PM

This database also has simple recovery mode.

0 · Share

10 |1200 characters needed characters left characters exceeded

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

Answer 6 · 2018-11-30T17:40:57Z

Answer by chitrarekhasaha · Nov 30, 2018 at 05:40 PM

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

1 · Share

10 |1200 characters needed characters left characters exceeded

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

How to backup database in external drive?

6 Replies

Your answer

Follow this Question

Related Questions