Backup file size is 0 KB

Question

Backup file size is 0 KB

Moenier Davids

SSC Eights!

Points: 885
More actions
July 30, 2010 at 1:13 am

#238294

Hi there
I've backed up a database and called the backup file SQLDBName-MD-20100729-15:300.bak. SQL Server Management Studio says the backup took place successfully. I can also find the backup location or directory in column msdb.dbo.backupmediafamily.physical_device_name, however when I browse to the location in windows explorer it displays the file name as [SQLDBName-MD-20100729-15] and the file size is 0 KB. I'm of the oponion it doesn't like the : character.
Has anyone have any ideas why the back file is 0 kb and Is there any way possible for me to recover this backup?
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Yes you are right. The ":" in your backup file is causing this issue. Without the : the backup should be fine. The ":" symbol is used only at the beginning of any path to identify the Drive name. If used anywhere else, that path becomes invalid.

Try creating the same file in Windows Explorer, you will not be allowed to.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 2

That's really interesting, have you tried doing a restore with verifyonly?

Hope this helps,
Rich

[p]
[/p]

Moenier Davids SSC Eights! Points: 885 More actions · Answer 3

I have tried however I'm getting a message saying The volumn on device e:\sql backups\sqlserver\dbname-md-20100729 is empty. RESTORE HEADERONLY is terminating abnormally....error 3254.

I doubt this backup is recoverable

Adiga One Orange Chip Points: 27284 More actions · Answer 4

SQL Server has just dumped the backup information to the file and the operating system did not allow the file to be processed. This backup file is irrecoverable

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

prashantjain07 SSC Journeyman Points: 85 More actions · Answer 5

Hi

This post is too old but just to add information so it can be helpful for others.

I faced same issue and by just trying i got the solution of it. (with some limitations).

your 0 bite file must be present at the same location with same name.

if you want to restore on different, 1st you need to check access form your 1st database server (source server) to 2nd database server (where u want to RESTORE database).

Check you database backup details using following script on 1st server ..

SELECT TOP 100

s.database_name,

m.physical_device_name,

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,

s.backup_start_date,

CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,

CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,

CASE s.[type]

WHEN 'D' THEN 'Full'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Transaction Log'

END AS BackupType,

s.server_name,

s.recovery_model

FROM msdb.dbo.backupset s

INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id

WHERE s.database_name = DB_NAME() -- Remove this line for all the database

ORDER BY backup_start_date DESC, backup_finish_date

GO

this will give you LNS details with backup time , size , LAN, backup file name and backup type.

now check the sequence of that backup set with

restore headeronly from disk =N'd:\test\test_trn_12:30.trn'

from the position column , take the position no of your backup set and use the same No for FILE = option in below query and run it on 2nd server.

RESTORE LOG [testing] FROM DISK = N'\\remote-server-name\D$\test\test_trn:20.trn' WITH FILE = 1, NORECOVERY/RECOVER

like the same way you can restore full and differential backup backup ....

RESTORE DATABASE Testing from DISK =N'\\remote-server-name\D$\test\test_full.bak' WITH FILE = 1 , NORECOVERY/RECOVER