Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Read a backup file to find the version Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 6:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 7, 2016 8:32 AM
Points: 329, Visits: 252
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.



Post #1427940
Posted Thursday, March 7, 2013 7:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 14,412, Visits: 37,726
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!
Post #1427954
Posted Thursday, March 7, 2013 9:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 7, 2016 8:32 AM
Points: 329, Visits: 252
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?


Post #1428069
Posted Thursday, March 7, 2013 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 14,412, Visits: 37,726
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!
Post #1428074
Posted Thursday, March 7, 2013 10:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 7, 2016 8:32 AM
Points: 329, Visits: 252
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.



Post #1428100
Posted Thursday, March 7, 2013 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 14,412, Visits: 37,726
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!
Post #1428140
Posted Thursday, March 7, 2013 1:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 7, 2016 8:32 AM
Points: 329, Visits: 252
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.


Post #1428249
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse