Auto counting in SQL

Hi all,

I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.

So if I for example select all Dates older than today's date, I would want something like this:

110/12/2006210/18/2006310/20/2006410/22/2006510/30/2006

Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".

Is this possible? And if yes, how ? :)

Thanks in advance

Nick

See if this helps:

Declare @.TTable ( col1datetime)Insert into @.TSelect'10/12/2006'unionallSELECT'10/18/2006'unionallSELECT'10/20/2006'unionallSELECT'10/22/2006'unionallSELECT'10/30/2006'SELECTCOUNT(*)AS [Row Number], T1.col1FROM @.T T1, @.T T2WHERE T1.col1 >= T2.col1Group by T1.col1

|||

SELECT *,row_number() OVER (ORDER BY ...) AS [Row Number]

FROM ...

ORDER BY ...

Just fill in the ...'s

|||

ndinakar, thanks for the answer, but not quite what i was looking for

Motley, thanks :) that was exactly what I needed ;)

cheers!

|||

I still don't get it how does this work ... an example:

If I return the values from DB using the following SQL

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTable

it works perfectly. When I add some filtering to WHERE-part

WHERE MyColB <> 'value1' AND MyColB <> 'value2'

I get somewhat strange results. Row numbering in my case starts from row 57, second row jumps to 60, then go normally for couple of rows, then jumps to 77 and so on. So it's definately not sequential. Any ides for this ?

|||

You should make sure that your where is in the correct SELECT statement.
There is for example a big difference in results when used either

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTableWHERE MyColB <> 'value1' AND MyColB <> 'value2'

in stead of

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTableWHERE MyColB <> 'value1' AND MyColB <> 'value2') AS DerivedTable

in the first statement, you're likely to get non-sequential numbering, and in the second one you should get them sequentially

|||

Yes, of course, I didn't realize that, but now it works. Thanks

Auto Commit off and getGeneratedKeys

I have AutoCommit turned off, as I am managing commit and rollback.

I also need to retrieve an identity key. Using the statement.getGeneratedKeys throws the error "The statement must be run before the generated keys are avalilable".

Ok, I can understand this, as I am trying to get the identity key before I am commiting the transaction. However I am inserting the parent row, then based on the identity key, I am inserting several children, all within the same transaction.

So how do I do this?

I d/l the latest JDBC drivers and using Java 1.5Sooooo, I need to commit the parent, then do the children within a transaction, and if anything blows up I rollback the children, then do an explicit delete on the parent? What if the app blows up, I now have a parent without the requisit children.

Sort of defeats the whole transaction thingy.|||

Hi busybee123,

I am a little confused about what you are trying to accomplish. Some sample/pseudo code would be great for helping us understand your algorithm. Are you trying to perform nested transactions or are you using two separate connection objects?

Here is some pseudo code of what I suspect you what to achieve; correct me if I am mistaken:

Connection conn1 = DriverManager.getConnection(...);

Connection conn2 = DriverManager.getConnection(...);

conn1.setAutoCommit(false);

conn2.setAutoCommit(false);

Statement stmt1 = conn1.createStatement();

Statement stmt2 = conn2.createStatement();

String parentUpdates[] = new String[]{all parent updates};

boolean commitParent;

for(int i = 0; i < parentUpdates.length; i++)

{

commitParent = true;

stmt1.executeUpdate(parentUpdates[ i ], Statement.RETURN_GENERATED_KEYS);

ResultSet keys = stmt1.getGeneratedKeys();

while(keys.next())

{

String aKey = keys.getString(1);

stmt2.addBatch(insert the children here given aKey value);

}

try{

int updateCounts[] = stmt2.executeBatch();

}

catch(BatchUpdateException bue)

{

//process success/failures here and decide if to commit conn1 and/or conn2 or not

if(something went wrong)

{

conn2.rollback();

commitParent = false;

}

else

{

conn2.commit();

}

}

if(commitParent)

{

conn1.commit(); //or delete parent now

}

else

{

//break, continue, or throw here

}

}

Thanks,

Jaaved Mohammed - MSFT

|||A few things contributed to my post.

