SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup file size is 0 KB


Backup file size is 0 KB

Author
Message
Moenier Davids
Moenier Davids
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 380
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
Adiga
Adiga
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6572 Visits: 21012
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
RichardDouglas
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 707
That's really interesting, have you tried doing a restore with verifyonly?

Hope this helps,
Rich



Moenier Davids
Moenier Davids
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 380
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
Adiga
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6572 Visits: 21012
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
prashantjain07
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search