SQL server backup problem

Question

I want to backup sql server database to Z drive.

I run backup through tasks--->backup

Backup destination is Z:\test.bak

It shows backup job completed successfully but no test.bak is on Z drive.

What's happened? How to fix this problem?

Answer 1

Are you looking on the Z drive on your SQL Server machine (not your client machine)?

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

Z drive is server local drive.

Answer 3

I am sure tibork  found ur mistake , but in case  you can check backup history to confirm that backup does happen

select b.database_name, b.type, bf.physical_device_name, b.backup_start_date, b.backup_finish_date
from msdb..backupset b
left outer join msdb..backupmediafamily bf on bf.media_set_id = b.media_set_id
where b.type in('D') and b.database_name = <database name in single quotes>'
order by b.backup_finish_date desc 

---

Thanks Saurabh Sinha

http://saurabhsinhainblogs.blogspot.in/

Please click the Mark as answer button and vote as helpful if this reply solves your problem

Answer 4

What if you execute the backup command from a query window, like:

BACKUP DATABASE x TO DISK = 'Z:\x.bak'

What do you see in the result window after executing above (replacing the obvious parts, of course)?

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 5

I run

BACKUP DATABASE x TO DISK = 'Z:\x.bak'

Still no X.bak on Z drive. Any help or thoughts?

in result windows

Processed 200 pages for database 'systems', file 'systems_1' on file 1.

Processed 24 pages for database 'systems', file 'systems_2' on file 1.

Processed 24 pages for database 'systems', file 'systems_3' on file 1.

Processed 24 pages for database 'systems', file 'systems_4' on file 1.

Processed 24 pages for database 'systems', file 'systems_5' on file 1.

Processed 24 pages for database 'systems', file 'systems_6' on file 1.

Processed 24 pages for database 'systems', file 'systems_7' on file 1.

Processed 24 pages for database 'systems', file 'systems_8' on file 1.

Processed 2 pages for database 'systems', file 'systems_log' on file 1.

BACKUP DATABASE successfully processed 370 pages in 0.180 seconds (16.040 MB/sec).

Answer 6

I run script and can find backup record.

Answer 7

Perhaps a permissions issue so that you do not have permissions to see the files produced by the SQL Server service account? 

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 8

out of curiosity, is Z a mapped network drive? could be its mapped for you (in which case it works to run the query that is run in your user context but isn't mapped for example for the account used for the sql agent

Answer 9

Z is local drive.

Other drive is all fine.

I can see .bak files on other drives.Only problem on Z drive.

Thanks

Answer 10

Z is local drive.

Other drive is all fine.

I can see .bak files on other drives.Only problem on Z drive.

Thanks

Can you add your accout  specifically on Z drive foldeer and give required priviliges

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 11

I have added my acct to Z drive: full permission.

Still same.

Here is log about this backup from log file viewer:

Message
Database backed up. Database: test, creation date(time): 2014/02/04(11:57:15), pages dumped: 395, first LSN: 20:8197:62, last LSN: 20:8222:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'Z:\test2.bak'}). This is an informational message only. No user action is required.

Any idea?

Answer 12

Since you use mapped drives, the mapping occurs at an account level - so the assumption here is that the service account used by the instance has a z mapping that is different from yours. Try using a UNC name instead - which is generally preferable since it avoids this type of confusion. 

Answer 13

How do you tell that Z drive here is a mapped drive here?

I don't see \\...\... from Z drive icon

Thanks

Answer 14

I just checked. Z drive is local drive.

Answer 15

Lets run couple of checks

1. Are you able to create any file on z: drive manually , may be text file and read it

2. run xp_cmdshell 'Dir z:\'  -->Are you able to see any files here, you can test again after creating manually.

3.  copy backup file from other drive and try to restore on sql server

This might give you some errors and that  will be the key to solution.

---

Thanks Saurabh Sinha

http://saurabhsinhainblogs.blogspot.in/

Please click the Mark as answer button and vote as helpful if this reply solves your problem

Answer 16

I can create/copy/delete on Z drive.

Z drive has 3 folders: A,B and C

When I run backup through Microsoft sql server management studio, 

right click database--->tasks-->Back up...

Go to Back up to Disk:

Click Add --->Browse File Name

It will list C,E,F....Z drive

If I click Z drive, it doesn't show any folders on Z drive

But if I click any other drive, it will show folders on that drive....

Any help or thoughts?

Thanks

Answer 17

In the file explorer do you see something like below?  If so, it is mapped.  In addition, where is the actual database server relative to the machine on which are running SSMS?  Is the instance on the same machine or a different one? 



Answer 18

Hi bestrongself,

I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If the issue still exists, I recommend you using other disk for backup.  

In theory, if you account have permission for drive disk and  run backup statement well . You should can check the .bak file on the related disk after back up. Personally, the issue regards your disk(Z:), you can choose other disk for backup database.

Thanks,
Sofiya Li

---

Sofiya Li
TechNet Community Support

Answer 19

Once the backup is complete, can you run below command?

restore headeronly from disk = 'Z:\test2.bak'

I can think of two possibilities.

1. Windows Explorer is not allowing you to view the file.
2. file is getting deleted as soon as backup is completed. May be antivirus?

Another possibility is that Z drive is mapped drive from SQL Server. so can you run below from SQL and check if you can see the backup file.

xp_cmdshell 'Dir Z:\'

---

Balmukund Lakhani
Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter | Facebook
Author: SQL Server 2012 AlwaysOn - Paperback, Kindle