Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
Directory lookup for the file "D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
SQL Backup process ended.
(10 row(s) affected)
name value
exitcode 0
sqlerrorcode 0
filename001 E:\Backup_A\FULL_(local)_master_20071203_234212.sqb
Could you pls download this utility, select the 'Check file integrity' function, and run the check on the file that you are having problems with (FULL_(local)_master_20071203_234212.sqb'')? Does it report any problems?
Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software Beyond compression - SQL Backup goodies under the hood, updated for version 8
12/5/2007 11:32:22 AM Not encrypted
12/5/2007 11:32:22 AM Single device file
12/5/2007 11:32:22 AM Compression level: 1
12/5/2007 11:32:22 AM File size: 664,576
12/5/2007 11:32:23 AM File read stopped at position: 664,233
12/5/2007 11:32:23 AM Remaining: 343
12/5/2007 11:32:23 AM Validating remaining bytes...
12/5/2007 11:32:23 AM File appears to be valid
12/5/2007 11:32:23 AM File read ended at position: 664,576
Did you try HEADERONLY on your computers? Could you successfully run it?
SQL Backup v5.2.0.2825
Reading file header of "e:\temp\headerproblem.sqb"
Backup name : Database (model), 12/3/2007 11:42:17 PM
Description : Backup on 12/3/2007 11:42:17 PM Server: ABSRAIDT Database: model
Backup type : 1 (Database)
User name : ABSOLUTE\tsun
Backup start : 12/3/2007 11:42:18 PM
Backup finish: 12/3/2007 11:42:18 PM
Server name : ABSRAIDT
Database name : model
Database version : 611
Creation date : 4/8/2003 9:13:36 AM
Size : 2.313 MB
Sort order : 52
Code page : 0
Machine name : ABSRAIDT
Collation : SQL_Latin1_General_CP1_CI_AS
First LSN : 22000000040000037
Last LSN : 22000000042400001
Checkpoint LSN : 22000000040000037
Database backup LSN : 22000000033600037
Differential base LSN : NULL
SQL Backup process ended.
(28 row(s) affected)
name value
exitcode 0
sqlerrorcode 0
filename001 e:\temp\headerproblem.sqb
(3 row(s) affected)
Is this not what you get, or were you expecting something different? Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software Beyond compression - SQL Backup goodies under the hood, updated for version 8
Yes, exactly, but it's entirely different from the sqlserver's result format.
If you use FILELISTONLY, you can get the same result as sqlserver's.
Actually, I'm using "Insert into @TempTable Exec(@Sql)", but if the result returns two tables with different column amount, I could not get the info. So far, I cannot figure out how to get these info in my stored procedure.
Why you design it like this? Or give me some suggestions.
Because it is easier to read. Also, I am not aware of any user requesting for the output to be consistent with that returned by SQL Server (until now, that is).
Or give me some suggestions.
Use the SINGLRESULTSET option to output only the first result set. E.g.
CREATE TABLE #temp1 (col1 nvarchar(256))
INSERT INTO #temp1 EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [e:\temp\pubs.sqb] WITH SINGLERESULTSET"'
SELECT * FROM #temp1
DROP TABLE #temp1
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software Beyond compression - SQL Backup goodies under the hood, updated for version 8
That's exactly what I want, "WITH SINGLERESULTSET". Thank you a lot for your quick reponses.
BTW, probably, I'm the first one who want to use this funcationality in programming.
It's easy for human beings to read but hard for machines, even I use "WITH SINGLERESULTSET", it still need some further process to deal with the info. Anyway, it will be great, if you could add a parameter to let people specify the result format. That's only my personal suggestion.
Comments
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
I don't think it's a file-related issue because I have tried a couple of files.
RESTORE FILELISTONLY works, but RESTORE VERIFYONLY and RESTORE SQBHEADERONLY don't work as well as HEADERONLY.
BTW, currently, I'm using 5.2 version for SQL 2005.
Here are some results:
--===============================================================--
declare @sql varchar(max)
set @sql = '-SQL "Restore verifyonly from disk = ''E:\Backup_A\FULL_(local)_master_20071203_234212.sqb''"'
exec master.dbo.sqlbackup @Sql
SQL Backup v5.2.0.2807
Verifying file:
E:\Backup_A\FULL_(local)_master_20071203_234212.sqb
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
Directory lookup for the file "D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
SQL Backup process ended.
(10 row(s) affected)
name value
exitcode 0
sqlerrorcode 0
filename001 E:\Backup_A\FULL_(local)_master_20071203_234212.sqb
(3 row(s) affected)
--===============================================================--
--===============================================================--
declare @sql varchar(max)
set @sql = '-SQL "Restore headeronly from disk = ''E:\Backup_A\FULL_(local)_master_20071203_234212.sqb''"'
exec master.dbo.sqlbackup @Sql
SQL Backup v5.2.0.2807
Reading file header of "E:\Backup_A\FULL_(local)_master_20071203_234212.sqb"
Backup name : Database (master), 12/3/2007 11:42:12 PM
Description : Backup on 12/3/2007 11:42:12 PM Server: CTESDTSS Database: master
Backup type : 1 (Database)
User name : CTESDTSS\osun
Backup start : 12/3/2007 11:42:13 PM
Backup finish: 12/3/2007 11:42:13 PM
Server name : CTESDTSS
Database name : master
Database version : 611
Creation date : 11/14/2007 6:24:11 PM
Size : 3.313 MB
Sort order : 52
Code page : 0
Machine name : CTESDTSS
Collation : SQL_Latin1_General_CP1_CI_AS
First LSN : 432000000003200037
Last LSN : 432000000005600001
Checkpoint LSN : 432000000003200037
Database backup LSN : 431000000019200037
Differential base LSN : NULL
SQL Backup process ended.
(28 row(s) affected)
name value
exitcode 0
sqlerrorcode 0
filename001 E:\Backup_A\FULL_(local)_master_20071203_234212.sqb
(3 row(s) affected)
--===============================================================--
--===============================================================--
declare @sql varchar(max)
set @sql = '-SQL "Restore sqbheaderonly from disk = ''E:\Backup_A\FULL_(local)_master_20071203_234212.sqb''"'
exec master.dbo.sqlbackup @Sql
SQL Backup v5.2.0.2807
Reading SQB file header of "E:\Backup_A\FULL_(local)_master_20071203_234212.sqb"
Backup group ID : 100457965
File number : 1 of 1
Backup type : 1 (Database)
Native backup size : 3.688 MB
Database size : 5.000 MB
Backup start : Monday, December 03, 2007 11:42:13 PM
Backup end : Monday, December 03, 2007 11:42:13 PM
Server name : CTESDTSS
Database name : master
First LSN : 432000000003200037
Last LSN : 432000000005600001
Checkpoint LSN : 432000000003200037
Database backup LSN : 431000000019200037
(20 row(s) affected)
name value
exitcode 0
sqlerrorcode 0
filename001 E:\Backup_A\FULL_(local)_master_20071203_234212.sqb
(3 row(s) affected)
--===============================================================--
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Here is the resutl:
12/5/2007 11:32:22 AM Not encrypted
12/5/2007 11:32:22 AM Single device file
12/5/2007 11:32:22 AM Compression level: 1
12/5/2007 11:32:22 AM File size: 664,576
12/5/2007 11:32:23 AM File read stopped at position: 664,233
12/5/2007 11:32:23 AM Remaining: 343
12/5/2007 11:32:23 AM Validating remaining bytes...
12/5/2007 11:32:23 AM File appears to be valid
12/5/2007 11:32:23 AM File read ended at position: 664,576
Did you try HEADERONLY on your computers? Could you successfully run it?
Thanks,
You may download this file and try it.
http://pickup.mofile.com/6330744401386462
declare @sql varchar(max)
set @sql = '-SQL "Restore headeronly from disk = ''E:\Backup_A\FULL_(local)_model_20071203_234217.sqb''"'
exec master.dbo.sqlbackup @Sql
[/url]
SQL Backup v5.2.0.2825
Reading file header of "e:\temp\headerproblem.sqb"
Backup name : Database (model), 12/3/2007 11:42:17 PM
Description : Backup on 12/3/2007 11:42:17 PM Server: ABSRAIDT Database: model
Backup type : 1 (Database)
User name : ABSOLUTE\tsun
Backup start : 12/3/2007 11:42:18 PM
Backup finish: 12/3/2007 11:42:18 PM
Server name : ABSRAIDT
Database name : model
Database version : 611
Creation date : 4/8/2003 9:13:36 AM
Size : 2.313 MB
Sort order : 52
Code page : 0
Machine name : ABSRAIDT
Collation : SQL_Latin1_General_CP1_CI_AS
First LSN : 22000000040000037
Last LSN : 22000000042400001
Checkpoint LSN : 22000000040000037
Database backup LSN : 22000000033600037
Differential base LSN : NULL
SQL Backup process ended.
(28 row(s) affected)
name value
exitcode 0
sqlerrorcode 0
filename001 e:\temp\headerproblem.sqb
(3 row(s) affected)
Is this not what you get, or were you expecting something different? Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
If you use FILELISTONLY, you can get the same result as sqlserver's.
Actually, I'm using "Insert into @TempTable Exec(@Sql)", but if the result returns two tables with different column amount, I could not get the info. So far, I cannot figure out how to get these info in my stored procedure.
Why you design it like this? Or give me some suggestions.
Thanks,
Use the SINGLRESULTSET option to output only the first result set. E.g.
CREATE TABLE #temp1 (col1 nvarchar(256))
INSERT INTO #temp1 EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [e:\temp\pubs.sqb] WITH SINGLERESULTSET"'
SELECT * FROM #temp1
DROP TABLE #temp1
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
That's exactly what I want, "WITH SINGLERESULTSET". Thank you a lot for your quick reponses.
BTW, probably, I'm the first one who want to use this funcationality in programming.
It's easy for human beings to read but hard for machines, even I use "WITH SINGLERESULTSET", it still need some further process to deal with the info. Anyway, it will be great, if you could add a parameter to let people specify the result format. That's only my personal suggestion.
Thanks again.
Neo