Clear error in for each loop container

Hi,

I have a "Data Flow Task" inside" For Each container". Data flow task is processing file and updating the DB.

If one of the file is correpted i want to move to error folder and continue with the next file. i have given red arrow to a script Task which move the file to error folder. but its not continuing with next file. how can Ido that?

Any help

Set the Max Error count to 0 for the container. 0 = unlimited.|||thanks crispin.. its works.

Cleanup Task / xp_delete_file

Hello together,
I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
english version but german regional settings
The following code does nothing in the file system, but allways states
success:
EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:2
9'
nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
include subfolders.
Any ideas ?
MichaelOne problem is that xp_delete_file belongs to the sys schema
not the dbo schema.
-Sue
On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:

>Hello together,
>I found some posts but not really a solution. Environment: SQL 2005 EE, SP1
,
>english version but german regional settings
>The following code does nothing in the file system, but allways states
>success:
>EXECUTE master.dbo.xp_delete_file 0,N'D:\test',N'.bak',N'06/03/2006 15:06:
29'
>nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>include subfolders.
>Any ideas ?
>Michael
>|||Dear Sue,
even when I call the sp with the sys schema it happes nothing in the
filesystem.
Any other idea?
Regards
Michael
"Sue Hoegemeier" wrote:

> One problem is that xp_delete_file belongs to the sys schema
> not the dbo schema.
> -Sue
> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
> <MichaelRoedeske@.discussions.microsoft.com> wrote:
>
>|||Others have had the same issue - and others do not. I
haven't seen anyone post what solved the issue.
Try removing the dot from the extension - for example have
it list as just BAK without the dot before BAK. Make sure
the extension is exactly what you are using for your
backups.
It was suppose to be fixed in SP1 but you may also want to
try creating a cleanup task for each subdirectory.
People sometimes spend too much time troubleshooting
maintenance plans when they can just write scripts that have
more flexibility in less time then they spend addressing
maintenance plan issue.
-Sue
On Wed, 7 Jun 2006 01:06:02 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Dear Sue,
>even when I call the sp with the sys schema it happes nothing in the
>filesystem.
>Any other idea?
>Regards
>Michael
>"Sue Hoegemeier" wrote:
>sqlsql

Cleanup Task / xp_delete_file

Hello together,
I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
english version but german regional settings
The following code does nothing in the file system, but allways states
success:
EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
include subfolders.
Any ideas ?
MichaelOne problem is that xp_delete_file belongs to the sys schema
not the dbo schema.
-Sue
On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Hello together,
>I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
>english version but german regional settings
>The following code does nothing in the file system, but allways states
>success:
>EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
>nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>include subfolders.
>Any ideas ?
>Michael
>|||Dear Sue,
even when I call the sp with the sys schema it happes nothing in the
filesystem.
Any other idea?
Regards
Michael
"Sue Hoegemeier" wrote:
> One problem is that xp_delete_file belongs to the sys schema
> not the dbo schema.
> -Sue
> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
> <MichaelRoedeske@.discussions.microsoft.com> wrote:
> >Hello together,
> >
> >I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
> >english version but german regional settings
> >
> >The following code does nothing in the file system, but allways states
> >success:
> >
> >EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
> >
> >nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
> >include subfolders.
> >
> >Any ideas ?
> >
> >Michael
> >
>|||Others have had the same issue - and others do not. I
haven't seen anyone post what solved the issue.
Try removing the dot from the extension - for example have
it list as just BAK without the dot before BAK. Make sure
the extension is exactly what you are using for your
backups.
It was suppose to be fixed in SP1 but you may also want to
try creating a cleanup task for each subdirectory.
People sometimes spend too much time troubleshooting
maintenance plans when they can just write scripts that have
more flexibility in less time then they spend addressing
maintenance plan issue.
-Sue
On Wed, 7 Jun 2006 01:06:02 -0700, Michael Roedeske
<MichaelRoedeske@.discussions.microsoft.com> wrote:
>Dear Sue,
>even when I call the sp with the sys schema it happes nothing in the
>filesystem.
>Any other idea?
>Regards
>Michael
>"Sue Hoegemeier" wrote:
>> One problem is that xp_delete_file belongs to the sys schema
>> not the dbo schema.
>> -Sue
>> On Tue, 6 Jun 2006 06:17:01 -0700, Michael Roedeske
>> <MichaelRoedeske@.discussions.microsoft.com> wrote:
>> >Hello together,
>> >
>> >I found some posts but not really a solution. Environment: SQL 2005 EE, SP1,
>> >english version but german regional settings
>> >
>> >The following code does nothing in the file system, but allways states
>> >success:
>> >
>> >EXECUTE master.dbo.xp_delete_file 0,N'D:\test\',N'.bak',N'06/03/2006 15:06:29'
>> >
>> >nor does it work with 'bak" ,'trn, '*.trn' or '*.*' or the seeting to
>> >include subfolders.
>> >
>> >Any ideas ?
>> >
>> >Michael
>> >
>>

