Backup file size is 0 KB

  • 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

  • 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

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

    Hope this helps,
    Rich

    [p]
    [/p]

  • 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

  • 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

  • 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

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

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