We were using an older JDBC driver which did not support getGeneratedKeys(), so we were using "insert ...;select SCOPE_IDENTITY() as newkey" with a statement.executeQuery(). However when we started using manual commit/rollback, this does not work as it creates an internal virtual connection which cannot be used with manual commit.

So we upgraded to the latest JDBC driver which does support getGeneratedKeys(). Reading through the new documentation we came across your example code. When we tried it out, we got the above noted error message. Searching the web for the error message produced zero hits.

Eventually we found that the statement.executeUpdate() method can take a second parameter with the value of Statement.RETURN_GENERATED_KEYS. This causes MS SQL to return the generated key which can then be retrieved as per the examples. So our bad :-) In retrospect the error message does explain what happened, but hindsight is always 20/20

A few things:
- the error message returned was not entirely clear as to why it was failing, and the error code number was zero.

- No documentation about the error could be found.

- in the HTML documentation under "Using Auto Generated Keys", in the second paragraph, second to last sentence it states "with the returned column name of GENERATED_KEYS". This is incorrect. Inspecting the metadata of the result set shows that the column does not have any name or label (empty string). The value can only be retrieved by using the index number of 1.

- could the error messages produced by the JDBC library be documented please?

Kudos to the JDBC team for supporting getGeneratedKeys()!|||I'm working on a similar issue and this code seems to help a bit, but i have a question : does getGeneratedKeys not work with a PreparedStatement for which i'm using batch update? I have my code on this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1470935&SiteID=1sql

Auto Commit off and getGeneratedKeys

I have AutoCommit turned off, as I am managing commit and rollback.

I also need to retrieve an identity key. Using the statement.getGeneratedKeys throws the error "The statement must be run before the generated keys are avalilable".

Ok, I can understand this, as I am trying to get the identity key before I am commiting the transaction. However I am inserting the parent row, then based on the identity key, I am inserting several children, all within the same transaction.

So how do I do this?

I d/l the latest JDBC drivers and using Java 1.5
Sooooo, I need to commit the parent, then do the children within a transaction, and if anything blows up I rollback the children, then do an explicit delete on the parent? What if the app blows up, I now have a parent without the requisit children.

Sort of defeats the whole transaction thingy.|||

Hi busybee123,

I am a little confused about what you are trying to accomplish. Some sample/pseudo code would be great for helping us understand your algorithm. Are you trying to perform nested transactions or are you using two separate connection objects?

Here is some pseudo code of what I suspect you what to achieve; correct me if I am mistaken:

Connection conn1 = DriverManager.getConnection(...);

Connection conn2 = DriverManager.getConnection(...);

conn1.setAutoCommit(false);

conn2.setAutoCommit(false);

Statement stmt1 = conn1.createStatement();

Statement stmt2 = conn2.createStatement();

String parentUpdates[] = new String[]{all parent updates};

boolean commitParent;

for(int i = 0; i < parentUpdates.length; i++)

{

commitParent = true;

stmt1.executeUpdate(parentUpdates[ i ], Statement.RETURN_GENERATED_KEYS);

ResultSet keys = stmt1.getGeneratedKeys();

while(keys.next())

{

String aKey = keys.getString(1);

stmt2.addBatch(insert the children here given aKey value);

}

try{

int updateCounts[] = stmt2.executeBatch();

}

catch(BatchUpdateException bue)

{

//process success/failures here and decide if to commit conn1 and/or conn2 or not

if(something went wrong)

{

conn2.rollback();

commitParent = false;

}

else

{

conn2.commit();

}

}

if(commitParent)

{

conn1.commit(); //or delete parent now

}

else

{

//break, continue, or throw here

}

}

Thanks,

Jaaved Mohammed - MSFT

|||A few things contributed to my post.

We were using an older JDBC driver which did not support getGeneratedKeys(), so we were using "insert ...;select SCOPE_IDENTITY() as newkey" with a statement.executeQuery(). However when we started using manual commit/rollback, this does not work as it creates an internal virtual connection which cannot be used with manual commit.

So we upgraded to the latest JDBC driver which does support getGeneratedKeys(). Reading through the new documentation we came across your example code. When we tried it out, we got the above noted error message. Searching the web for the error message produced zero hits.