Child Package Fails when called from parent

So I have a parent package that calls another package using the Execute Package Task. When I run the child it runs fine but when I run it from the parent i get this msg...Any ideas?

Error: 0xC00220E4 at Execute VR Account Load: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

Pls take a look at this post to see whether the investigations and solutions there helps. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=241941&SiteID=1

thanks

wenyang

|||

Wenyang,

Thanks for the reply but the solution did not work. I might have found a bug here because the child package uses package configurations and even though I have disabled the package config on the child when I execute it from the parent the output window says that it is trying to load the package configurations. Not sure if this has anything to do with it.

Information: 0x40016040 at VR Load Account: The package is attempting to configure from SQL Server using the configuration string ""localhost.CALLMIS";"[dbo].[SSIS Configurations]";"MISLoads_ServerName";".

Thx

|||

Disregard last msg I posted. It appears the problem was that there was a bad connection guid or something like that still hanging around in the child? I recreated the package and it appears to be working now...

Not my idea of fun...

Checkpoints: FTP task download FTP files fails in between then what will happen?

Hi,

I have a FTP task in my control flow that download files from a FTP server. This ftp task is inside a foreach container that loops over a ADO recordset for the file name. The files that the ftp task pulls are huge. If the FTP task fails then I want the FTP task to restart and only download those files that have not been downloaded. Is this possible?

What possible configurations do I have to make to the foreach container and the filetask?

Thanks a lot in advance for your help and time.

Regards,

$wapnil

Experts!!!! any inputs on this.

Thanks in advance for your time.

Regards,

$wapnil

|||

anyone.....help!

Thanks,

$wapnil

Checking for file existance in DTS

I have a DTS package that extracts information and puts it in an ASCII file
for upload to our bank. The upload is done by a scheduled task. Since I
don't always know if this task is successful, (the file ascii file will be
deleted if it is), I need to check for the existance of this file at the
beginning of the DTS package to stop it from running if the file already
exists.
This is on SQL2000
My questions are...
1) Is there a way to check for the existance of an ascii file on the server
from inside a DTS package, and then stop the package if it exists.
2) (alternately) Is there a way to set a DTS data transformation that
copies the data from a table into the ascii file so that it will append
rather than overwritting the ascii file.
Sorry if this is a duplicate, I think I lost a previous version of this
before it was posted.
Charlietake a look at the code below I have it in an internal package that I use in
a lot of packages so that I don't have duplicate code
FolderPath = (DTSGlobalVariables("gvFolderPath").value)
FilePath = DTSGlobalVariables("gvFilePath").value
Dim FSO
Function Main()
Set FSO = CreateObject("Scripting.FileSystemObject")
MoveFile(FSO)
Main = DTSTaskExecResult_Success
End Function
Function MoveFile(FSO)
MoveFrom = FolderPath & FilePath
If FSO.FileExists(MoveFrom) Then
DTSGlobalVariables("gvPackageError").value =0
else
DTSGlobalVariables("gvPackageError").value = 1
End If
End Function
"Charlie Chisholm" <charlie.chisholm@.goodwill-suncoast.com> wrote in message
news:bJe3e.38592$Fz.5460@.tornado.tampabay.rr.com...
>I have a DTS package that extracts information and puts it in an ASCII file
>for upload to our bank. The upload is done by a scheduled task. Since I
>don't always know if this task is successful, (the file ascii file will be
>deleted if it is), I need to check for the existance of this file at the
>beginning of the DTS package to stop it from running if the file already
>exists.
> This is on SQL2000
> My questions are...
> 1) Is there a way to check for the existance of an ascii file on the
> server from inside a DTS package, and then stop the package if it exists.
> 2) (alternately) Is there a way to set a DTS data transformation that
> copies the data from a table into the ascii file so that it will append
> rather than overwritting the ascii file.
> Sorry if this is a duplicate, I think I lost a previous version of this
> before it was posted.
> Charlie
>

