Questions, Answers, and Tips About SQL Server

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.

   Previous  [1]  2  3  Next