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


Read a backup file to find the version


Read a backup file to find the version

Author
Message
buddy__a
buddy__a
Say Hey Kid
Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)

Group: General Forum Members
Points: 688 Visits: 257
Lowell, I'd like to find out more about why it failed on your backups since I tested it on several versions and different database names. Can you give me the backup command that you used to create the backups or if it's possible can you give me some sample backup files that it failed on?

I'd also like to get some others to try this to see if it works for them.



Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67365 Visits: 40890
easy peasy!

The error could be on my side, or maybe something is different on my backups that you should double check against.
!edit!
something i was thinking: my 2008/2008R2/2012 backups are on a disk that is formatted with 4k sectors instead of the "normal" 512 sectors!
that might be the difference!

for my 2008R2 for example, i created a brand new database CodeTest, and did a normal backup via the GUI:
the scripted command is:

BACKUP DATABASE [CodeTest] TO DISK = N'F:\SQLData\SQL2008R2\Backup\CodeTest.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'CodeTest-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO



i did exactly that code in all 4 version i have available to me.

here is a link to a zip file with all four empty databases, each one created on one of my servers or local instances:
so you can test it on your side:


CodeTest2005.bak
CodeTest2008.bak
CodeTest2008R2.bak
CodeTest2012.bak


Four SQL Backups CodeTestDBs.zip 729kb zip/6 meg uncompressed

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
buddy__a
buddy__a
Say Hey Kid
Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)

Group: General Forum Members
Points: 688 Visits: 257
Well Lowell, sure enough your backups fail (i.e. return zero for version number), although backing up my database using your command works fine. I don't have a 4K sector disk to test on right now so I can't verify that that is the issue. It would seem odd if that were the case as it would make the file usability dependent on the disk. Do you have a "normal" 512 byte disk that you could backup on?



Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67365 Visits: 40890
sure: here's the same databases as a new backup, but placed on a disk with sector sizes of 512:

hope this helps!

CodeTestDBs-512.zip

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
buddy__a
buddy__a
Say Hey Kid
Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)

Group: General Forum Members
Points: 688 Visits: 257
The sector size must be the difference, the new files all report the correct version. I looked through the 4k files and found that the offset is at 10412 (so you could modify the code I posted to set the Seek offset to 10412 and it will work on the 4k files). I wonder if there is a way to determine if a file was created on a 4k sector disk?

Thanks for your time in helping me find out more about this. I really appreciate it.



Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67365 Visits: 40890
interesting that it's different for the 4k sectors;
in your code, i guess you'll check the size,and if it is zero, try the 4k sector size method instead?

that'd work i think, but it depends on your process, of course.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
buddy__a
buddy__a
Say Hey Kid
Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)

Group: General Forum Members
Points: 688 Visits: 257
Yeah I don't understand it either but I've found that there is an identifier about 160 bytes before the data I want so I will read for it at the location for 512 byte files and if not there assume that it's a 4k file and read the appropriate data location.



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