My picture album
My code library
Search my site
SQL Server Articles New
Database encryption for SQL Server and MSDE:
You are here: Home > Microsoft SQL Server FAQ > Administration
Subscribe to my newsletter:
|Want to keep in touch with the
latest in SQL Server world? Email firstname.lastname@example.org with
'subscribe' in the subject line
How to read transaction logs?
How to reset or reseed the IDENTITY
How to persist objects, permissions etc.
How to simulate a deadlock for testing
How to rename an SQL Server computer?
How to run jobs from T-SQL?
How to restore single tables from backup
in SQL Server 7.0/2000, like we did in SQL Server 6.5?
Where to get the latest MDAC from?
I forgot/lost the sa password. What to
I have only the .mdf file backup and no
SQL Server database backups. Can I get my database back
into SQL Server?
How to add a new column at a specific
position (say at the beginning of the table or after the
second column) using ALTER TABLE command?
How to change or alter a user defined
How to rename an SQL Server 2000
How to capture/redirect detailed deadlock
information into the error logs?
How to restart (auto-start) SQL Server
Agent service, when SQL Server starts on a Windows 95 or
Windows 98 computer?
I want to set my SQL Server 7.0 database
in single user mode, but if there are active connections,
sp_dboption fails with the following error. How to kill all active connections before
running sp_dboption OR how to simulate the new ALTER
DATABASE...SET SINGLE_USER ROLLBACK IMMEDIATE or ROLLBACK
AFTER syntax of SQL Server 2000?
Server: Msg 15089, Level 11, State 1,
Procedure sp_dboption, Line 400
change the 'single user' option of a database while
another user is in the database.
How to backup to and restore from network
drives, mapped drives or network shares?
How to remotely administer SQL Server?
What are the effects of switching SQL
Server from 'Mixed mode' to 'Windows only' authentication
mode? What are the steps required, to not break existing
Is there a command to list all the tables
and their associated filegroups?
How to ship the stored procedures, user
defined functions (UDFs), triggers, views of my
application, in an encrypted form to my
clients/customers? How to protect intellectual property?
How to archive data from my tables? Is there a built-in command or tool for this?
How to troubleshoot ODBC timeout expired errors experienced by applications accessing SQL Server databases?
How to restart SQL Server service automatically at regular intervals?
|How to read transaction
There is no easy way
out if you want to read the transaction logs for whatever
reason. The transaction log architecture is proprietary
to Microsoft and is not published.
However, there is an undocumented DBCC LOG command that lets you see the
records in transaction log. Here is an example:
LOG (Your_Database_Name, 2)
There is a third party tool called Log Explorer by Lumigent, that helps you read
transaction logs and do stuff like recovering data,
auditing database etc.
|How to reset or reseed
the IDENTITY column? <top>
CHECKIDENT in SQL Server Books Online.
A quick and dirty way to reset the IDENTITY column would be
to run TRUNCATE TABLE command on that
table. TRUNCATE TABLE will delete all
the rows from the table and reset the IDENTITY column.
However, you will not be able to run TRUNCATE
TABLE on a table referenced by foreign keys.
|How to persist objects,
permissions etc. in tempdb? <top>
gets recreated every time SQL Server service restarts.
So, you will end up losing whatever you store in tempdb.
Actually, it's not a good practice to store your own
objects in tempdb. But if you must have some of your
tables or stored procedures or other objects in tempdb,
consider the following two options:
a stored procedure that creates the required
objects in tempdb. Mark this stored procedure as
a startup stored procedure, so that it runs
everytime SQL Server service starts. See sp_procoption in SQL
Server Books Online.
the required objects to the model database. Since
the model database is used as a template for
creating new databases, all new databases will
inherit the objects from model database.
In Query Analyzer, run
the following statements first:
TABLE t1 (i int)
CREATE TABLE t2 (i int)
INSERT t1 SELECT 1
INSERT t2 SELECT 9
Open a new window (say Window1) in Query Analyzer, paste
the following SQL statements:
UPDATE t1 SET i = 11 WHERE i = 1
WAITFOR DELAY '00:00:20'
UPDATE t2 SET i = 99 WHERE i = 9
Open another window (say Window2) in Query Analyzer and
paste the following code:
UPDATE t2 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:20'
UPDATE t1 SET i = 11 WHERE i = 1
Now run the code from Window1, followed by Window2
simultaneously. Briefly after 20 seconds, one of the
experience a dead lock!
|How to rename an SQL
Server computer? <top>
are running SQL Server 7.0, after renaming the SQL Server
machine, the SQL Server service will fail to start, with
an error message "Your installation was corrupted or
had been tampered with. To get around this problem, you
have to rerun the SQL Server setup. Setup will prompt you
to upgrade. After doing so, the necessary SQL Server
registry entries will be reset with the new computer
name. Now you will be able to start SQL Server. After
restarting, use Query Analyzer to run the following
EXEC sp_addserver 'Your_NEW_Computer_Name', 'local'
Restart your SQL Server service. Connect using Query
Analyzer and run the following command (It should output
the new server name):
If you are running
SQL Server 2000, the new name is recognized, the next
time SQL Server service starts. You don't have to rerun
the setup. However, you have to run the sp_dropserver and sp_addserver stored procedure as shown
|How to run jobs from
Use the procedure
msdb..sp_start_job to start jobs programmatically. See
SQL Server Books Online for more information.
|How to restore single
tables from backup in SQL Server 7.0/2000, like
we did in SQL Server 6.5? <top>
for restoring individual tables from backup is
discontinued in SQL Server 7.0/2000. If you need this
functionality, here are some roundabout ways:
the complete database onto a new database with a
different name. Copy the required tables (using
T-SQL or DTS) into the actual database and drop
the new database that you just created
could place the required tables onto specific
filegroups and implement filegroup backup and
restore. But filegroup backup will not backup the
transaction log. So there is a chance of losing
some data when you restore the filegroups. See
SQL Server Books Online for more information
|Where to get the latest
MDAC from? <top>
(Microsoft Data Access Components) can be downloaded from
the Microsoft Universal Data Access site.
|I forgot/lost the sa
password. What to do? <top>
or lost your sa password? Don't worry, there is a way out
Login to the SQL Server computer as the Administrator of
that computer. Open Query Analyzer and connect to SQL
Server using Windows NT authentication. Run sp_password as show below
to reset the sa password:
@new = 'will_never_forget_again', @loginame = 'sa'
The system stored procedures sp_attach_db and sp_attach_single_file_db allow you to
attach .mdf files to SQL Server. In the absence of the
log file (.ldf), SQL Server creates a new log file.
TABLE always adds new columns at the end of
the table and will not let you add new columns at a
specific position. If you must add a column at a specific
position, use Enterprise Manager. In Enterprise Manager,
right click on the table, select 'Design Table'. Right
click on the desired location and select 'Insert Column'.
Mind you, Enterprise Manager drops and recreates the
table to add a column at a specific location. So it might
take a long time if your table is huge.
there is no easy way to alter or modify a user defined
data type. To modify a user defined data type, follow
all the tables, that are referencing this user
defined data type (UDT), using ALTER
TABLE...ALTER COLUMN command and change the data
type of the referencing column to an equivalent
(or the intended) base data type.
the user defined data type using sp_droptype.
the user defined datatype with the required
changes using sp_addtype.
use the ALTER TABLE...ALTER COLUMN syntax to
change the column's datatype to the user defined
cannot rename an instance of an SQL Server 2000. If you
must rename an instance, follow these steps:
a new SQL Server 2000 instance with the desired
your databases from the old instance to the newly
the old instance of SQL Server 2000.
capture detailed deadlock information into the error
logs, enable the trace flags 1204 and 3605 at the session
level using the DBCC TRACEON command. When
you enable these trace flags at the session level, only
those deadlocks are captured into the error log, in which
this session has participated.
To enable these trace flags at the server level, start
your SQL Server from command prompt (sqlservr.exe) with
-T1204 and -T3605 parameters. You could also set these
trace flags from Enterprise Manager. (Right click on the
server, select 'Properties'. Click on 'Startup
parameters...'. Add the parameters -T1204 and -T3605 one
after another by clicking the 'Add' button.). After
setting these trace flags in Enterprise Manager, you must
restart your SQL Server service for these trace flags to
Click here to download the
script that restarts SQL Server agent automatically when
SQL Server starts on a Windows 95 or Windows 98 computer.
This script uses an undocumented system extended stored
procedure named xp_servicecontrol, to start the SQL
Server Agent service. Complete documentation and usage
information of this script is available within the script
in the form of comments. Please note that you will have
to customize this procedure a little bit when using it
against SQL Server 2000.
to set my SQL Server 7.0 database in single user
mode, but if there are active connections,
sp_dboption fails with the following error. How
to kill all active connections before running
sp_dboption OR how to simulate the new ALTER
DATABASE...SET SINGLE_USER ROLLBACK IMMEDIATE or
ROLLBACK AFTER syntax of SQL Server 2000?
Server: Msg 15089, Level 11,
State 1, Procedure sp_dboption, Line 400
change the 'single user' option of a database
while another user is in the database. <top>
Click here to download
sp_dboption2, an enhanced version of sp_dboption, that
can be used with SQL Server 7.0 to kill all active
connections before changing the database mode. The system
stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline' modes if the database
is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can
configure it to kill the connections immediately, or
after waiting for a specified interval. This procedure
simulates the new ALTER TABLE syntax of SQL Server 2000
(the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along
with OFFLINE, ONLINE, READ_ONLY, READ_WRITE, SINGLE_USER,
Please see the comments within the stored procedure body,
for usage, examples and other information. Create this
procedure in the master database.
Server cannot read mapped drives. In your backup and
restore commands always refer to the network drive or
network share using UNC path. UNC path has the following
format: \\MachineName\ShareName or
Here is an example to backup the pubs database to a share
called 'AllBackups' on a remote machine named
DATABASE Pubs TO
To backup pubs database to a the admin share 'D$' on a
remote server named 'BackupServer':
DATABASE Pubs TO
For network backups and restores to work, make sure your
SQL Server and SQL Agent services are NOT running under
system account. These services must run using a domain
account and this domain account must have read and write
permissions on the network share or drive.
register the remote SQL Server in the Enterprise Manager
and start administering it, just as you would administer
a local server.
If the SQL Server is across a WAN/Internet, then you will
have to connect using the IP address of the SQL Server.
In that case you might have to add a TCP/IP advanced
entry on your client PC using the 'Client Network
Utility' (Start -> Programs -> Microsoft SQL Server
-> Client Network Utility). You'll find the following
INF: TCP Ports Needed for Communication
to SQL Server Through a Firewall (Q287932)
To gain complete access to the remote desktop, you will
have to use Windows Terminal Services. With a Terminal
client you can connect to a remote server, do anything
you want with it, as if you are on that server
physically. There are other third party alternatives like
PC-Anywhere, PC-Duo, RemotelyAnywhere etc.
from Mixed mode to Windows authentication is a major
conversion and requires some good planning. Here are some
steps you need to take:
the connect strings in all your applications to
connect using windows authentication.
your applications are using DSNs, you will have
to alter the same, to connect using Windows
your users should login using an NT account that
has been granted access to the SQL Server, as
well the database in question.
you have old third party applications (for which
you don't have source code, and are using
DSN-less connections), that are written to
connect using SQL Server authentication, you will
have problems, as these applications will fail to
rid of your current SQL Server logins and users
and replace them with NT logins and grant
database access to these NT logins.
you have users connecting from non-windows
platforms, they will not be able to connect using
Windows authentication. So you might want to
migrate them to Windows first.
the login information for all the replication
agents and DTS packages involved, so that they
connect using trusted connection.
Enterprise Manager, edit the server registration
properties, so that EM connects to SQL Server
using Windows authentication.
is no built-in command just to list all the tables along
with their file groups. sp_help is the closest you can
get to. The following query lists all the tables and the
filegroups those tables belong to:
OBJECT_NAME(id) [Table Name], FILEGROUP_NAME(groupid) AS
WHERE indid IN (0, 1) AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0
Server 7.0 Enterprise Manager allowed us to script
encrypted objects in encrypted form. But that feature is
not available anymore in SQL Server 2000. This means that
there is no direct way out, for shipping your database
objects in encrypted form. Here are a couple of
workarounds, that may or may not help depending on the
1: Create a template database, with encrypted
objects. Backup this database. Add this database
backup to your setup application, and restore it
at your cleints' place, from the setup program.
2: Create a template database, with encrypted
objects. Detach this database. Add this detached
database to your setup application, and attach it
to your clients' SQL Server from your setup
3: What if your application is already live
at your clients' place and you just want to ship
some updates to stored procedures? The above
methods will not work, because, restore or
'attaching a database' will overwrite the
existing database, resulting in losing all the
data. Here's how you can handle this situation:
Design your application to have two databases.
One database will contain all the tables and the
other database will contain all the stored
procedures, UDFs, triggers and views and these
objects must be coded to access tables from the
other database. Now if you want to ship updated
code, just modify the stored procedures and ship
the backup of the second database only. This way
you are not overwriting the existing data at your
You might have to create different template databases for different collations/sort orders and character sets, that your clients might be using. This isn't much of a problem in SQL Server 2000, but it sure is, in SQL Server 7.0. Well,
all this is worth the effort? May be not! Because, the
stored procedure encryption in SQL Server is not that
strong and there are tools out there, that can decrypt
the stored procedures in a second! As one of my fellow
SQL Server MVPs BP Margolin says, your intellectual
property is better protected with 'legal agreements'