SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automatically Restores All database ".BAK" Files


Automatically Restores All database ".BAK" Files

Author
Message
Lester Policarpio
Lester Policarpio
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2459
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 BigGrin

"-=Still Learning=-"

Lester Policarpio
gtossa
gtossa
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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
Lester Policarpio
Lester Policarpio
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2459
The updated script is already up and running BigGrin

"-=Still Learning=-"

Lester Policarpio
Gabriel -142394
Gabriel -142394
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 165
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?
Lester Policarpio
Lester Policarpio
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2459
PM'ed you but you didn't asnwer Wink. 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 Smile

"-=Still Learning=-"

Lester Policarpio
gtossa
gtossa
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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.
Lester Policarpio
Lester Policarpio
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2459
Thanks for the compliment and much more thanks for the positive criticism its that type of criticism that makes me going Smile

"-=Still Learning=-"

Lester Policarpio
mike.winter
mike.winter
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Kenneth Pau
Kenneth Pau
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 115
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
)



Lester Policarpio
Lester Policarpio
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2459
@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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search