Microsoft SQL Server Administration FAQ
Last updated: May 7th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

Database encryption for SQL Server and MSDE:
ActiveCrypt.com

You are here: Home > Microsoft SQL Server FAQ > Administration FAQ

How to read transaction logs?

How to reset or reseed the IDENTITY column?

How to persist objects, permissions etc. in tempdb?

How to simulate a deadlock for testing purposes?

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 do?

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 data type?

How to rename an SQL Server 2000 instance?

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
Cannot 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 applications?

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?

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:

DBCC 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.

See DBCC 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.

Tempdb 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:

• Create 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.
  
• Add 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:

CREATE 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:

BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
WAITFOR DELAY '00:00:20'
UPDATE t2 SET i = 99 WHERE i = 9
COMMIT

Open another window (say Window2) in Query Analyzer and paste the following code:

BEGIN TRAN
UPDATE t2 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:20'
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT

Now run the code from Window1, followed by Window2 simultaneously. Briefly after 20 seconds, one of the windows will 
experience a dead lock!

If you 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 commands:

EXEC sp_dropserver 'Your_OLD_Computer_Name'
GO

EXEC sp_addserver 'Your_NEW_Computer_Name', 'local'
GO

Restart your SQL Server service. Connect using Query Analyzer and run the following command (It should output the new server name):

SELECT @@SERVERNAME
GO

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 above.

Use the procedure msdb..sp_start_job to start jobs programmatically. See SQL Server Books Online for more information.

Support for restoring individual tables from backup  is discontinued in SQL Server 7.0/2000. If you need this functionality, here are some roundabout ways:

• Restore 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
• You 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

MDAC (Microsoft Data Access Components) can be downloaded from the Microsoft Universal Data Access site.

Forgot 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:

sp_password @new = 'will_never_forget_again', @loginame = 'sa'

Yes. 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.

ALTER 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.

Unfortunately, there is no easy way to alter or modify a user defined data type. To modify a user defined data type, follow these steps:

• Alter 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.
  
• Drop the user defined data type using sp_droptype.
  
• Recreate the user defined datatype with the required changes using sp_addtype.
  
• Again, use the ALTER TABLE...ALTER COLUMN syntax to change the column's datatype to the user defined data type.

You cannot rename an instance of an SQL Server 2000. If you must rename an instance, follow these steps:

• Install a new SQL Server 2000 instance with the desired name.
  
• Move your databases from the old instance to the newly created instance.
  
• Uninstall the old instance of SQL Server 2000.

To 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 take effect.

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.

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, RESTRICTED_USER, MULTI_USER).

Please see the comments within the stored procedure body, for usage, examples and other information. Create this procedure in the master database.

SQL 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 \\MachineName\DriveLetter$\Path

Here is an example to backup the pubs database to a share called 'AllBackups' on a remote machine named 'BackupServer':

BACKUP DATABASE Pubs TO DISK='\\BackupServer\AllBackups\Pubs.BAK'

To backup pubs database to a the admin share 'D$' on a remote server named 'BackupServer':

BACKUP DATABASE Pubs TO DISK='\\BackupServer\D$\MSSQL7\BACKUP\Pubs.BAK'

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.

Just 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 article helpful:

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.

Switching from Mixed mode to Windows authentication is a major conversion and requires some good planning. Here are some steps you need to take:

• Change the connect strings in all your applications to connect using windows authentication.
• If your applications are using DSNs, you will have to alter the same, to connect using Windows authentication.
• All your users should login using an NT account that has been granted access to the SQL Server, as well the database in question.
• If 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 connect.
• Get rid of your current SQL Server logins and users and replace them with NT logins and grant database access to these NT logins.
• If 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.
• Update the login information for all the replication agents and DTS packages involved, so that they connect using trusted connection.
• In Enterprise Manager, edit the server registration properties, so that EM connects to SQL Server using Windows authentication.
  

There 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:

SELECT OBJECT_NAME(id) [Table Name], FILEGROUP_NAME(groupid) AS [Filegroup Name]
FROM sysindexes
WHERE indid IN (0, 1) AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0

SQL 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 situation:

• Method 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.
  
  
• Method 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 program.
  
  
• Method 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 cleints' end.

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'