Tuning SQL Server is a series of tradeoffs
Q: I received an error message that SQL Server had marked
my database suspect. What can I do?
Use emergency mode (also called bypass mode) to recover data when
SQL Server marks a database suspect. Occasionally something (hardware glitches,
software glitches, gamma rays, or whatever) causes SQL Server's ordinarily
robust automatic recovery to fail. Of course, recovery failure is never a
problem because everyone always has an accurate, up-to-date backup handy. Uh-
huh. Right. But what if (hypothetically) you don't have a good backup?
Most of your data is probably still there on disk, but you can't get to it
because SQL Server says the database is suspect. Setting the database status to
emergency mode tells SQL Server to skip automatic recovery and lets you access
the data. To get your data, use this script:
Sp_configure "allow updates", 1
Reconfigure with override
GO
Update sysdatabases set status = -32768 where name = "BadDbName"
Sp_configure "allow updates", 0
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
DUMP TRANSACTION WITH NO_LOG to extract your data while the database is in
emergency mode, but you won't be able to issue data modification commands. Get
your data out as fast as you can before anything else can go wrong. Count your
blessings if it's all there, because we can't guarantee that this technique will
work in all cases. Call Microsoft if you're still stuck. The $150 service call
is worth the money if Product Support can help you recover critical data. (See
the Microsoft Knowledge Base article Q165918--http://www.microsoft.com/kb/articles/q165/9/18.htm--for more information on this topic.)
Why might SQL Server mark your database suspect? For starters, try querying
on suspect in SQL Server Books Online (BOL) in the MS SQL
Server 6.5 Group in the Programs Start Menu. You'll be surprised how many hits
you get. Granted, some of them are for the verb suspect, but you'll have
dozens of hits on suspect as an adjective. For example, you'll find
stored procedures such as sp_resetstatus and sp_mark suspect that turn a
database's suspect flag off and on, respectively. Marking a database suspect is
often the easiest way to drop it (if you can't drop it using SQL Enterprise
Manager's Manage Databases or Server Manager windows) because you can drop
suspect databases. Use the command
DBCC DBREPAIR(database_name, dropdb)
(Remember, though, that you can never drop master, model, or tempdb
databases, or a database that's open or participating in replication.)
Also recognize that DBCC DBREPAIR is a command of last resort. Always try
DROP DATABASE or sp_db remove first. DBCC DBREPAIR is for backward compatibility
only.
Q: I distribute SQL Server databases to my customers, and I don't want
them to see the stored procedure code because it's proprietary to my business.
How can I keep them from seeing the procedures?
Before SQL Server 6.5, many people hid stored procedure code by simply
deleting the contents of the text field from the syscomments table, where SQL
Server stores SQL definition statements for view, rules, defaults, triggers,
CHECK and DEFAULT constraints--and stored procedure code. This tactic is less
feasible now, however, because SQL Server 6.5 requires data in the text column.
Now, the WITH ENCRYPTION option is probably the best way to protect your
procedures without deleting data from syscomments; you can also use WITH
ENCRYPTION to keep your views and triggers secret. Use this syntax:
CREATE PROCEDURE TestProc WITH ENCRYPTION
As
Print "this is just a test"
Go
SQL Server populates syscomments but encrypts the text so no one--not even
you or the systems administrator--can read it. This option protects your secret
information, and you'll have no problems upgrading to future versions. But if
you use WITH ENCRYPTION, remember that it really hides your information.
You can't read the procedure after you create it, so make sure you have earlier
versions of the code (or at least text files) outside of SQL Server.