Checking conditional in SSIS

I am converting a DTS package to SSIS.

DTS has the following steps:

1. SQL task that returns a variable ( a count ).

2. ActiveX script:

Function Main()

If DTSGlobalVariables("gsPrevProcessCount").Value > 0 Then
MsgBox "The data for one or more of your members has already been processed. Please review your data files and remove the processed files from the data directory."
Main = DTSTaskExecResult_Failure
Else
Main = DTSTaskExecResult_Success
End If

End Function

In SSIS, I have an Execute SQL Task that returns a variable PrevProcessedCount. Now, I am stuck. How do I display a message to the user (or maybe just put it in the audit log) and how to I check the variable and stop processing my package?

Linda

See if this helps any:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1699966&SiteID=1|||Spiffy! Thanks!

Checking a Column Data Type

Here is the issue,

I was given the task of creating a datamart by combining the information from several different database servers. While doing this I ran into an interesting issue that I can't seem to figure out.

I have two tables, one table has a code value in it pointing to the corresponding lookup table. The lookup table for whatever reason (I didn't build the application or the database, yay legacy support! ) has two lookup columns, one is the standard incrementing numbers and the other is a series of letters. The problem lies in the fact that the code in the first table is a char(1) and can either be a letter or a number. The look up has type int and char so I run into a simple conversion problem. Here is my datamart select statement that fails because it can not convert a char to an int. My question is what would be an easy way to test which value type the char(1) is and use the appropriate left outer join.. I tried several different approaches but just can't seem to figure it out.

SELECT *

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU

ON

(

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM) OR

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd)

)

the above of course fails due to type conversion. status_cd is the char(1), status_nm is the char and pslu_cd is the int. I have no clue why they set it up this way, but I get to figure out how to deal with it.

Confused,

-Andrew

Try to use ISNUMERIC:

declare @.c char(1)

set @.c='5'

select isnumeric(@.c)

Result: 1

declare @.c2 char(1)

set @.c2='a'

select isnumeric(@.c2)

Result: 0

|||

yeah I thought about that but then the question is, how do you use IF ELSE Logic inside a JOIN statement

Can you even do something like this? ( I just tried it and got parse errors) So I guess the question is now how do you put conditional logic inside a JOIN ON ?

LEFT OUTER JOIN PERMIT_STATUS_LU

ON

(

IF ( isnumeric(PERMIT.endorse_status_cd) = 1)

BEGIN

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM)

END

ELSE
BEGIN

(PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd)

END

)

|||

Use CASE instead.

Also, isnumeric, under some situations, can be unreliable. Refer to this article for details.

isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390

|||

Good idea using the CASE. I am still getting errors though, I am still unsure if its even possible to put a CASE statement inside the JOIN on clause. For example I tried

....

FROM PERMIT

CASE isnumeric(PERMIT.endorse_status_cd)

