Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup file size is 0 KB Expand / Collapse
Author
Message
Posted Friday, July 30, 2010 1:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 8, 2014 5:42 AM
Points: 37, Visits: 355
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
Post #961170
Posted Friday, July 30, 2010 5:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #961257
Posted Friday, July 30, 2010 7:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 27, 2014 5:48 AM
Points: 969, Visits: 693
That's really interesting, have you tried doing a restore with verifyonly?

Hope this helps,
Rich



Post #961338
Posted Friday, July 30, 2010 7:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 8, 2014 5:42 AM
Points: 37, Visits: 355
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
Post #961351
Posted Friday, July 30, 2010 10:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #961687
Posted Sunday, January 20, 2013 3:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 24, 2013 1:50 AM
Points: 13, Visits: 47
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



Post #1409264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse