Determine next LSN for a Recovering database

Using SQL 2005. Is it possible to determine the LastLSN of a database that
is in "Recovering" mode?
I have a bunch of TransactionLog backups. Some have been restored to that
Recovering database and some have not. Is it possible to "query" the
Recovering database (or inspect the MDF/LDF files) to discover which LSN is
the "Current LastLSN"? If I know that, then I'll know which TransactionLog
backup needs to be restored next.
And to make it more challenging, what if the Recovering database was
restored from a SQL2000 database backup? The TransactionLog backups are
obviously from that same SQL2000 database.
Thanks,
RobThanks for the reply Jamie. I believe what you are referring to is actually
the LSN information of the TransactionLog backup and not of the database.
After selecting a TransactionLog backup to restore, the wizard displays the
FirstLSN and LastLSN information for the TransactionLog backup.
Unfortunately, the wizard does not display which LSN the database actually
"needs". If it did, you could determine which of your TransactionLog backups
is next in line to be restored.
-rob
"thejamie" wrote:
> Over 2 years ago, we went through a recovery. Without knowing exactly, it
> does seem to me the LSN is listed in the wizard dialog when you restore from
> a given transaction log. That was 2000, not 2005. Functionality generally
> remains the same ...Fairly certain that it shows up as one of the column
> headers.
> http://msdn2.microsoft.com/en-us/library/ms177446.aspx
> --
> Regards,
> Jamie
>
> "nexdeveloper" wrote:
> > Using SQL 2005. Is it possible to determine the LastLSN of a database that
> > is in "Recovering" mode?
> >
> > I have a bunch of TransactionLog backups. Some have been restored to that
> > Recovering database and some have not. Is it possible to "query" the
> > Recovering database (or inspect the MDF/LDF files) to discover which LSN is
> > the "Current LastLSN"? If I know that, then I'll know which TransactionLog
> > backup needs to be restored next.
> >
> > And to make it more challenging, what if the Recovering database was
> > restored from a SQL2000 database backup? The TransactionLog backups are
> > obviously from that same SQL2000 database.
> >
> > Thanks,
> > Rob|||Hi Rob,
I understand that you would like to know the current lastLSN value of your
restoring database so that you could know which transaction log backup was
to be restored next.
If I have misunderstood, please let me know.
As far as I know, it is hard to get that information from a restoring
database. Generally when we decide to restore a database, we need to
manually record the date, time and backup files that used to restore
database. The process has many steps which are recommended to be recorded.
However for this issue I will consult the product team to see if they could
give me some valuable information on this issue. I will let you know the
result as soon as possible if I get the answer.
If you have any other questions or concerns, please feel free to let me
know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||nexdeveloper wrote:
> Using SQL 2005. Is it possible to determine the LastLSN of a database that
> is in "Recovering" mode?
> I have a bunch of TransactionLog backups. Some have been restored to that
> Recovering database and some have not. Is it possible to "query" the
> Recovering database (or inspect the MDF/LDF files) to discover which LSN is
> the "Current LastLSN"? If I know that, then I'll know which TransactionLog
> backup needs to be restored next.
> And to make it more challenging, what if the Recovering database was
> restored from a SQL2000 database backup? The TransactionLog backups are
> obviously from that same SQL2000 database.
> Thanks,
> Rob
I believe this will get you what you're looking for:
SELECT last_lsn
FROM dbo.backupset
WHERE backup_set_id = (
SELECT TOP 1 backup_set_id
FROM dbo.restorehistory
WHERE destination_database_name = 'mytest'
ORDER BY restore_date DESC
)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
It seems that my reply is late now. I just got the confirmation from the
product team that we can get that information from the backupset and
restorehistory tables in msdb database, which is same as Tracy's suggestion.
Anyway, I am glad to hear that the issue has been resolved. Please feel
free to let us know if you have any other questions or concerns.
Have a great day!
Charles Wang
Microsoft Online Community Support

Determine MS SQL License Mode on Wintel Servers

Hi there.

I am trying to remotely determine the license mode that was used when installing MS SQL (versions 6, 7 and 2000) on a remote server. I do not have a SQL logon and thus am asking is there a file / registry entry that can tell one if SQL was installed PERSEAT or PERSERVER?