WHEN 0 THEN LEFT OUTER JOIN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM

WHEN 1 THEN LEFT OUTER JOIN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd

END

That didnt work so I tried

....

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU ON

(

CASE isnumeric(PERMIT.endorse_status_cd)

WHEN 0 THEN PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM

WHEN 1 THEN PERMIT_STATUS_LU ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd

END

)

that failed as well

Does anyone have a definative answer to the above question?

|||

I created 2 separate queries: One that contains only the rows that have a numeric value for endorse_status_cd and another for the non numeric values. I used a union to merge them into one singe result set. Here is the query:

Code Snippet

SELECT *

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU

ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.PSLU_PERMIT_STATUS_NM

WHERE IsNumeric(PERMIT.endorse_status_cd) = 0

UNION

SELECT *

FROM PERMIT

LEFT OUTER JOIN PERMIT_STATUS_LU

ON PERMIT.endorse_status_cd = PERMIT_STATUS_LU.pslu_cd

WHERE IsNumeric(PERMIT.endorse_status_cd) = 1

Based on the article that Arnie included in his reply, it might be better to create your own function (check the article) instead of IsNumeric.

I hope this answers your question.

Best regards,

Sami Samir

check time stamp on a *.bak file against the DB I have in SQL server and restore

I have a *.bak file generated by other guys using their own SQL server.
My task is to restore the *.bak to my SQL server if what i have in my
SQL server is an older version that the *.bak file.
ThanksHi
Look at
RESTORE HEADERONLY
FROM <backup_device>
in BOL. Will tell you when the DB was backed up.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Try for instance RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||RESTORE HEADERONLY gives you the backup time, BUT... the backup time doesn't
necisarrily mean your database is older. The data in your current database
could be newer than the backup.
It depends what your trying to do here? Restore the backup simply because
it's a later backup or restore the DB if the data is newer?
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Doesn't Resore Headeronly...affect my database?
Let me rephrase my question.
I have a SQL server running in my machine. Another guy, Mr. X, does all
the data entry, table creation and all that on a separate server. The
bak file is constantly updated by X and he puts the latest in some
shared folder.
In my machine when I run a certain code, I check to see if a database I
have in my sql server (i.e which is restored from a *bak file from Mr.
X) is up to date with what X has put in his shared folder. If what I
have is old, I do a restore if not restore is skipped.
Thanks All|||> Doesn't Resore Headeronly...affect my database?
No. It doesn't do a restore, it only give you information about the backup. See the documentation in
Books Online.
One way you can do this is to read the backup history tables in your msdb database and see when you
last restored that database and compare that to the output from RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133826384.413091.91120@.o13g2000cwo.googlegroups.com...
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>|||xela wrote:
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>
Hi
What you try to do is not absolutely "fool proof" since you're trying to
compare a database to a backup. I think your best option, is to store
the backupcreate date from the .bak file everytime you restore it. Then
you can compare this date with the date from the "new" backup file and
restore it if it's newer. I think you'll have to do some coding your
self to achieve the function you want.
You should also keep in mind that the backup creation time/date doesn't
tell anything about the data in the file as such- it only tells you when
the backup was done.
Regards
Steen|||Hey guys, thanks for all the support.
I have more questions though.I was trying to follow Steens' steps.
how can select the db property "Date created" from the live database.
and also how do i select the "backupstartdate" from the *.file?
Thanks|||xela wrote:
> Hey guys, thanks for all the support.
> I have more questions though.I was trying to follow Steens' steps.
> how can select the db property "Date created" from the live database.
> and also how do i select the "backupstartdate" from the *.file?
> Thanks
>
The database create date you can get from sysdatabases (SELECT crdate
FROM master.dbo.sysdatabases WHERE name = 'YourDatabaseName') but that
might not always be what you want.
It would require that you drop the database before you restore the new
database to get this field updated with the new date. If you just
restore a new backup on top of the existing database, the create date
will stays the same.
The Backupstart and -stop date, you can get from running a RESTORE
HEADERONLY on your backup file as already mentioned in this thread.
Regards
Steen|||here is one bulky way i tried doing it...it does get the job done but
its ugly
set @.dbexist = 1
--create a temporary table for the bak file header result set
CREATE TABLE #mytemptable (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),DatabaseBackupLSN
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 bit,BeginsLogChain
bit,HasIncompleteMetaData bit,
IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
NULL,DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
NULL)
declare @.mycommand nvarchar(500),
@.guid_bak uniqueidentifier,
@.guid_db uniqueidentifier
set @.mycommand = 'Restore HEADERONLY FROM DISK =''$(TempPath)\AdventureWorks\AdventureWorksStaging.bak'' WITH NOUNLOAD'
insert #mytemptable exec(@.mycommand)
--get the GUIDs
set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
by BackupStartDate desc)
set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
where [name] like '%AdventureWorksStaging%' order by
database_creation_date desc)
IF (@.guid_bak <> @.guid_db)
begin
set @.versionIntact = -1
end
else
set @.versionIntact = 1
drop table #mytemptable|||xela wrote:
> here is one bulky way i tried doing it...it does get the job done but
> its ugly
> set @.dbexist = 1
> --create a temporary table for the bak file header result set
> CREATE TABLE #mytemptable (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),DatabaseBackupLSN
> 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 bit,BeginsLogChain
> bit,HasIncompleteMetaData bit,
> IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
> uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
> RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
> NULL,DifferentialBaseGUID uniqueidentifier,
> BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
> NULL)
> declare @.mycommand nvarchar(500),
> @.guid_bak uniqueidentifier,
> @.guid_db uniqueidentifier
> set @.mycommand = 'Restore HEADERONLY FROM DISK => ''$(TempPath)\AdventureWorks\AdventureWorksStaging.bak'' WITH NOUNLOAD'
> insert #mytemptable exec(@.mycommand)
> --get the GUIDs
> set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
> by BackupStartDate desc)
> set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
> where [name] like '%AdventureWorksStaging%' order by
> database_creation_date desc)
> IF (@.guid_bak <> @.guid_db)
> begin
> set @.versionIntact = -1
> end
> else
> set @.versionIntact = 1
> drop table #mytemptable
>
It's more or less the same way I'm doing and I don't know of any smarter
way to get the info from the RESTORE HEADERONLY command.
Regards
Steen