Eventually we found that the statement.executeUpdate() method can take a second parameter with the value of Statement.RETURN_GENERATED_KEYS. This causes MS SQL to return the generated key which can then be retrieved as per the examples. So our bad :-) In retrospect the error message does explain what happened, but hindsight is always 20/20

A few things:
- the error message returned was not entirely clear as to why it was failing, and the error code number was zero.

- No documentation about the error could be found.

- in the HTML documentation under "Using Auto Generated Keys", in the second paragraph, second to last sentence it states "with the returned column name of GENERATED_KEYS". This is incorrect. Inspecting the metadata of the result set shows that the column does not have any name or label (empty string). The value can only be retrieved by using the index number of 1.

- could the error messages produced by the JDBC library be documented please?

Kudos to the JDBC team for supporting getGeneratedKeys()!
|||I'm working on a similar issue and this code seems to help a bit, but i have a question : does getGeneratedKeys not work with a PreparedStatement for which i'm using batch update? I have my code on this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1470935&SiteID=1

auditting select statement

We have triggers written for insert/update/deletes of data, now there is a
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:

> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>

Auditing Question

Suppose I would like to mimic SQL Profiler. That is I would like to capture
every sql statement issued to my SQL Server.
What are my options? Obviously I could find a log reader and read
update,insert or delete statements but I would like to capture exec and
select statements too. I would like to capture all T-SQL statements just as
SQL Profiler does. Can I do this?You can start a trace using system procedures as well (Profiler is actually
client-side tool that uses those traces). Check the sp_trace* procedures.
Note that you can create a trace with Profiler and then save it as a script
as well, so you don't have to do the programming part.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JI" <anonymous@.discussions.microsoft.com> wrote in message
news:uT6IDt$GEHA.2576@.TK2MSFTNGP11.phx.gbl...
> Suppose I would like to mimic SQL Profiler. That is I would like to
capture
> every sql statement issued to my SQL Server.
> What are my options? Obviously I could find a log reader and read
> update,insert or delete statements but I would like to capture exec and
> select statements too. I would like to capture all T-SQL statements just
as
> SQL Profiler does. Can I do this?
>
>

Auditing Question

Suppose I would like to mimic SQL Profiler. That is I would like to capture
every sql statement issued to my SQL Server.
What are my options? Obviously I could find a log reader and read
update,insert or delete statements but I would like to capture exec and
select statements too. I would like to capture all T-SQL statements just as
SQL Profiler does. Can I do this?You can start a trace using system procedures as well (Profiler is actually
client-side tool that uses those traces). Check the sp_trace* procedures.
Note that you can create a trace with Profiler and then save it as a script
as well, so you don't have to do the programming part.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"JI" <anonymous@.discussions.microsoft.com> wrote in message
news:uT6IDt$GEHA.2576@.TK2MSFTNGP11.phx.gbl...
> Suppose I would like to mimic SQL Profiler. That is I would like to
capture
> every sql statement issued to my SQL Server.
> What are my options? Obviously I could find a log reader and read
> update,insert or delete statements but I would like to capture exec and
> select statements too. I would like to capture all T-SQL statements just
as
> SQL Profiler does. Can I do this?
>
>

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permission
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution
Hi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permission
to everybody?
Thanks all.
--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net
_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permis
sion
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Auditing and IP addresses

