SQL FAQ

How to rename an SQL Server computer?  If you are running SQL Server 7.0, after renaming the SQL Server machine, the SQL Server service will...

How to rename an SQL Server computer?

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.

I forgot/lost the sa password. What to do?

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'

I have only the .mdf file backup and no SQL Server database backups. Can I get my database back into SQL Server?

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.

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?

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.

How to change or alter a user defined data type?

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.

How to rename an SQL Server 2000 instance?

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.

How to backup to and restore from network drives, mapped drives or network shares?

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.

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?

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.