Any help would be greatly appreciated as I have tried searching google and have not found anything.I don't think software license information is available through software installation.

All server available information can be seen by executing:

sp_server_info|||Hi there. No Licensing mode information is available from sp_server_info

Any other stored procedures?|||As I told you above it is not available.|||I understand what you are looking for. In an enterprise environment, it is necessary to audit your servers for licenses. You might acquire servers from other departments or even other companies that were built prior to you having ownership of them. An audit is necessary to not only see what version of SQL server is on a machine but also if it is licensed using per seat or per processor (and how many).

Does anyone know a command or stored procedure to reveal this data? Thanks.

Determine if database is in standby/read-only mode?

Hi all,
Sorry for the frequent posts, but I have one other thing I'd like to figure
out.
Can I write a query to determine if a database is in standby or read-only
mode? I would like to put a check in the restore routine for custom log
shipping so that DIFFs and TRANS are restored when not in standby/read-only.
I tried to trace Enterprise Mangler, and see how it set the Read-only
checkbox for the database properties, and I saw this ...
USE [<DatabaseName>]
SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
dbo.sysfilegroups f
However, even though Enterprise Mangler shows read-only as checked, this
query keeps returning false (0).
Any thoughts? Am I going about this the wrong way?
Thanks for the help!
WadeNevermind, I found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
Thanks!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
> figure out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
> standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>|||Use function databaseproperty.
Example:
use master
go
select
[name],
databaseproperty([name], 'IsInStandBy') as IsInStandBy,
databaseproperty([name], 'IsInRecovery') as IsInRecovery
from
sysdatabases
go
AMB
"Wade" wrote:
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
>|||See DATABASEPROPERTY and DATABASEPROPERTYEX functions in SQL Server Books
Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>

Determine if database is in standby/read-only mode?

Hi all,
Sorry for the frequent posts, but I have one other thing I'd like to figure
out.
Can I write a query to determine if a database is in standby or read-only
mode? I would like to put a check in the restore routine for custom log
shipping so that DIFFs and TRANS are restored when not in standby/read-only.
I tried to trace Enterprise Mangler, and see how it set the Read-only
checkbox for the database properties, and I saw this ...
USE [<DatabaseName>]
SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
dbo.sysfilegroups f
However, even though Enterprise Mangler shows read-only as checked, this
query keeps returning false (0).
Any thoughts? Am I going about this the wrong way?
Thanks for the help!
WadeNevermind, I found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
Thanks!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
> figure out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
> standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>|||Use function databaseproperty.
Example:
use master
go
select
[name],
databaseproperty([name], 'IsInStandBy') as IsInStandBy,
databaseproperty([name], 'IsInRecovery') as IsInRecovery
from
sysdatabases
go
AMB
"Wade" wrote:

> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to figur
e
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in standby/read-onl
y.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
>|||See DATABASEPROPERTY and DATABASEPROPERTYEX functions in SQL Server Books
Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>

Determine if database is in standby/read-only mode?

Hi all,
Sorry for the frequent posts, but I have one other thing I'd like to figure
out.
Can I write a query to determine if a database is in standby or read-only
mode? I would like to put a check in the restore routine for custom log
shipping so that DIFFs and TRANS are restored when not in standby/read-only.
I tried to trace Enterprise Mangler, and see how it set the Read-only
checkbox for the database properties, and I saw this ...
USE [<DatabaseName>]
SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
dbo.sysfilegroups f
However, even though Enterprise Mangler shows read-only as checked, this
query keeps returning false (0).
Any thoughts? Am I going about this the wrong way?
Thanks for the help!
Wade
Nevermind, I found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
Thanks!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
> figure out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
> standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
|||Use function databaseproperty.
Example:
use master
go
select
[name],
databaseproperty([name], 'IsInStandBy') as IsInStandBy,
databaseproperty([name], 'IsInRecovery') as IsInRecovery
from
sysdatabases
go
AMB
"Wade" wrote:

> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
>
|||See DATABASEPROPERTY and DATABASEPROPERTYEX functions in SQL Server Books
Online.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>

Detecting a suspect database...

