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
>>
>