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 ««12345»»»

Automatically Restores All database ".BAK" Files Expand / Collapse
Author
Message
Posted Sunday, May 11, 2008 10:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
Posted the updated script which accept backup files with multiple ndfs im just waiting for the site confirmation for this. After running the script please tell me if there were bugs so that i can correct and adjust the error/s. Enjoy :D

"-=Still Learning=-"

Lester Policarpio

Post #498544
Posted Monday, May 12, 2008 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 6, 2010 1:23 AM
Points: 30, Visits: 163
Hello!
Is it possible to have the new script, which takes ndfs and perform some testing on it before the site confirmation?

Thanks
gtossa
Post #498913
Posted Monday, May 12, 2008 7:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
The updated script is already up and running :D

"-=Still Learning=-"

Lester Policarpio

Post #499243
Posted Monday, May 19, 2008 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:21 PM
Points: 2, Visits: 159
My Database files have the correct format and I get the error while trying to use your script?

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


Is it possible to use sp_MSforeachdb?
Post #502832
Posted Thursday, May 22, 2008 7:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
PM'ed you but you didn't asnwer ;). what do you want to attain by using sp_msforeachdb?? I also did some error checking and found out that in other cases, using RESTORE HEADERONLY in the backup file may produce 1 or more results that can lead to a script error anyways i'll update the script to prevent this error. About your problem maybe if you can give me a little bit of information more i can detect the problem you are facing :)

"-=Still Learning=-"

Lester Policarpio

Post #505587
Posted Wednesday, June 4, 2008 1:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 6, 2010 1:23 AM
Points: 30, Visits: 163
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.
Post #511714
Posted Thursday, June 5, 2008 6:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
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

Post #512616
Posted Friday, June 6, 2008 3:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 10, 2008 1:29 AM
Points: 1, Visits: 3
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
Post #512764
Posted Friday, June 6, 2008 6:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 18, 2013 7:50 PM
Points: 2, Visits: 113
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
)


Post #513286
Posted Monday, June 9, 2008 7:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:06 PM
Points: 398, Visits: 2,428
@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

Post #514118
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse