Chinese character in the field (Sql 7)

Guys,
I have to replace some old chinese characters (words) with some new
ones. In the database, the fields are displayed as ?.
The current chinese characters will display fine when you retrieved the
fields through the website. As soon as I cut and paste the new chinese
characters onto the field that I want to replace, the website will
display ? with the fields that I just updated.
I have installed the Chinese (Taiwan) letters and enable my language
toolbar. During the cut and paste process, I also switched to "CH".
Any idea?
Please help.
Thanks in advance,
TonyHi,
You can try to use CAST(0xABCD1234 as NVARCHAR(4)) etc. instead of copy and
paste.
Replace the 0xABCD1234 with the binary code of the Chinese characters.
Kenneth
<tractng@.gmail.com> wrote in message
news:1132790630.847208.228190@.z14g2000cwz.googlegroups.com...
> Guys,
> I have to replace some old chinese characters (words) with some new
> ones. In the database, the fields are displayed as ?.
> The current chinese characters will display fine when you retrieved the
> fields through the website. As soon as I cut and paste the new chinese
> characters onto the field that I want to replace, the website will
> display ? with the fields that I just updated.
>
> I have installed the Chinese (Taiwan) letters and enable my language
> toolbar. During the cut and paste process, I also switched to "CH".
> Any idea?
> Please help.
>
> Thanks in advance,
> Tony
>|||Kenneth,
Can you explain, how I can do this. I am a newbie.
Thanks,
Tony

Checklist for SQL Server

Hi guys
Can anyone provide me some checklist to verify that SQL 2000 is unicode
enabled . In short how do i test the same
Help is appreciated !
Regards
SierraSierra wrote:
> Hi guys
> Can anyone provide me some checklist to verify that SQL 2000 is
> unicode enabled . In short how do i test the same
> Help is appreciated !
> Regards
> Sierra
You use unicode data by using nchar, nvarchar, and ntext data types in
your tables. If you don't use those data types, you can't store unicode
data.
--
David Gugick
Imceda Software
www.imceda.com|||Perhaps I don't fully understand your question but all SQL Server 2000
instances are 'unicode enabled'. One can store unicode data in nchar,
nvarchar or ntext data types.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
news:%23q3lulO2EHA.3392@.TK2MSFTNGP10.phx.gbl...
> Hi guys
> Can anyone provide me some checklist to verify that SQL 2000 is unicode
> enabled . In short how do i test the same
> Help is appreciated !
> Regards
> Sierra
>|||Hi Dan
Thanks for the response
I want to know how to check if a particular database and its tables,
related scripts, stored procedures, etc are Unicode-enabled in SQL 2000
Do you have any info on Test Statergy for the same
Regards
Sierra
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ezaPjOP2EHA.1192@.tk2msftngp13.phx.gbl...
> Perhaps I don't fully understand your question but all SQL Server 2000
> instances are 'unicode enabled'. One can store unicode data in nchar,
> nvarchar or ntext data types.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
> news:%23q3lulO2EHA.3392@.TK2MSFTNGP10.phx.gbl...
> > Hi guys
> >
> > Can anyone provide me some checklist to verify that SQL 2000 is unicode
> > enabled . In short how do i test the same
> >
> > Help is appreciated !
> >
> > Regards
> > Sierra
> >
> >
>|||The scripts below will identify non-unicode character types in tables and
views as well as stored procedure and function parameters.
It's probably best to perform unicode testing as part of your overall
application testing strategy. Include unicode test data and cases in your
testing suite. This will identify issues with application code as well as
SQL objects and scripts.
--list character columns not unicode types
SELECT TOP 10
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME
WHERE
c.DATA_TYPE IN('char', 'varchar', 'text') AND
t.TABLE_TYPE = 'BASE TABLE'
--list stored procedure and function character parameters not unicode types
SELECT TOP 10
SPECIFIC_SCHEMA,
SPECIFIC_NAME,
PARAMETER_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE
DATA_TYPE IN('char', 'varchar', 'text')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
news:u0a4ka52EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hi Dan
> Thanks for the response
> I want to know how to check if a particular database and its tables,
> related scripts, stored procedures, etc are Unicode-enabled in SQL 2000
> Do you have any info on Test Statergy for the same
> Regards
> Sierra
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:ezaPjOP2EHA.1192@.tk2msftngp13.phx.gbl...
>> Perhaps I don't fully understand your question but all SQL Server 2000
>> instances are 'unicode enabled'. One can store unicode data in nchar,
>> nvarchar or ntext data types.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
>> news:%23q3lulO2EHA.3392@.TK2MSFTNGP10.phx.gbl...
>> > Hi guys
>> >
>> > Can anyone provide me some checklist to verify that SQL 2000 is unicode
>> > enabled . In short how do i test the same
>> >
>> > Help is appreciated !
>> >
>> > Regards
>> > Sierra
>> >
>> >
>>
>|||Thanks Dan
Sierra
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ebiT3252EHA.3932@.TK2MSFTNGP12.phx.gbl...
> The scripts below will identify non-unicode character types in tables and
> views as well as stored procedure and function parameters.
> It's probably best to perform unicode testing as part of your overall
> application testing strategy. Include unicode test data and cases in your
> testing suite. This will identify issues with application code as well as
> SQL objects and scripts.
> --list character columns not unicode types
> SELECT TOP 10
> c.TABLE_SCHEMA,
> c.TABLE_NAME,
> c.COLUMN_NAME,
> c.DATA_TYPE
> FROM INFORMATION_SCHEMA.TABLES t
> JOIN INFORMATION_SCHEMA.COLUMNS c ON
> c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
> c.TABLE_NAME = t.TABLE_NAME
> WHERE
> c.DATA_TYPE IN('char', 'varchar', 'text') AND
> t.TABLE_TYPE = 'BASE TABLE'
> --list stored procedure and function character parameters not unicode
types
> SELECT TOP 10
> SPECIFIC_SCHEMA,
> SPECIFIC_NAME,
> PARAMETER_NAME,
> DATA_TYPE
> FROM INFORMATION_SCHEMA.PARAMETERS
> WHERE
> DATA_TYPE IN('char', 'varchar', 'text')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
> news:u0a4ka52EHA.1292@.TK2MSFTNGP10.phx.gbl...
> > Hi Dan
> >
> > Thanks for the response
> >
> > I want to know how to check if a particular database and its tables,
> > related scripts, stored procedures, etc are Unicode-enabled in SQL 2000
> >
> > Do you have any info on Test Statergy for the same
> >
> > Regards
> >
> > Sierra
> >
> >
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:ezaPjOP2EHA.1192@.tk2msftngp13.phx.gbl...
> >> Perhaps I don't fully understand your question but all SQL Server 2000
> >> instances are 'unicode enabled'. One can store unicode data in nchar,
> >> nvarchar or ntext data types.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
> >> news:%23q3lulO2EHA.3392@.TK2MSFTNGP10.phx.gbl...
> >> > Hi guys
> >> >
> >> > Can anyone provide me some checklist to verify that SQL 2000 is
unicode
> >> > enabled . In short how do i test the same
> >> >
> >> > Help is appreciated !
> >> >
> >> > Regards
> >> > Sierra
> >> >
> >> >
> >>
> >>
> >
> >
>

