know if DB has been restored

Hi,
I wan't an application to be aware if the database has been restored since
the last time it connected to the database.
The reason is that because of security reasons the database may never be
restored without knowing it (to cancel for instance a transaction). So I
would like to encrypt somehow the last-restore-date in the database, and
check this value every time with the real value.
Is this possible? Or something else that will give me the same possibility
to be aware of restores?
Thanks a lot in advance,
Pieterselect crdate from master.dbo.sysdatabases where name = 'databasename'
The CRDate gets updated when you restore a database.
You could copy the "correct" value to another table and use a query to
compare them.
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23IzW7pt%23HHA.4784@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I wan't an application to be aware if the database has been restored since
> the last time it connected to the database.
> The reason is that because of security reasons the database may never be
> restored without knowing it (to cancel for instance a transaction). So I
> would like to encrypt somehow the last-restore-date in the database, and
> check this value every time with the real value.
> Is this possible? Or something else that will give me the same possibility
> to be aware of restores?
>
> Thanks a lot in advance,
>
> Pieter
>|||On Wed, 19 Sep 2007 13:11:52 -0400, "Geoff Chovaz"
<chovaz@.nospam.nospam> wrote:
>select crdate from master.dbo.sysdatabases where name = 'databasename'
>The CRDate gets updated when you restore a database.
>You could copy the "correct" value to another table and use a query to
>compare them.
But if that other table is in the same database it gets restored with
everything else. To be any use I think it would have to be in a
different database.
Roy Harvey
Beacon Falls, CT|||Great! Thank you very much!!!
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:eqtYuAu%23HHA.4200@.TK2MSFTNGP04.phx.gbl...
> select crdate from master.dbo.sysdatabases where name = 'databasename'
> The CRDate gets updated when you restore a database.
> You could copy the "correct" value to another table and use a query to
> compare them.
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
> news:%23IzW7pt%23HHA.4784@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I wan't an application to be aware if the database has been restored
>> since the last time it connected to the database.
>> The reason is that because of security reasons the database may never be
>> restored without knowing it (to cancel for instance a transaction). So I
>> would like to encrypt somehow the last-restore-date in the database, and
>> check this value every time with the real value.
>> Is this possible? Or something else that will give me the same
>> possibility to be aware of restores?
>>
>> Thanks a lot in advance,
>>
>> Pieter
>|||Yes, you would need to keep it in a separate database...
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Roy Harvey (MVP)" <roy_harvey@.snet.net> wrote in message
news:m6n2f3lllklq9l2okt68a0ukqf4ib4n61t@.4ax.com...
> On Wed, 19 Sep 2007 13:11:52 -0400, "Geoff Chovaz"
> <chovaz@.nospam.nospam> wrote:
>>select crdate from master.dbo.sysdatabases where name = 'databasename'
>>The CRDate gets updated when you restore a database.
>>You could copy the "correct" value to another table and use a query to
>>compare them.
> But if that other table is in the same database it gets restored with
> everything else. To be any use I think it would have to be in a
> different database.
> Roy Harvey
> Beacon Falls, CT|||Pieter,
You can also use the restore history in the msdb database.
select * from msdb..restorehistory
The usability of this depends on how long you keep restore history around
before purging it.
RLF
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23IzW7pt%23HHA.4784@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I wan't an application to be aware if the database has been restored since
> the last time it connected to the database.
> The reason is that because of security reasons the database may never be
> restored without knowing it (to cancel for instance a transaction). So I
> would like to encrypt somehow the last-restore-date in the database, and
> check this value every time with the real value.
> Is this possible? Or something else that will give me the same possibility
> to be aware of restores?
>
> Thanks a lot in advance,
>
> Pieter
>|||No it isn't: That's just te trick:
For instance you have a database that you started to use the first of
january, so you put that date in the table.
Than: the second of march they take a backup, do some fraud on the original
databse, and than put back the backup... The restore date will be second of
march, but they date I wrote in the database will still be first of
january...
"Roy Harvey (MVP)" <roy_harvey@.snet.net> wrote in message
news:m6n2f3lllklq9l2okt68a0ukqf4ib4n61t@.4ax.com...
> On Wed, 19 Sep 2007 13:11:52 -0400, "Geoff Chovaz"
> <chovaz@.nospam.nospam> wrote:
> But if that other table is in the same database it gets restored with
> everything else. To be any use I think it would have to be in a
> different database.
> Roy Harvey
> Beacon Falls, CT|||Thanks! This one works fine :-)
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OqQyl9v%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Pieter,
> You can also use the restore history in the msdb database.
> select * from msdb..restorehistory
> The usability of this depends on how long you keep restore history around
> before purging it.
> RLF|||Actually: it doesn't seem to work: it gives me the creation date of the
database, not the last restore date... Although it's helpfull too, and the
solution of Russell Fields works fine for the Last Restore Date:
-- Gives the Creation Date of the DataBase, not the Last Restore
SELECT * FROM master.dbo.sysdatabases WHERE NAME = 'LGDPFULL'
--Gives the Last Restore Date of the DataBase
SELECT TOP 1 * FROM msdb..restorehistory WHERE destination_database_name ='LGDPFULL' ORDER BY restore_history_id DESC
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:eqtYuAu%23HHA.4200@.TK2MSFTNGP04.phx.gbl...
> select crdate from master.dbo.sysdatabases where name = 'databasename'
> The CRDate gets updated when you restore a database.
> You could copy the "correct" value to another table and use a query to
> compare them.
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer|||It worked for me, I tried it on 2 different databases and the CR date was
replaced each time.
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:uYvrL71%23HHA.320@.TK2MSFTNGP04.phx.gbl...
> Actually: it doesn't seem to work: it gives me the creation date of the
> database, not the last restore date... Although it's helpfull too, and the
> solution of Russell Fields works fine for the Last Restore Date:
> -- Gives the Creation Date of the DataBase, not the Last Restore
> SELECT * FROM master.dbo.sysdatabases WHERE NAME = 'LGDPFULL'
> --Gives the Last Restore Date of the DataBase
> SELECT TOP 1 * FROM msdb..restorehistory WHERE destination_database_name => 'LGDPFULL' ORDER BY restore_history_id DESC
> "Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
> news:eqtYuAu%23HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> select crdate from master.dbo.sysdatabases where name = 'databasename'
>> The CRDate gets updated when you restore a database.
>> You could copy the "correct" value to another table and use a query to
>> compare them.
>>
>> --
>> Geoff Chovaz
>> MCTS: SQL Server 2005
>> MCITP: Database Administrator
>> MCITP: Database Developer
>|||Also, there are restore history tables in msdb...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:uy3wEH4%23HHA.4656@.TK2MSFTNGP04.phx.gbl...
> It worked for me, I tried it on 2 different databases and the CR date was replaced each time.
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
> news:uYvrL71%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>> Actually: it doesn't seem to work: it gives me the creation date of the database, not the last
>> restore date... Although it's helpfull too, and the solution of Russell Fields works fine for the
>> Last Restore Date:
>> -- Gives the Creation Date of the DataBase, not the Last Restore
>> SELECT * FROM master.dbo.sysdatabases WHERE NAME = 'LGDPFULL'
>> --Gives the Last Restore Date of the DataBase
>> SELECT TOP 1 * FROM msdb..restorehistory WHERE destination_database_name = 'LGDPFULL' ORDER BY
>> restore_history_id DESC
>> "Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
>> news:eqtYuAu%23HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> select crdate from master.dbo.sysdatabases where name = 'databasename'
>> The CRDate gets updated when you restore a database.
>> You could copy the "correct" value to another table and use a query to compare them.
>>
>> --
>> Geoff Chovaz
>> MCTS: SQL Server 2005
>> MCITP: Database Administrator
>> MCITP: Database Developer
>>
>

