Dear All,
I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,
Entry 1: [1, 2]
Entry 2: [3, 8]
Entry 3: [9, 10]
Entry 4: [11, 11]
Entry 5: [12, 20]
The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?
Thanks.You'll have to explain that better.|||For example, Entry 3: [9, 10] means Entry 3 has the number range starting from 9 and ending at 10.
The winning number will be drawn from the number range.
You'll have to explain that better.|||Clear as mud.
If you want to ensure two entries do not share the same starting value, then make the starting value the primary key or a unique index.|||blindman: qxz wants to validate new entries to ensure that it does not conflict with any existing entry and wants to enforce this validation in case of multiple users updating the Db same time...
qxz: a table lock is needed to ensure proper insert. remember explicit locks can have negative effect on performance
create procedure CheckIt (@.StVal int, @.EndVal int)
as
BEGIN TRANSACTION
declare @.CheckFailed char(1)
set @.CheckFailed = 'N'
if exists (select * from MyTable with (tablockx) where @.StVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if exists (select * from MyTable where @.EndVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if @.CheckFailed = 'Y'
begin
ROLLBACK TRANSACTION
return 0
end
else
begin
insert into MyTable .....
COMMIT TRANSACTION
return 1
end
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Thursday, March 29, 2012
Database concurrency
we are developing an ASP.NET application with SqlServer at backend..
there are an supplier and about 3000 customer, and each customer has about
3-4 users. they are selling mobile phone counters.
while a sale occurs, we are selecting customers stock amount, if it is
bigger then sale amount, we are updating its stock, and we increment
suppliers stock. The query is like that :
DECLARE @.StockAmount int
SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerId
IF @.StockAmount > @.SaleAmount
UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
@.BuyerCustomerId
SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
@.SellerCustomerId
UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
@.SellerCustomerId
i know, each customer has got not so many users and a concurrency problem
seems to be not a big possibility. but there is just a 1 supplier record and
i think conlicts are possible. how can i alter this problem. after a
research, i found
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE
CustomerId = @.SellerCustomerId
seems to work fine for us, but it will block the row untill the transaction
finishes. any approaches are appreciated. thanks.Try
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (UPDLOCK) WHERE
CustomerId = @.SellerCustomerId
Some amount of blocks is unavoidable.
This lock hint won't block others for reading. It will assure that the data
has not changed since you last read it
"The Crow" <q> wrote in message
news:ODUI3bVVFHA.2984@.tk2msftngp13.phx.gbl...
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId =
@.BuyerCustomerId
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record
and
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK)
WHERE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the
transaction
> finishes. any approaches are appreciated. thanks.
>|||You can use locking hint "UPDLOCK" or you can modify the statement and use
something like:
UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
...
AMB
"The Crow" wrote:
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerI
d
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record a
nd
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHE
RE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the transactio
n
> finishes. any approaches are appreciated. thanks.
>
>|||This is the SQL Server Books Online explanations :
UPDATE LOCK :
Used on resources that can be updated. Prevents a common form of deadlock
that occurs when multiple sessions are reading, locking, and potentially
updating resources later.
UPDLOCK :
Takes update locks instead of shared locks. Cannot be used with NOLOCK or
XLOCK.
so, shared lock as u may know doesnt prevent reading data but modyfying,
which is not the case.|||UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
this statement "SELECT"s the suitable row aquiring update lock which behaves
same as shared lock, and then converts it exclusive lock prior to doing
actual update. isnt it?
there are an supplier and about 3000 customer, and each customer has about
3-4 users. they are selling mobile phone counters.
while a sale occurs, we are selecting customers stock amount, if it is
bigger then sale amount, we are updating its stock, and we increment
suppliers stock. The query is like that :
DECLARE @.StockAmount int
SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerId
IF @.StockAmount > @.SaleAmount
UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
@.BuyerCustomerId
SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
@.SellerCustomerId
UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
@.SellerCustomerId
i know, each customer has got not so many users and a concurrency problem
seems to be not a big possibility. but there is just a 1 supplier record and
i think conlicts are possible. how can i alter this problem. after a
research, i found
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE
CustomerId = @.SellerCustomerId
seems to work fine for us, but it will block the row untill the transaction
finishes. any approaches are appreciated. thanks.Try
SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (UPDLOCK) WHERE
CustomerId = @.SellerCustomerId
Some amount of blocks is unavoidable.
This lock hint won't block others for reading. It will assure that the data
has not changed since you last read it
"The Crow" <q> wrote in message
news:ODUI3bVVFHA.2984@.tk2msftngp13.phx.gbl...
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId =
@.BuyerCustomerId
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record
and
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK)
WHERE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the
transaction
> finishes. any approaches are appreciated. thanks.
>|||You can use locking hint "UPDLOCK" or you can modify the statement and use
something like:
UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
...
AMB
"The Crow" wrote:
> we are developing an ASP.NET application with SqlServer at backend..
> there are an supplier and about 3000 customer, and each customer has about
> 3-4 users. they are selling mobile phone counters.
> while a sale occurs, we are selecting customers stock amount, if it is
> bigger then sale amount, we are updating its stock, and we increment
> suppliers stock. The query is like that :
> DECLARE @.StockAmount int
> SELECT @.StockAmount = Amont FROM Stocks WHERE CustomerId = @.BuyerCustomerI
d
> IF @.StockAmount > @.SaleAmount
> UPDATE Stocks SET amount = @.StockAmount - @.SaleAmount WHERE CustomerId =
> @.BuyerCustomerId
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
> @.SellerCustomerId
> UPDATE Stocks SET amount = @.StockAmount + @.SaleAmount WHERE CustomerId =
> @.SellerCustomerId
> i know, each customer has got not so many users and a concurrency problem
> seems to be not a big possibility. but there is just a 1 supplier record a
nd
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHE
RE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the transactio
n
> finishes. any approaches are appreciated. thanks.
>
>|||This is the SQL Server Books Online explanations :
UPDATE LOCK :
Used on resources that can be updated. Prevents a common form of deadlock
that occurs when multiple sessions are reading, locking, and potentially
updating resources later.
UPDLOCK :
Takes update locks instead of shared locks. Cannot be used with NOLOCK or
XLOCK.
so, shared lock as u may know doesnt prevent reading data but modyfying,
which is not the case.|||UPDATE Stocks
SET amount = amount - @.SaleAmount
WHERE CustomerId = @.BuyerCustomerId and amount > @.SaleAmount
this statement "SELECT"s the suitable row aquiring update lock which behaves
same as shared lock, and then converts it exclusive lock prior to doing
actual update. isnt it?
Database Compatibilty with ASP.net
Hi:
If I will export my SQL express based database to SQL 2000 database - would I will be facing any compatibiltiy issues with my application. Do I just need to change the connection string in my config file and everything will work?
Danka
No, it won't just "work". It is possible for it to work though. You will need to ensure that your SQL Compatibility level on your express database is set to 80. Even then, I am not sure about express. It may not be possible at all.
Database Comparison
I had an application go down yesterday...it was giving a Type Mismatch error and the support team couldn't figure it out.
My suspicion is that a field in the database got populated with a character the vb app didn't like when it renders the information.
I'd like to prove that theory...but the database is large (4gb) and I'm not sure how to do a database comparison to see where the records differ.
Anybody have a script to get me started OR some experience to share?
Thanks,
Alex8675Anybody have some real, useful, practical information that could help us figure out what your problem is?
Exact text of error?
DDL of table?
Datatype of column?
We are not mentalists, here. We don't play guessing games.
My suspicion is that a field in the database got populated with a character the vb app didn't like when it renders the information.
I'd like to prove that theory...but the database is large (4gb) and I'm not sure how to do a database comparison to see where the records differ.
Anybody have a script to get me started OR some experience to share?
Thanks,
Alex8675Anybody have some real, useful, practical information that could help us figure out what your problem is?
Exact text of error?
DDL of table?
Datatype of column?
We are not mentalists, here. We don't play guessing games.
Tuesday, March 27, 2012
Database Collation: Chinese, Japanese, Korean
My application supports multiple languages/locales in a single database. Some of our new customers want to support Chinese, Japanese, Korean, Italian, Spanish, and German in addition to English. Supporting the Latin based languages is not a problem. But I am having trouble finding a collation sequence that allows me to store the other double byte languages in the same database correctly.
I have found changing the data types from text, char, varchar to ntext, nchar, nvarchar and adding an N in front of the various strings that getting inserted seems to work:
insert into CONTENTDATA (recordid, xml)
values (newid(), N'<CHANNEL1><FILE1/><TEXT1><![CDATA[和红魔拉拉队的动感精神
]]></TEXT1><TEXT3><![CDATA[和红魔拉拉队的动感精神]]></TEXT3></CHANNEL1>');
But this is not going to be a practical solution for us. Is there a collation sequence that would allow us to store multiple locales like we do in Oracle (AL32UTF8)?
Thanks in advance
Dov RosenbergYou can store multiple languages in SQL Server (since version 7.0) using the Unicode data types as you have noted. In Setup, you can also choose the default Server collation.If you need a finer level of collation control subsequently, you can use the multiple collations per column feature in SQL Server 2000 / 2005. More details at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp, http://msdn.microsoft.com/SQL/2000/learn/internat/default.aspx.
Database become suspect..
Hello,
One of my production database become suspect. Here is the messages taken from event veiwer-> Application Log. This is all I can share with you. Please help.
Regards
Nurullah
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA "19013 :
SQL server listening on TCP, Shared Memory, Named Pipes.
"
--
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 17126 :
SQL Server is ready for client connections =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'. =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=3D2610 Failed Assertion =3D 'spaceNeeded <=3D spaceContig && spaceNeeded <=3D space_usable'.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "18052 :
Error: 3624, Severity: 20, State: 1.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '1618d4000000100' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <scanrid.cpp>, line=3D321 Failed Assertion =3D 'm_len !=3D 0'.
"
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '=01' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:34 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3063 Failed Assertion =3D '(m_lastLSN =3D=3D NullLSN) || (m_lastLSN > m_curLSN)'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3282 Failed Assertion =3D 'm_lastLSN =3D=3D NullLSN || startLSN < m_lastLSN'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9004, Severity: 23, State: 7
An error occurred while processing the log for database 'eBill_Main'. "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9001, Severity: 21, State: 1
The log for database 'eBill_Main' is not available. "
--An assertion is when a piece of SQL Server's C Code goes west.
This line interested me though
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=2610
Failed Assertion = 'spaceNeeded <= spaceContig &&
spaceNeeded <= space_usable'.
Do you have enough disk space?
What version SQL Server and SP are you running ?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
One of my production database become suspect. Here is the messages taken from event veiwer-> Application Log. This is all I can share with you. Please help.
Regards
Nurullah
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA "19013 :
SQL server listening on TCP, Shared Memory, Named Pipes.
"
--
9/18/2003 12:27:57 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 17126 :
SQL Server is ready for client connections =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'. =09
--
9/18/2003 12:28:08 PM MSSQLSERVER Information
-2 17055 N/A SERVERA 8128:00:00
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=3D2610 Failed Assertion =3D 'spaceNeeded <=3D spaceContig && spaceNeeded <=3D space_usable'.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "18052 :
Error: 3624, Severity: 20, State: 1.
"
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '1618d4000000100' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <scanrid.cpp>, line=3D321 Failed Assertion =3D 'm_len !=3D 0'.
"
--
9/18/2003 12:33:31 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 644, Severity: 21, State: 5
Could not find the index entry for RID '=01' in index page (1:9759), index ID 2, database 'eBill_Main'. "
--
9/18/2003 12:33:34 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3063 Failed Assertion =3D '(m_lastLSN =3D=3D NullLSN) || (m_lastLSN > m_curLSN)'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <logscan.cpp>, line=3D3282 Failed Assertion =3D 'm_lastLSN =3D=3D NullLSN || startLSN < m_lastLSN'.
"
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9004, Severity: 23, State: 7
An error occurred while processing the log for database 'eBill_Main'. "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 3314, Severity: 21, State: 4
Error while undoing logged operation in database 'eBill_Main'. Error at log record ID (25502:819:174). "
--
9/18/2003 12:33:36 PM MSSQLSERVER Error -2
17052 N/A SERVERA "Error: 9001, Severity: 21, State: 1
The log for database 'eBill_Main' is not available. "
--An assertion is when a piece of SQL Server's C Code goes west.
This line interested me though
9/18/2003 12:33:29 PM MSSQLSERVER Error -2
17055 N/A SERVERA "17066 :
SQL Server Assertion: File: <page.cpp>, line=2610
Failed Assertion = 'spaceNeeded <= spaceContig &&
spaceNeeded <= space_usable'.
Do you have enough disk space?
What version SQL Server and SP are you running ?
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Sunday, March 25, 2012
database backups
We have a Veritas application that backs up our databases at night. There are two databases that are backed up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines below. We the program gets to these tw
o databases the process that is running changes to spid? while the other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does the process start the database up first? Why would the database not be online
already? And what does the "IN LOAD" phrase mean?
Thanks,
backupDatabase backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backupDatabase restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
spid51Starting up database 'master4IDR'.
spid51Recovery is checkpointing database 'master4IDR' (15)
spid51Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51Starting up database 'master4IDR'.
spid51Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51Starting up database 'master4IDR'.
backupDatabase backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backupDatabase backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
backupDatabase restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
spid51Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
spid51Starting up database 'model4IDR'.
spid51Recovery is checkpointing database 'model4IDR' (15)
spid51Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51Starting up database 'model4IDR'.
spid51Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51Starting up database 'model4IDR'.
backupDatabase backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
Dan D.
Perhaps the autoclose database option is set for these databases. However, considering the names of the
database, my guess is that veritas does something strange to these databases, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
I'd be happy to hear... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
below. We the program gets to these two databases the process that is running changes to spid? while the
other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
the process start the database up first? Why would the database not be online already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> --
> Dan D.
|||Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm wondering why the file is there?
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However, considering the names of the
> database, my guess is that veritas does something strange to these databases, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
> below. We the program gets to these two databases the process that is running changes to spid? while the
> other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be online already? And what does the "IN
> LOAD" phrase mean?
>
>
|||Again, my guess is that it is Veritas that creates these files and does some strange detach thingy. It is not
SQL Server. Profiler will tell you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm
wondering why the file is there?[vbcol=seagreen]
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
out,[vbcol=seagreen]
backed[vbcol=seagreen]
lines[vbcol=seagreen]
does[vbcol=seagreen]
"IN[vbcol=seagreen]
o databases the process that is running changes to spid? while the other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does the process start the database up first? Why would the database not be online
already? And what does the "IN LOAD" phrase mean?
Thanks,
backupDatabase backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backupDatabase restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
spid51Starting up database 'master4IDR'.
spid51Recovery is checkpointing database 'master4IDR' (15)
spid51Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51Starting up database 'master4IDR'.
spid51Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51Starting up database 'master4IDR'.
backupDatabase backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backupDatabase backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
backupDatabase restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
spid51Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
spid51Starting up database 'model4IDR'.
spid51Recovery is checkpointing database 'model4IDR' (15)
spid51Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51Starting up database 'model4IDR'.
spid51Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51Starting up database 'model4IDR'.
backupDatabase backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
Dan D.
Perhaps the autoclose database option is set for these databases. However, considering the names of the
database, my guess is that veritas does something strange to these databases, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
I'd be happy to hear... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
below. We the program gets to these two databases the process that is running changes to spid? while the
other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
the process start the database up first? Why would the database not be online already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> --
> Dan D.
|||Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm wondering why the file is there?
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However, considering the names of the
> database, my guess is that veritas does something strange to these databases, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
> below. We the program gets to these two databases the process that is running changes to spid? while the
> other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be online already? And what does the "IN
> LOAD" phrase mean?
>
>
|||Again, my guess is that it is Veritas that creates these files and does some strange detach thingy. It is not
SQL Server. Profiler will tell you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm
wondering why the file is there?[vbcol=seagreen]
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
out,[vbcol=seagreen]
backed[vbcol=seagreen]
lines[vbcol=seagreen]
does[vbcol=seagreen]
"IN[vbcol=seagreen]
database backups
We have a Veritas application that backs up our databases at night. There ar
e two databases that are backed up - model4IDR and master4IDR - whose lines
in the log files are different. I've included some sample lines below. We t
he program gets to these tw
o databases the process that is running changes to spid' while the other da
tabases are backed up by "backup". These two databases don't show up in Ente
rprise Manager. Why does the process start the database up first? Why would
the database not be online
already? And what does the "IN LOAD" phrase mean?
Thanks,
backup Database backed up: Database: master, creation date(time): 2004/06/10
(20:01:42)
backup Database restored: Database: master4IDR, creation date(time): 2004/06
/10(20:01:
spid51 Starting up database 'master4IDR'.
spid51 Recovery is checkpointing database 'master4IDR' (15)
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN
LOAD.
spid51 Starting up database 'master4IDR'.
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN
LOAD.
spid51 Starting up database 'master4IDR'.
backup Database backed up: Database: master, creation date(time): 2004/06/10
(20:01:42)
backup Database backed up: Database: model, creation date(time): 2000/08/06(
01:40:52),
backup Database restored: Database: model4IDR, creation date(time): 2000/08/
06(01:40:5
spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored p
rocedure '
spid51 Starting up database 'model4IDR'.
spid51 Recovery is checkpointing database 'model4IDR' (15)
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN L
OAD.
spid51 Starting up database 'model4IDR'.
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN L
OAD.
spid51 Starting up database 'model4IDR'.
backup Database backed up: Database: model, creation date(time): 2000/08/06(
01:40:52),
--
Dan D.Perhaps the autoclose database option is set for these databases. However, c
onsidering the names of the
database, my guess is that veritas does something strange to these databases
, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas ac
tually does. If you do find out,
I'd be happy to hear... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two
databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different.
I've included some sample lines
below. We the program gets to these two databases the process that is runnin
g changes to spid' while the
other databases are backed up by "backup". These two databases don't show up
in Enterprise Manager. Why does
the process start the database up first? Why would the database not be onlin
e already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/
10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/
06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked I
N LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked I
N LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/
10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/0
6(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/0
8/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored
procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN
LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN
LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/0
6(01:40:52),
> --
> Dan D.|||Do you have any idea what the files are for? Since a database by that name d
oesn't show up in EE, I'm wondering why the file is there?
--
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However,
considering the names of the
> database, my guess is that veritas does something strange to these databas
es, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas
actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> up - model4IDR and master4IDR - whose lines in the log files are different
. I've included some sample lines
> below. We the program gets to these two databases the process that is runn
ing changes to spid' while the
> other databases are backed up by "backup". These two databases don't show
up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be onl
ine already? And what does the "IN
> LOAD" phrase mean?
>
>|||Again, my guess is that it is Veritas that creates these files and does some
strange detach thingy. It is not
SQL Server. Profiler will tell you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't
show up in EE, I'm
wondering why the file is there?[vbcol=seagreen]
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
>
out,[vbcol=seagreen]
backed[vbcol=seagreen]
lines[vbcol=seagreen]
does[vbcol=seagreen]
"IN[vbcol=seagreen]
e two databases that are backed up - model4IDR and master4IDR - whose lines
in the log files are different. I've included some sample lines below. We t
he program gets to these tw
o databases the process that is running changes to spid' while the other da
tabases are backed up by "backup". These two databases don't show up in Ente
rprise Manager. Why does the process start the database up first? Why would
the database not be online
already? And what does the "IN LOAD" phrase mean?
Thanks,
backup Database backed up: Database: master, creation date(time): 2004/06/10
(20:01:42)
backup Database restored: Database: master4IDR, creation date(time): 2004/06
/10(20:01:
spid51 Starting up database 'master4IDR'.
spid51 Recovery is checkpointing database 'master4IDR' (15)
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN
LOAD.
spid51 Starting up database 'master4IDR'.
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN
LOAD.
spid51 Starting up database 'master4IDR'.
backup Database backed up: Database: master, creation date(time): 2004/06/10
(20:01:42)
backup Database backed up: Database: model, creation date(time): 2000/08/06(
01:40:52),
backup Database restored: Database: model4IDR, creation date(time): 2000/08/
06(01:40:5
spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored p
rocedure '
spid51 Starting up database 'model4IDR'.
spid51 Recovery is checkpointing database 'model4IDR' (15)
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN L
OAD.
spid51 Starting up database 'model4IDR'.
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN L
OAD.
spid51 Starting up database 'model4IDR'.
backup Database backed up: Database: model, creation date(time): 2000/08/06(
01:40:52),
--
Dan D.Perhaps the autoclose database option is set for these databases. However, c
onsidering the names of the
database, my guess is that veritas does something strange to these databases
, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas ac
tually does. If you do find out,
I'd be happy to hear... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two
databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different.
I've included some sample lines
below. We the program gets to these two databases the process that is runnin
g changes to spid' while the
other databases are backed up by "backup". These two databases don't show up
in Enterprise Manager. Why does
the process start the database up first? Why would the database not be onlin
e already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/
10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/
06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked I
N LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked I
N LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/
10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/0
6(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/0
8/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored
procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN
LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN
LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/0
6(01:40:52),
> --
> Dan D.|||Do you have any idea what the files are for? Since a database by that name d
oesn't show up in EE, I'm wondering why the file is there?
--
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However,
considering the names of the
> database, my guess is that veritas does something strange to these databas
es, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas
actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> up - model4IDR and master4IDR - whose lines in the log files are different
. I've included some sample lines
> below. We the program gets to these two databases the process that is runn
ing changes to spid' while the
> other databases are backed up by "backup". These two databases don't show
up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be onl
ine already? And what does the "IN
> LOAD" phrase mean?
>
>|||Again, my guess is that it is Veritas that creates these files and does some
strange detach thingy. It is not
SQL Server. Profiler will tell you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't
show up in EE, I'm
wondering why the file is there?[vbcol=seagreen]
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
>
out,[vbcol=seagreen]
backed[vbcol=seagreen]
lines[vbcol=seagreen]
does[vbcol=seagreen]
"IN[vbcol=seagreen]
database backups
We have a Veritas application that backs up our databases at night. There are two databases that are backed up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines below. We the program gets to these two databases the process that is running changes to spid' while the other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does the process start the database up first? Why would the database not be online already? And what does the "IN LOAD" phrase mean?
Thanks,
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
spid51 Starting up database 'master4IDR'.
spid51 Recovery is checkpointing database 'master4IDR' (15)
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
spid51 Starting up database 'model4IDR'.
spid51 Recovery is checkpointing database 'model4IDR' (15)
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
--
Dan D.Perhaps the autoclose database option is set for these databases. However, considering the names of the
database, my guess is that veritas does something strange to these databases, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
I'd be happy to hear... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
below. We the program gets to these two databases the process that is running changes to spid' while the
other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
the process start the database up first? Why would the database not be online already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> --
> Dan D.|||Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm wondering why the file is there?
--
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However, considering the names of the
> database, my guess is that veritas does something strange to these databases, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > We have a Veritas application that backs up our databases at night. There are two databases that are backed
> up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
> below. We the program gets to these two databases the process that is running changes to spid' while the
> other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be online already? And what does the "IN
> LOAD" phrase mean?
> >
> > Thanks,
> >
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > spid51 Starting up database 'master4IDR'.
> > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > spid51 Starting up database 'model4IDR'.
> > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > --
> > Dan D.
>
>|||Again, my guess is that it is Veritas that creates these files and does some strange detach thingy. It is not
SQL Server. Profiler will tell you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm
wondering why the file is there?
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
> > Perhaps the autoclose database option is set for these databases. However, considering the names of the
> > database, my guess is that veritas does something strange to these databases, like taking a copy of some
> > database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find
out,
> > I'd be happy to hear... :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > > We have a Veritas application that backs up our databases at night. There are two databases that are
backed
> > up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample
lines
> > below. We the program gets to these two databases the process that is running changes to spid' while the
> > other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why
does
> > the process start the database up first? Why would the database not be online already? And what does the
"IN
> > LOAD" phrase mean?
> > >
> > > Thanks,
> > >
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > --
> > > Dan D.
> >
> >
> >sql
Thanks,
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
spid51 Starting up database 'master4IDR'.
spid51 Recovery is checkpointing database 'master4IDR' (15)
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
spid51 Starting up database 'master4IDR'.
backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
spid51 Starting up database 'model4IDR'.
spid51 Recovery is checkpointing database 'model4IDR' (15)
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
spid51 Starting up database 'model4IDR'.
backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
--
Dan D.Perhaps the autoclose database option is set for these databases. However, considering the names of the
database, my guess is that veritas does something strange to these databases, like taking a copy of some
database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
I'd be happy to hear... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> We have a Veritas application that backs up our databases at night. There are two databases that are backed
up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
below. We the program gets to these two databases the process that is running changes to spid' while the
other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
the process start the database up first? Why would the database not be online already? And what does the "IN
LOAD" phrase mean?
> Thanks,
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> spid51 Starting up database 'master4IDR'.
> spid51 Recovery is checkpointing database 'master4IDR' (15)
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'master4IDR'.
> backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> spid51 Starting up database 'model4IDR'.
> spid51 Recovery is checkpointing database 'model4IDR' (15)
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> spid51 Starting up database 'model4IDR'.
> backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> --
> Dan D.|||Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm wondering why the file is there?
--
Dan D.
"Tibor Karaszi" wrote:
> Perhaps the autoclose database option is set for these databases. However, considering the names of the
> database, my guess is that veritas does something strange to these databases, like taking a copy of some
> database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find out,
> I'd be happy to hear... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > We have a Veritas application that backs up our databases at night. There are two databases that are backed
> up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample lines
> below. We the program gets to these two databases the process that is running changes to spid' while the
> other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why does
> the process start the database up first? Why would the database not be online already? And what does the "IN
> LOAD" phrase mean?
> >
> > Thanks,
> >
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > spid51 Starting up database 'master4IDR'.
> > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'master4IDR'.
> > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > spid51 Starting up database 'model4IDR'.
> > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > spid51 Starting up database 'model4IDR'.
> > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > --
> > Dan D.
>
>|||Again, my guess is that it is Veritas that creates these files and does some strange detach thingy. It is not
SQL Server. Profiler will tell you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:022F9D75-A679-4527-B6FB-DF63828DDD5B@.microsoft.com...
> Do you have any idea what the files are for? Since a database by that name doesn't show up in EE, I'm
wondering why the file is there?
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
> > Perhaps the autoclose database option is set for these databases. However, considering the names of the
> > database, my guess is that veritas does something strange to these databases, like taking a copy of some
> > database and detaching the database. I'd run profiler to see what Veritas actually does. If you do find
out,
> > I'd be happy to hear... :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:8292D621-25AB-49D5-9CF6-640749EB3568@.microsoft.com...
> > > We have a Veritas application that backs up our databases at night. There are two databases that are
backed
> > up - model4IDR and master4IDR - whose lines in the log files are different. I've included some sample
lines
> > below. We the program gets to these two databases the process that is running changes to spid' while the
> > other databases are backed up by "backup". These two databases don't show up in Enterprise Manager. Why
does
> > the process start the database up first? Why would the database not be online already? And what does the
"IN
> > LOAD" phrase mean?
> > >
> > > Thanks,
> > >
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database restored: Database: master4IDR, creation date(time): 2004/06/10(20:01:
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Recovery is checkpointing database 'master4IDR' (15)
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > spid51 Bypassing recovery for database 'master4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'master4IDR'.
> > > backup Database backed up: Database: master, creation date(time): 2004/06/10(20:01:42)
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > backup Database restored: Database: model4IDR, creation date(time): 2000/08/06(01:40:5
> > > spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure '
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Recovery is checkpointing database 'model4IDR' (15)
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > spid51 Bypassing recovery for database 'model4IDR' because it is marked IN LOAD.
> > > spid51 Starting up database 'model4IDR'.
> > > backup Database backed up: Database: model, creation date(time): 2000/08/06(01:40:52),
> > > --
> > > Dan D.
> >
> >
> >sql
Thursday, March 22, 2012
Database backup size increases-no restore
Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
Abbas
Most probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
Abbas
Most probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>
Database backup size increases-no restore
Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>
Database backup size increases-no restore
Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the tim
e. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when y
ou restored, you
probably didn't specify a FILE option, which meant you restore the first one
= the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2G
B.
> to investigate, When backup taken from enterprise manager it is 60 MB. Wha
t
> could be the rerasons?. The clients are doing hundered of inserts daily. b
ut
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the tim
e. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when y
ou restored, you
probably didn't specify a FILE option, which meant you restore the first one
= the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2G
B.
> to investigate, When backup taken from enterprise manager it is 60 MB. Wha
t
> could be the rerasons?. The clients are doing hundered of inserts daily. b
ut
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>
Wednesday, March 21, 2012
Database backup from ADP application
if it is possible, how would i go about creating routines (storedprocedures?
)
that can be invoked from a microsoft access ADP file that will backup (and
hopefully have the ability to also restore) as SQL Server database?
thanks for any and all help!
benYou would execute the TSQL BACKUP and RESTORE command from your Access appli
cation. As for how you
execute TSQL command from Access, I suggest you ask in the Access group(s).
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:3AC37D13-C777-462C-ABFD-13F82698A070@.microsoft.com...
> if it is possible, how would i go about creating routines (storedprocedure
s?)
> that can be invoked from a microsoft access ADP file that will backup (and
> hopefully have the ability to also restore) as SQL Server database?
> thanks for any and all help!
> ben
)
that can be invoked from a microsoft access ADP file that will backup (and
hopefully have the ability to also restore) as SQL Server database?
thanks for any and all help!
benYou would execute the TSQL BACKUP and RESTORE command from your Access appli
cation. As for how you
execute TSQL command from Access, I suggest you ask in the Access group(s).
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:3AC37D13-C777-462C-ABFD-13F82698A070@.microsoft.com...
> if it is possible, how would i go about creating routines (storedprocedure
s?)
> that can be invoked from a microsoft access ADP file that will backup (and
> hopefully have the ability to also restore) as SQL Server database?
> thanks for any and all help!
> ben
Thursday, March 8, 2012
Database Access Right Issues
Recently I have changed the VB Application for Authentication method
to connect to the SQL Server 7.0 database using the Windows NT
Authentication.
to connect to the SQL Server 7.0 database using the Windows NT
Authentication.
Later I had created permissions for Select, Insert, Update, Delete,
Exec everything based on the Windows NT Authentication on an Active
Directory Group based.
Now I am trying to use the application from the Windows 2000 machine.
It runs normal with the all rules applied.
When I run from a windows 98 PC. It connects to the database without
any problem. When I tried to run the transactions or reports, It
throws the error as either Permission denied or Runt-time Error '13'
Type mismatch like that.
Can anybody tell me the work around for this issue?
I doubt this is something do with OS incompatiblity to access database
tables from Windows 98. Where as it is ok with Windows 2000.
Thanks in advance
SaravanakumarYour problem is with Win98, I believe. Win98 does not use named pipes,
nor does it use Windows Authentication since it does notprovide
security for different users in the same way that Windows 2000 Pro
does.
The solution to the security aspect is to set the sqlserver up with
Mixed mode logon security, and then create, in the sqkserver, a user
name and password for the user that works from thw Win98 machine. Each
time that user attempts to access sqlserver, he will have to specify
the username and password.
You may be able to solve the named pipes problem by disabling this
protocol in the client computer with the Client Network Utility. Run
this utility from the dos command Start>Run>cliconfg.exe. Then disable
Named pipes, or alternatively define an alias for the server instance
that enables only TCP/IP.
I'm not experienced with Win98 so another reader should verify this.
Also check the SQL server 2000 Books Online (download and install from
microsoft web site). This handy help file explains authentication
methods and may also have specific recommendations for Win98.
Mark Ferguson.
Ottawa Canada
On 21 Jun 2004 22:21:28 -0700, kssaran@.hotmail.com (Saravanakumar)
wrote:
>Recently I have changed the VB Application for Authentication method
>to connect to the SQL Server 7.0 database using the Windows NT
>Authentication.
>Later I had created permissions for Select, Insert, Update, Delete,
>Exec everything based on the Windows NT Authentication on an Active
>Directory Group based.
>Now I am trying to use the application from the Windows 2000 machine.
>It runs normal with the all rules applied.
>When I run from a windows 98 PC. It connects to the database without
>any problem. When I tried to run the transactions or reports, It
>throws the error as either Permission denied or Runt-time Error '13'
>Type mismatch like that.
>Can anybody tell me the work around for this issue?
>I doubt this is something do with OS incompatiblity to access database
>tables from Windows 98. Where as it is ok with Windows 2000.
>Thanks in advance
>Saravanakumar
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/
> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/
> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access log
SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish
Database access limitation
I want to release an application that uses a SQL server database.
The access to this database should only be done and managed by my
application with an own authentication mechanism.
Nobody besides me (developer) should be able to access the database in any
way. Even the structure of the database should be invisible to anyone.
How can I reach this?
Is it necessary to add a special user to my database that is used by my
application? What about the standard databaseowner, have I to disable it or
limit its rights?
Is it necessary to add a special user to the SQL server instance or can it
be done just within my own database that is shipped with my application?
Where can I find a good tutorial for this kind of problem?
Please help.
Thanks a lot!
Hi
Have you considered using APPLICATION ROLE?
"Jochen Mller" <jochenmueller132355@.hotmail.com> wrote in message
news:er0CZcehFHA.3124@.TK2MSFTNGP12.phx.gbl...
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it
or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>
|||Jochen,
Application roles might be a way to go here, or use a standard SQL login
and don't share the password.
Bear in mind that an administrator of the machine will be able to access
SQL Server if the BUILTIN/Administrators group is a member of the
sysadmin role.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jochen Mller wrote:
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>
|||You can deny all permissions to users except exec permissions on the
stored procs for data access. Or you can use application roles.
You can't hide the data and structure from SYSADMINs or administrators
on the server. You just need to make sure that only appropriate people
have that level of access.
David Portas
SQL Server MVP
|||Thanks for your reply!
Is my conclusion right:
My customer needs to administer his SQL server (e.g. database backup).
Therefor I have to give him access also to my database. That means that at
each installation at least the administrator can see my database structure.
But how can I protect my intelectual property (database design)?
With my older ACCESS database files I made database encryption and so really
nobody besides me could see any data or structure. Is this not possible with
SQL server?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegr oups.com...
> You can deny all permissions to users except exec permissions on the
> stored procs for data access. Or you can use application roles.
> You can't hide the data and structure from SYSADMINs or administrators
> on the server. You just need to make sure that only appropriate people
> have that level of access.
> --
> David Portas
> SQL Server MVP
> --
>
|||> But how can I protect my intelectual property (database design)?
With a licence agreement.
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure.
You are mistaken. The Access encryption option is feeble. It's a
trivial task to bypass it with tools freely available online. What you
are really saying is that your customers lack the IT competence or the
incentive to bypass the "security" of your application. To manage a SQL
Server a higher level of competence is expected. The admin's job
requires that he/she can access your database so unless you want to
host the application yourself and provide all the operational support
for it you must assume that you cannot and should not prevent that
access.
David Portas
SQL Server MVP
|||Hi
For Access, there are enough tools out there that would crack the encryption
in seconds. If someone has physical access to your DB, they can look at it,
be it SQL Server or Access.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jochen Müller" wrote:
> Thanks for your reply!
> Is my conclusion right:
> My customer needs to administer his SQL server (e.g. database backup).
> Therefor I have to give him access also to my database. That means that at
> each installation at least the administrator can see my database structure.
> But how can I protect my intelectual property (database design)?
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure. Is this not possible with
> SQL server?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
> Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegr oups.com...
>
>
The access to this database should only be done and managed by my
application with an own authentication mechanism.
Nobody besides me (developer) should be able to access the database in any
way. Even the structure of the database should be invisible to anyone.
How can I reach this?
Is it necessary to add a special user to my database that is used by my
application? What about the standard databaseowner, have I to disable it or
limit its rights?
Is it necessary to add a special user to the SQL server instance or can it
be done just within my own database that is shipped with my application?
Where can I find a good tutorial for this kind of problem?
Please help.
Thanks a lot!
Hi
Have you considered using APPLICATION ROLE?
"Jochen Mller" <jochenmueller132355@.hotmail.com> wrote in message
news:er0CZcehFHA.3124@.TK2MSFTNGP12.phx.gbl...
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it
or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>
|||Jochen,
Application roles might be a way to go here, or use a standard SQL login
and don't share the password.
Bear in mind that an administrator of the machine will be able to access
SQL Server if the BUILTIN/Administrators group is a member of the
sysadmin role.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jochen Mller wrote:
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>
|||You can deny all permissions to users except exec permissions on the
stored procs for data access. Or you can use application roles.
You can't hide the data and structure from SYSADMINs or administrators
on the server. You just need to make sure that only appropriate people
have that level of access.
David Portas
SQL Server MVP
|||Thanks for your reply!
Is my conclusion right:
My customer needs to administer his SQL server (e.g. database backup).
Therefor I have to give him access also to my database. That means that at
each installation at least the administrator can see my database structure.
But how can I protect my intelectual property (database design)?
With my older ACCESS database files I made database encryption and so really
nobody besides me could see any data or structure. Is this not possible with
SQL server?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegr oups.com...
> You can deny all permissions to users except exec permissions on the
> stored procs for data access. Or you can use application roles.
> You can't hide the data and structure from SYSADMINs or administrators
> on the server. You just need to make sure that only appropriate people
> have that level of access.
> --
> David Portas
> SQL Server MVP
> --
>
|||> But how can I protect my intelectual property (database design)?
With a licence agreement.
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure.
You are mistaken. The Access encryption option is feeble. It's a
trivial task to bypass it with tools freely available online. What you
are really saying is that your customers lack the IT competence or the
incentive to bypass the "security" of your application. To manage a SQL
Server a higher level of competence is expected. The admin's job
requires that he/she can access your database so unless you want to
host the application yourself and provide all the operational support
for it you must assume that you cannot and should not prevent that
access.
David Portas
SQL Server MVP
|||Hi
For Access, there are enough tools out there that would crack the encryption
in seconds. If someone has physical access to your DB, they can look at it,
be it SQL Server or Access.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jochen Müller" wrote:
> Thanks for your reply!
> Is my conclusion right:
> My customer needs to administer his SQL server (e.g. database backup).
> Therefor I have to give him access also to my database. That means that at
> each installation at least the administrator can see my database structure.
> But how can I protect my intelectual property (database design)?
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure. Is this not possible with
> SQL server?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
> Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegr oups.com...
>
>
Database access limitation
I want to release an application that uses a SQL server database.
The access to this database should only be done and managed by my
application with an own authentication mechanism.
Nobody besides me (developer) should be able to access the database in any
way. Even the structure of the database should be invisible to anyone.
How can I reach this?
Is it necessary to add a special user to my database that is used by my
application? What about the standard databaseowner, have I to disable it or
limit its rights?
Is it necessary to add a special user to the SQL server instance or can it
be done just within my own database that is shipped with my application?
Where can I find a good tutorial for this kind of problem?
Please help.
Thanks a lot!Hi
Have you considered using APPLICATION ROLE?
"Jochen Müller" <jochenmueller132355@.hotmail.com> wrote in message
news:er0CZcehFHA.3124@.TK2MSFTNGP12.phx.gbl...
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it
or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>|||You can deny all permissions to users except exec permissions on the
stored procs for data access. Or you can use application roles.
You can't hide the data and structure from SYSADMINs or administrators
on the server. You just need to make sure that only appropriate people
have that level of access.
--
David Portas
SQL Server MVP
--|||Jochen,
Application roles might be a way to go here, or use a standard SQL login
and don't share the password.
Bear in mind that an administrator of the machine will be able to access
SQL Server if the BUILTIN/Administrators group is a member of the
sysadmin role.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jochen Müller wrote:
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>|||Thanks for your reply!
Is my conclusion right:
My customer needs to administer his SQL server (e.g. database backup).
Therefor I have to give him access also to my database. That means that at
each installation at least the administrator can see my database structure.
But how can I protect my intelectual property (database design)?
With my older ACCESS database files I made database encryption and so really
nobody besides me could see any data or structure. Is this not possible with
SQL server?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegroups.com...
> You can deny all permissions to users except exec permissions on the
> stored procs for data access. Or you can use application roles.
> You can't hide the data and structure from SYSADMINs or administrators
> on the server. You just need to make sure that only appropriate people
> have that level of access.
> --
> David Portas
> SQL Server MVP
> --
>|||> But how can I protect my intelectual property (database design)?
With a licence agreement.
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure.
You are mistaken. The Access encryption option is feeble. It's a
trivial task to bypass it with tools freely available online. What you
are really saying is that your customers lack the IT competence or the
incentive to bypass the "security" of your application. To manage a SQL
Server a higher level of competence is expected. The admin's job
requires that he/she can access your database so unless you want to
host the application yourself and provide all the operational support
for it you must assume that you cannot and should not prevent that
access.
--
David Portas
SQL Server MVP
--|||Hi
For Access, there are enough tools out there that would crack the encryption
in seconds. If someone has physical access to your DB, they can look at it,
be it SQL Server or Access.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jochen Müller" wrote:
> Thanks for your reply!
> Is my conclusion right:
> My customer needs to administer his SQL server (e.g. database backup).
> Therefor I have to give him access also to my database. That means that at
> each installation at least the administrator can see my database structure.
> But how can I protect my intelectual property (database design)?
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure. Is this not possible with
> SQL server?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
> Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegroups.com...
> > You can deny all permissions to users except exec permissions on the
> > stored procs for data access. Or you can use application roles.
> >
> > You can't hide the data and structure from SYSADMINs or administrators
> > on the server. You just need to make sure that only appropriate people
> > have that level of access.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>
>
The access to this database should only be done and managed by my
application with an own authentication mechanism.
Nobody besides me (developer) should be able to access the database in any
way. Even the structure of the database should be invisible to anyone.
How can I reach this?
Is it necessary to add a special user to my database that is used by my
application? What about the standard databaseowner, have I to disable it or
limit its rights?
Is it necessary to add a special user to the SQL server instance or can it
be done just within my own database that is shipped with my application?
Where can I find a good tutorial for this kind of problem?
Please help.
Thanks a lot!Hi
Have you considered using APPLICATION ROLE?
"Jochen Müller" <jochenmueller132355@.hotmail.com> wrote in message
news:er0CZcehFHA.3124@.TK2MSFTNGP12.phx.gbl...
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it
or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>|||You can deny all permissions to users except exec permissions on the
stored procs for data access. Or you can use application roles.
You can't hide the data and structure from SYSADMINs or administrators
on the server. You just need to make sure that only appropriate people
have that level of access.
--
David Portas
SQL Server MVP
--|||Jochen,
Application roles might be a way to go here, or use a standard SQL login
and don't share the password.
Bear in mind that an administrator of the machine will be able to access
SQL Server if the BUILTIN/Administrators group is a member of the
sysadmin role.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jochen Müller wrote:
> I want to release an application that uses a SQL server database.
> The access to this database should only be done and managed by my
> application with an own authentication mechanism.
> Nobody besides me (developer) should be able to access the database in any
> way. Even the structure of the database should be invisible to anyone.
> How can I reach this?
> Is it necessary to add a special user to my database that is used by my
> application? What about the standard databaseowner, have I to disable it or
> limit its rights?
> Is it necessary to add a special user to the SQL server instance or can it
> be done just within my own database that is shipped with my application?
> Where can I find a good tutorial for this kind of problem?
> Please help.
> Thanks a lot!
>|||Thanks for your reply!
Is my conclusion right:
My customer needs to administer his SQL server (e.g. database backup).
Therefor I have to give him access also to my database. That means that at
each installation at least the administrator can see my database structure.
But how can I protect my intelectual property (database design)?
With my older ACCESS database files I made database encryption and so really
nobody besides me could see any data or structure. Is this not possible with
SQL server?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegroups.com...
> You can deny all permissions to users except exec permissions on the
> stored procs for data access. Or you can use application roles.
> You can't hide the data and structure from SYSADMINs or administrators
> on the server. You just need to make sure that only appropriate people
> have that level of access.
> --
> David Portas
> SQL Server MVP
> --
>|||> But how can I protect my intelectual property (database design)?
With a licence agreement.
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure.
You are mistaken. The Access encryption option is feeble. It's a
trivial task to bypass it with tools freely available online. What you
are really saying is that your customers lack the IT competence or the
incentive to bypass the "security" of your application. To manage a SQL
Server a higher level of competence is expected. The admin's job
requires that he/she can access your database so unless you want to
host the application yourself and provide all the operational support
for it you must assume that you cannot and should not prevent that
access.
--
David Portas
SQL Server MVP
--|||Hi
For Access, there are enough tools out there that would crack the encryption
in seconds. If someone has physical access to your DB, they can look at it,
be it SQL Server or Access.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jochen Müller" wrote:
> Thanks for your reply!
> Is my conclusion right:
> My customer needs to administer his SQL server (e.g. database backup).
> Therefor I have to give him access also to my database. That means that at
> each installation at least the administrator can see my database structure.
> But how can I protect my intelectual property (database design)?
> With my older ACCESS database files I made database encryption and so really
> nobody besides me could see any data or structure. Is this not possible with
> SQL server?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
> Newsbeitrag news:1121067494.770514.253430@.g43g2000cwa.googlegroups.com...
> > You can deny all permissions to users except exec permissions on the
> > stored procs for data access. Or you can use application roles.
> >
> > You can't hide the data and structure from SYSADMINs or administrators
> > on the server. You just need to make sure that only appropriate people
> > have that level of access.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
>
>
Subscribe to:
Posts (Atom)