Checking for @@Error in DTS package (was "Error Trouble")

Hi Guys,

SQL Server 2000

I've run out of ideas on what to do with a DTS process that has suddenly started giving an error when one company is processed on it.

Here's the deal:

DTS package launches a complex set of stored procedures (built entirely without error handling) as a step in importing data from various customers. I have isolated the error to one segment where I have to cursor through records that have been identified as having changes. Unfortunately, this cursor is updating about 15 system tables and uses modular stored procedures, about 12 of them, to do the job.

Error: The classic Subquery returned more than one value.

Oddity: If I run the code in Query Analyzer I never get an error, the code finishes without complaints. If I run the exact same code via the DTS package I always get the error. Note that the cursor appears to process every row assigned to it and then the sproc throws the error.

What I've Tried:
1) The obvious, reviewing the code for any unprotected subqueries. Haven't found it.
2) Checking for @.@.Error = 1 at key points in the code to try to isolate where this is happening. Can't catch it.

Note that this same process is used for several other company's data import process and they do not error even when running the DTS package. Therefore I'm assuming it is a data issue with the one company that errors that is causing the problem.

Any suggestions on what to try next would be greatly appreciated!

Thanks!You are assuming @.@.Error is going to be 1

IF @.@.ERROR > 0 then|||Actually I was checking for

IF @.@.Error = 0 Not @.@.Error = 1

Then having it write markers to another table. Seems that every time I checked @.@.Error = 0.

Checking existence of network file

Hi Guys,

Just wonder if anyone know how can I check the network file (whether exists or not) using TSQL or Extended Stored Procedures?

What I can find out is the xp_fileexist, but it is only meant for searching file residing locally... I have tried mapping the drive of another server to the SQL server, but it just won't do it...

Any idea?

Thanks in advance.

Regards,
TangI have used xp_cmdshell as follows:

exec @.result = xp_cmdshell ('dir \\server\share\filename.ext')

if(@.result = 0)

begin
it's there so do something
end

This has worked well so far...

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