June 4, 2008 at 1:16 pm
Hi Lester,
I have tested the script. It works fine. Good job!
You may want though to adjust the data type of the BakcupSize column in the header_lester table.
You get conversion over flow error when you're dealing with big backup files.
June 5, 2008 at 6:32 pm
Thanks for the compliment and much more thanks for the positive criticism its that type of criticism that makes me going 🙂
"-=Still Learning=-"
Lester Policarpio
June 6, 2008 at 3:30 am
Hi lester im getting...
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Let me explain the scenario. We restore a Gas database given to us from a, lets say LESS than helpful, 3rd party company. This data is ours, the database is ours but its hosted on their servers (dont even get me started on what i would do if i were in charge of the litigation).
Anyway they send us a 'copy' of the gas database and we do a manual restore process twice per day. I was trying to use your script to automatically restore this database any ideas on why its failing?
Cheers
June 6, 2008 at 6:17 pm
mike.winter (6/6/2008)
Hi lester im getting...Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Let me explain the scenario. We restore a Gas database given to us from a, lets say LESS than helpful, 3rd party company. This data is ours, the database is ours but its hosted on their servers (dont even get me started on what i would do if i were in charge of the litigation).
Anyway they send us a 'copy' of the gas database and we do a manual restore process twice per day. I was trying to use your script to automatically restore this database any ideas on why its failing?
Cheers
The reason it is not working is the result set from REQUEST HEADERONLY / FILEONLY is different from the migration and the header table that is created.
i ran into this problem and I was able to fix it after using the exact column name and data type from those two queries.
Try replacing migration_lester and header_lester with this one:
CREATE TABLE migration_lester(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
type char(1),
FileGroupName nvarchar(128),
size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
)
CREATE TABLE header_lester (
BackupName nvarchar(128) ,
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLsn numeric(25,0),
LastLsn numeric(25,0),
CheckpointLsn numeric(25,0),
DifferentialBackupLsn numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleid int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingId uniqueidentifier,
RecoveryForkId uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged Int,
BeginsLogChain bit,
HAsIncompleteMetaData bit,
IsForceOFfline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBAseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier
)
June 9, 2008 at 7:50 pm
@mike.winter
Please try Kenneth's 2 new tables and can you give us a feedback on what happened to your restoration process
@kenneth Pau
Nice work!!! thanks for the contribution though it seems like not all backup files uses the table schema i used (for filelist and headeronly) as well as your 2 table schema. I used the schema u gave but encountered many insert errors. Anyways thanks it will really help the script to be improved i'll study the tables u gave and incorporate it to the restore script. My guess is that different backups resulted to different kinds of table schema (for filelist and headeronly)
"-=Still Learning=-"
Lester Policarpio
June 9, 2008 at 8:00 pm
mike.winter (6/6/2008)
Hi lester im getting...Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Let me explain the scenario. We restore a Gas database given to us from a, lets say LESS than helpful, 3rd party company. This data is ours, the database is ours but its hosted on their servers (dont even get me started on what i would do if i were in charge of the litigation).
Anyway they send us a 'copy' of the gas database and we do a manual restore process twice per day. I was trying to use your script to automatically restore this database any ideas on why its failing?
Cheers
just 1 question.... What version of MSSQL are you using? this script is tested to be running in sql 7 and 2000...
"-=Still Learning=-"
Lester Policarpio
June 10, 2008 at 3:50 pm
Lester Policarpio (6/9/2008)
mike.winter (6/6/2008)
Hi lester im getting...Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Let me explain the scenario. We restore a Gas database given to us from a, lets say LESS than helpful, 3rd party company. This data is ours, the database is ours but its hosted on their servers (dont even get me started on what i would do if i were in charge of the litigation).
Anyway they send us a 'copy' of the gas database and we do a manual restore process twice per day. I was trying to use your script to automatically restore this database any ideas on why its failing?
Cheers
just 1 question.... What version of MSSQL are you using? this script is tested to be running in sql 7 and 2000...
Hi Lester,
That should be the difference between us since I am using 2005. So I am able to verify your script is at least working on 1 2005 instance when using the updated headers and no other real changes.
2005 result set http://msdn.microsoft.com/en-us/library/ms178536.aspx
2000 result set http://msdn.microsoft.com/en-us/library/aa238455(SQL.80).aspx
If Mike is on 2005 also hopefully that can make it 2. Thanks for the script!
Regards,
Ken
June 12, 2008 at 6:59 pm
Thanks Kenneth I've never handled mssql 2005 before so i can't test the script if it will run in 2005. But as what you've said it works with a simple tweak in the headeronly and filelistonly. I think i can make changes to the script for it to be available in versions 7,2000 and 2005. Can you confirm if the values in the headeronly and filelistonly in 2000 are available in 2005? Thanks you very much.....
"-=Still Learning=-"
Lester Policarpio
June 22, 2008 at 4:53 pm
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\restore\istest_db_200806201557.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\restore\istest_db_200806201557.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
Hi there, I'm not sure what's going on but this script seems to be cutting off the first 3 letters off the .bak name.
(this happens for every database)
The script looks like it'll do the trick otherwise, do you know what I'm doing wrong?
June 23, 2008 at 7:25 pm
What version of sql do you have?? is it 2000 or 2005??
Can you please run the script below and send me the result. Just want to make sure that the backup name is being gathered completely
CREATE TABLE cmdshell_lester( fentry varchar(1000))
--Declare Variables
DECLARE @path varchar(1024),@restore varchar(1024)
DECLARE @restoredb varchar(2000),@extension varchar(1024)
DECLARE @pathension varchar(1024),@newpath varchar(1024),@header varchar(500)
--Set Values to the variables
SET @newpath = 'D:\' --new path wherein you will put the mdf/ldf
SET @path = 'D:\' --Path of the Backup File
SET @extension = 'BAK'
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension
--Insert the value of the command shell to the table
INSERT INTO cmdshell_lester exec master..xp_cmdshell @pathension
--Delete non backup files data, delete null values
DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%'
DELETE FROM cmdshell_lester WHERE FEntry is NULL
select substring(FEntry,40,len(FEntry)) as 'FEntry'from cmdshell_lester
DROP TABLE cmdshell_lester
"-=Still Learning=-"
Lester Policarpio
June 24, 2008 at 3:19 am
I'm using SQL 2000.
My variables are:
C:\Program Files\Microsoft SQL Server\MSSQL\Dataand
C:\restore
but I expect that's fairly typical.
Alas running that query the data is truncated.
Changing the (FEntry,40,len(FEntry) to (FEntry,37,len(FEntry) seemed to fix things, ah ha!
June 25, 2008 at 8:01 pm
Well I've tested the script in my database with almost as alike as your backup file name and the values in the old path and new path doesn't have any problems. It might be the result given by your xp_cmdshell less than 3 characters....
"-=Still Learning=-"
Lester Policarpio
July 20, 2008 at 3:01 pm
Hi,
if i use in the @path 'C:\Backup' where my BAK files are,the script looks for the BAK file in C:\BackupFileName.BAK why?
July 21, 2008 at 11:50 pm
avipenina (7/20/2008)
Hi,if i use in the @path 'C:\Backup' where my BAK files are,the script looks for the BAK file in C:\BackupFileName.BAK why?
Did you try 'C:\Backup\'?
"-=Still Learning=-"
Lester Policarpio
July 22, 2008 at 12:04 am
sry. i figure it out after i post this message.
Viewing 15 posts - 16 through 30 (of 79 total)
You must be logged in to reply to this topic. Login to reply