check time stamp on a *.bak file against the DB I have in SQL server and restore

I have a *.bak file generated by other guys using their own SQL server.
My task is to restore the *.bak to my SQL server if what i have in my
SQL server is an older version that the *.bak file.
ThanksTry for instance RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Hi
Look at
RESTORE HEADERONLY
FROM <backup_device>
in BOL. Will tell you when the DB was backed up.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||RESTORE HEADERONLY gives you the backup time, BUT... the backup time doesn't
necisarrily mean your database is older. The data in your current database
could be newer than the backup.
It depends what your trying to do here? Restore the backup simply because
it's a later backup or restore the DB if the data is newer?
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Doesn't Resore Headeronly...affect my database?
Let me rephrase my question.
I have a SQL server running in my machine. Another guy, Mr. X, does all
the data entry, table creation and all that on a separate server. The
bak file is constantly updated by X and he puts the latest in some
shared folder.
In my machine when I run a certain code, I check to see if a database I
have in my sql server (i.e which is restored from a *bak file from Mr.
X) is up to date with what X has put in his shared folder. If what I
have is old, I do a restore if not restore is skipped.
Thanks All|||> Doesn't Resore Headeronly...affect my database?
No. It doesn't do a restore, it only give you information about the backup.
See the documentation in
Books Online.
One way you can do this is to read the backup history tables in your msdb da
tabase and see when you
last restored that database and compare that to the output from RESTORE HEAD
ERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133826384.413091.91120@.o13g2000cwo.googlegroups.com...
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>|||Hey guys, thanks for all the support.
I have more questions though.I was trying to follow Steens' steps.
how can select the db property "Date created" from the live database.
and also how do i select the "backupstartdate" from the *.file?
Thanks|||here is one bulky way i tried doing it...it does get the job done but
its ugly
set @.dbexist = 1
--create a temporary table for the bak file header result set
CREATE TABLE #mytemptable (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),DatabaseBackupLSN
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 bit,BeginsLogChain
bit,HasIncompleteMetaData bit,
IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
NULL,DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
NULL)
declare @.mycommand nvarchar(500),
@.guid_bak uniqueidentifier,
@.guid_db uniqueidentifier
set @.mycommand = 'Restore HEADERONLY FROM DISK =
''$(TempPath)\AdventureWorks\AdventureWo
rksStaging.bak'' WITH NOUNLOAD'
insert #mytemptable exec(@.mycommand)
--get the GUIDs
set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
by BackupStartDate desc)
set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
where [name] like '%AdventureWorksStaging%' order by
database_creation_date desc)
IF (@.guid_bak <> @.guid_db)
begin
set @.versionIntact = -1
end
else
set @.versionIntact = 1
drop table #mytemptable