Key Violation during synchronization

Dear Friends

I restored same database in Publisher & subscriber.If I want to apply Transactional replication I have to apply initial snapshot.then I am getting key-violation problems during initialization .Some times Primary key table will be dropped before foreign key table.sometimes it won't able to drop some indexes.This I am getting if I am going for all tables of database.But here i need all tables.If I am going for selective table I don't have any problem.For avoiding this problem I tried all the options in table article option in publication wizard.But some na some key violation I am getting always.So please give me some better suggestion

Thanks in Advance

Filson

hi,

though i haven't done this

how about having a snapshot replication for the table that

encounter the key violation?

When your transaction replication encounters a key violation

run the snapshot replication.

this will work if there is no updating from the subscriber.

works like a multiple publication -multiple subscriber topology

regards,

|||

Sounds like you are trying to initialize a subscription with a backup. You may want to check the following document in book online to see if it helps.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Gary

|||

Gary I won't able to find this document in net.Can you give me it as a link.

Thanks

Filson

|||

You can access the article on web from here.

http://msdn2.microsoft.com/en-us/library/ms151705.aspx

FYI, the previous link I post is for SQL Server Book Online.

Cheers,

Gary

Key Violation during synchronization

Dear Friends

I restored same database in Publisher & subscriber.If I want to apply Transactional replication I have to apply initial snapshot.then I am getting key-violation problems during initialization .Some times Primary key table will be dropped before foreign key table.sometimes it won't able to drop some indexes.This I am getting if I am going for all tables of database.But here i need all tables.If I am going for selective table I don't have any problem.For avoiding this problem I tried all the options in table article option in publication wizard.But some na some key violation I am getting always.So please give me some better suggestion