In a database I created an audit table in which, e.g., I insert one record
for each INSERT statement made on another table (via a trigger).
My problem is to recover remote IP address of the connected client which
does the INSERT.
I tried to use DMV sys.dm_exec_connections because it contains a "client_net
_address"
field: it was just what was searching for.
But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
is not assigned to any user.
May you tell me how can I solve this problem, avoiding to assign that permis
sion
to everybody?
Thanks all.
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolutionHi,
this is the only way to get the information. prior to the DMV you had
to retrieve the information from XP_cmdshell:
http://www.sqlserver2005.de/Articles/3/
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||PBsoft (info[REMOVE]@.pbsoft.it) writes:
> In a database I created an audit table in which, e.g., I insert one record
> for each INSERT statement made on another table (via a trigger).
> My problem is to recover remote IP address of the connected client which
> does the INSERT.
> I tried to use DMV sys.dm_exec_connections because it contains a
> "client_net_address"
> field: it was just what was searching for.
> But, unfortunately, this DMV requires VIEW SERVER STATE permission, which
> is not assigned to any user.
> May you tell me how can I solve this problem, avoiding to assign that
> permission to everybody?
Create a certificate and which you sign the trigger with. You need to
have this certificate in master as well. Then create a login from that
certificate, and grant that login the rights.
I have an article on my web site that describes this in a lot more
detail: http://www.sommarskog.se/grantperm.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||try the auditdatabase
auditing tools ( http://www.auditdatabase.com/AuditTools.html ) for
generate audit triggers (FREE) for SQL Server and other DBMS's)
This triggers save the client IP and MAC information
Delia.
Erland Sommarskog ha escrito:
> PBsoft (info[REMOVE]@.pbsoft.it) writes:
> Create a certificate and which you sign the trigger with. You need to
> have this certificate in master as well. Then create a login from that
> certificate, and grant that login the rights.
> I have an article on my web site that describes this in a lot more
> detail: http://www.sommarskog.se/grantperm.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Attempting to restore a db from a network drive and use the 'with move' statement

Attempting to restore a db from a network drive and need to move the
log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
Here is the script that I am using for this procedure
===================================== USE MASTER
GO
RESTORE FILELISTONLY
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
RESTORE LOG Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
-- WITH NORECOVERY
RESTORE DATABASE Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH NORECOVERY
=====================================
Here are the error(s) that I am receiving
===================================== Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 65535. Database may not be activated yet or
may be in transition.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.war_wheelan@.yahoo.com wrote:
> Attempting to restore a db from a network drive and need to move the
> log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> Here is the script that I am using for this procedure
> =====================================> USE MASTER
> GO
> RESTORE FILELISTONLY
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> RESTORE LOG Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> -- WITH NORECOVERY
> RESTORE DATABASE Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH NORECOVERY
> =====================================> Here are the error(s) that I am receiving
> =====================================> Server: Msg 913, Level 16, State 8, Line 3
> Could not find database ID 65535. Database may not be activated yet or
> may be in transition.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> Server: Msg 5105, Level 16, State 2, Line 7
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> incorrect.
> Server: Msg 3156, Level 16, State 1, Line 7
> File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE DATABASE is terminating abnormally.
>
It looks like you're trying to restore the log before the database has
been restored. Restore the database first, THEN the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
The reason that I attempted to restore the log file first is because I
receive the same error either way.
Tracy McKibben wrote:
> war_wheelan@.yahoo.com wrote:
> > Attempting to restore a db from a network drive and need to move the
> > log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> > AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> >
> > Here is the script that I am using for this procedure
> > =====================================> > USE MASTER
> > GO
> > RESTORE FILELISTONLY
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > RESTORE LOG Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> > -- WITH NORECOVERY
> > RESTORE DATABASE Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH NORECOVERY
> > =====================================> >
> > Here are the error(s) that I am receiving
> > =====================================> > Server: Msg 913, Level 16, State 8, Line 3
> > Could not find database ID 65535. Database may not be activated yet or
> > may be in transition.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > RESTORE LOG is terminating abnormally.
> > Server: Msg 5105, Level 16, State 2, Line 7
> > Device activation error. The physical file name 'C:\Program
> > Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> > incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 7
> > File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> > a valid location for the file.
> > Server: Msg 3013, Level 16, State 1, Line 7
> > RESTORE DATABASE is terminating abnormally.
> >
> It looks like you're trying to restore the log before the database has
> been restored. Restore the database first, THEN the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Attempting to restore a db from a network drive and use the 'with move' statement

Attempting to restore a db from a network drive and need to move the
log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
Here is the script that I am using for this procedure
=====================================
USE MASTER
GO
RESTORE FILELISTONLY
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
RESTORE LOG Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$INDII_NY2_PROD\Data\Indii_L
og.LDF'
-- WITH NORECOVERY
RESTORE DATABASE Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH NORECOVERY
=====================================
Here are the error(s) that I am receiving
=====================================
Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 65535. Database may not be activated yet or
may be in transition.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.Tracy,
The reason that I attempted to restore the log file first is because I
receive the same error either way.
Tracy McKibben wrote:
> war_wheelan@.yahoo.com wrote:
> It looks like you're trying to restore the log before the database has
> been restored. Restore the database first, THEN the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com