check time stamp on a *.bak file against the DB I have in SQL server and restore

I have a *.bak file generated by other guys using their own SQL server.
My task is to restore the *.bak to my SQL server if what i have in my
SQL server is an older version that the *.bak file.
Thanks
Try for instance RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegro ups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>
|||Hi
Look at
RESTORE HEADERONLY
FROM <backup_device>
in BOL. Will tell you when the DB was backed up.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegro ups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>
|||RESTORE HEADERONLY gives you the backup time, BUT... the backup time doesn't
necisarrily mean your database is older. The data in your current database
could be newer than the backup.
It depends what your trying to do here? Restore the backup simply because
it's a later backup or restore the DB if the data is newer?
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegro ups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>
|||Doesn't Resore Headeronly...affect my database?
Let me rephrase my question.
I have a SQL server running in my machine. Another guy, Mr. X, does all
the data entry, table creation and all that on a separate server. The
bak file is constantly updated by X and he puts the latest in some
shared folder.
In my machine when I run a certain code, I check to see if a database I
have in my sql server (i.e which is restored from a *bak file from Mr.
X) is up to date with what X has put in his shared folder. If what I
have is old, I do a restore if not restore is skipped.
Thanks All
|||> Doesn't Resore Headeronly...affect my database?
No. It doesn't do a restore, it only give you information about the backup. See the documentation in
Books Online.
One way you can do this is to read the backup history tables in your msdb database and see when you
last restored that database and compare that to the output from RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133826384.413091.91120@.o13g2000cwo.googlegro ups.com...
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>
|||Hey guys, thanks for all the support.
I have more questions though.I was trying to follow Steens' steps.
how can select the db property "Date created" from the live database.
and also how do i select the "backupstartdate" from the *.file?
Thanks
|||here is one bulky way i tried doing it...it does get the job done but
its ugly
set @.dbexist = 1
--create a temporary table for the bak file header result set
CREATE TABLE #mytemptable (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),DatabaseBackupLSN
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 bit,BeginsLogChain
bit,HasIncompleteMetaData bit,
IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
NULL,DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
NULL)
declare @.mycommand nvarchar(500),
@.guid_bak uniqueidentifier,
@.guid_db uniqueidentifier
set @.mycommand = 'Restore HEADERONLY FROM DISK =
''$(TempPath)\AdventureWorks\AdventureWorksStaging .bak'' WITH NOUNLOAD'
insert #mytemptable exec(@.mycommand)
--get the GUIDs
set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
by BackupStartDate desc)
set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
where [name] like '%AdventureWorksStaging%' order by
database_creation_date desc)
IF (@.guid_bak <> @.guid_db)
begin
set @.versionIntact = -1
end
else
set @.versionIntact = 1
drop table #mytemptable