Thanks in Advance

Filson

hi,

though i haven't done this

how about having a snapshot replication for the table that

encounter the key violation?

When your transaction replication encounters a key violation

run the snapshot replication.

this will work if there is no updating from the subscriber.

works like a multiple publication -multiple subscriber topology

regards,

|||

Sounds like you are trying to initialize a subscription with a backup. You may want to check the following document in book online to see if it helps.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Gary

|||

Gary I won't able to find this document in net.Can you give me it as a link.

Thanks

Filson

|||

You can access the article on web from here.

http://msdn2.microsoft.com/en-us/library/ms151705.aspx

FYI, the previous link I post is for SQL Server Book Online.

Cheers,

Gary

KDC error

Hi,
Since I have restored the MSDB database I have been
getting the following error:
Event Type: Error
Event Source: KDC
Event Category: None
Event ID: 11
Date: 07/12/2003
Time: 10:10:02:PM
User: N/A
Computer: SERVER1
Description:
There are multiple accounts with name
MSSQLSvc/SERVER1.DOMAIN.com:1433 of type 10.
does anyone know what this error is? every time I get this
there are 10 occurances within a 1minute period.
the SQL server is SQL Server 2000 SP2, on a Windows 2000
SP3 Server.
any help will be much appreciated| Hi,
|
| Since I have restored the MSDB database I have been
| getting the following error:
|
| Event Type: Error
| Event Source: KDC
| Event Category: None
| Event ID: 11
| Date: 07/12/2003
| Time: 10:10:02:PM
| User: N/A
| Computer: SERVER1
| Description:
| There are multiple accounts with name
| MSSQLSvc/SERVER1.DOMAIN.com:1433 of type 10.
|
| does anyone know what this error is? every time I get this
| there are 10 occurances within a 1minute period.
|
| the SQL server is SQL Server 2000 SP2, on a Windows 2000
| SP3 Server.
|
| any help will be much appreciated
--
This error is not related to MSDB. This error is related to the service
principle name (spn). Maybe the spn for SQL Server is not registered with
Active Directory.
Some information about SPNs:
HOW TO: Troubleshoot the "Cannot Generate SSPI Context" Error Message
http://support.microsoft.com/?id=811889
For more details please speak with your domain administrator.
Hope this helps,
--
Eric Cárdenas
SQL Server support