Howdy.
Hey, I am writing a proc that will monitor database vitals. does anyone know how to tell if a database is in suspect mode from query analyzer? I imagine you should be able to tell from the status column in sysdatabases, but I cant seem to figure it out.
TIAHere's some info
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp

and some more
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-d_5xrn.asp|||Type following on the SQL Query Analyser

USE <database_name>
SELECT DATABASEPROPERTY('<database_name>', 'IsSuspect')

Check result:
1 = TRUE
0 = FALSE
NULL = Invalid input

Hope this helps!!!!!

Design Query in Editor (SSMS)

In Enterprise Manager(EM): click a table then open table in Query mode. A
user can drag and drop tables from different databases. Once a user connects
a common ID the tool will automatically create the JOINS for you.
Question--
In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
the same functionality described in the EM above. Is there a certain way to
do this besides typing it the query window?Open the Query Designer (Click on the "New Query" button).
On the toolbar just over the frame you use to write your queries, you find
these buttons (from let to right):
-Connect
-Disconnect,
-Change Connection
-Combo to select the Database to be used
-Execute
-Check syntax
-stop execution
-Display Estimated query Plan
-Analyze query in Database Engine Tuning Advisor
-DESIGN QUERY IN EDITOR (the button with the classic symbol for Microsoft
design mode...)
-...etc, etc.
Have a good job
Gilberto Zampatti
"morphius" wrote:
> In Enterprise Manager(EM): click a table then open table in Query mode. A
> user can drag and drop tables from different databases. Once a user connects
> a common ID the tool will automatically create the JOINS for you.
> Question--
> In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
> the same functionality described in the EM above. Is there a certain way to
> do this besides typing it the query window?|||But the DESIGN QUERY IN EDITOR ( the classic symbol for Microsoft
> design mode...) does not allow a user to do the functionality described below?
"Gilberto Zampatti" wrote:
> Open the Query Designer (Click on the "New Query" button).
> On the toolbar just over the frame you use to write your queries, you find
> these buttons (from let to right):
> -Connect
> -Disconnect,
> -Change Connection
> -Combo to select the Database to be used
> -Execute
> -Check syntax
> -stop execution
> -Display Estimated query Plan
> -Analyze query in Database Engine Tuning Advisor
> -DESIGN QUERY IN EDITOR (the button with the classic symbol for Microsoft
> design mode...)
> -...etc, etc.
> Have a good job
> Gilberto Zampatti
> "morphius" wrote:
> > In Enterprise Manager(EM): click a table then open table in Query mode. A
> > user can drag and drop tables from different databases. Once a user connects
> > a common ID the tool will automatically create the JOINS for you.
> >
> > Question--
> > In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
> > the same functionality described in the EM above. Is there a certain way to
> > do this besides typing it the query window?|||The behavior will be slightly different, but - for example - if you drag two
tables and connect two fields (with or without the same name), the designer
proposes automaticllay an INNER JOIN.
What do you need exactly?
Gilberto Zampatti
"morphius" wrote:
> But the DESIGN QUERY IN EDITOR ( the classic symbol for Microsoft
> > design mode...) does not allow a user to do the functionality described below?
>
> "Gilberto Zampatti" wrote:
> > Open the Query Designer (Click on the "New Query" button).
> > On the toolbar just over the frame you use to write your queries, you find
> > these buttons (from let to right):
> > -Connect
> > -Disconnect,
> > -Change Connection
> > -Combo to select the Database to be used
> > -Execute
> > -Check syntax
> > -stop execution
> > -Display Estimated query Plan
> > -Analyze query in Database Engine Tuning Advisor
> > -DESIGN QUERY IN EDITOR (the button with the classic symbol for Microsoft
> > design mode...)
> > -...etc, etc.
> > Have a good job
> > Gilberto Zampatti
> > "morphius" wrote:
> >
> > > In Enterprise Manager(EM): click a table then open table in Query mode. A
> > > user can drag and drop tables from different databases. Once a user connects
> > > a common ID the tool will automatically create the JOINS for you.
> > >
> > > Question--
> > > In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
> > > the same functionality described in the EM above. Is there a certain way to
> > > do this besides typing it the query window?