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 ««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 10, 2014 4:50 PM
Points: 324, Visits: 217
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 @ 2:39 PM
Points: 12,903, Visits: 32,143
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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 10, 2014 4:50 PM
Points: 324, Visits: 217
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 @ 2:39 PM
Points: 12,903, Visits: 32,143
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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 10, 2014 4:50 PM
Points: 324, Visits: 217
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 @ 2:39 PM
Points: 12,903, Visits: 32,143
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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 10, 2014 4:50 PM
Points: 324, Visits: 217
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