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

Killing automatically crashed sessions ??

If one of my workstations (which communicate using ODBC) crashes, I still see it as connected in Enterprise Manager under management\current activity\.
Is there anyway to kill automatically the crashed cessions?
Thkx in advanceYou could periodicaly scan the sysprocesses table and kill any connection that hasn't had activity in some predeturmined amount of time.

Keep all servers registered and connected

I have 5 servers in my Server Mgmt Studio divided into 4 groups,

Local

Production

Test

Development

In Sql 2000 Enterprise Manager would put the little green arrow next to your db if it was connected, I noticed that Sql 2005 does do that but it wont allow me to keep all 5 servers connected and I need them all to stay connected.

If I look under Registered Servers it shows all 5 servers there but again only 2 show as connected even if I connect the other ones they dont stay connected. I have deleted the registerd ones and re added them and still no difference.

How can I keep all servers registered and connected and then have them show up on start-up?

Thanks!

Stokh

Launch Mgmt Studio

In the Registered Servers tool window right-click on the server and select Properties

On the Properties dialog select the Connection Properties tab

Set Connection time-out to 0 (the default is 15 seconds; 0 indicates no time-out).

|||I already did that and it still does the same thing, only shows 2 servers registered and doesnt keep the other 3 connected|||I suggest you file a bug using the Product Feedback Center: http://lab.msdn.microsoft.com/productfeedback/Default.aspx.|||I would also like to complain about this new 2005 behavior. As a DBA managing dozens of servers, it is ridiculous to keep jumping through the connection hoops in 2005 whenever I switch server context. It's hard to believe MS could screw up something so basic... and something that worked perfectly well in 2000.|||The best thing to do is search the Product Feedback Center for the issue and vote on it. In SP2 we are targeting many of the annoyances of the Mgmt Studio but we need your votes. The voting system let's us know what issues are causing pain for people and those tend to float to the top part of the list.