Configure SQL Server 2005 to allow remote connections

To configure SQL Server 2005 to allow remote connections, complete all the following steps:

• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Detailed steps here http://support.microsoft.com/kb/914277

How to rename a SQL Database

You can not rename a SQL Server database from Enterprise Manager, but you can with the sp_renamedb stored procedure. You must first set the database to singe user.

1. Set the database to Single User by right clicking the database in Enterprise Manager, select Properties, select the Options Tab, check the Restrict Access checkbox, and select Read-Only, then click OK.
2. Close Enterprise Manager (since we can only have 1 user accessing it at a time now).
3. Open Query Analyser.
4. Type: sp_renamedb ,
5. Don't forget to reset the Restrict Access settings on the database that you changed in step 2.

SQL Server replication over internet

SQL Server replication over the internet or across non-trusted domains is usually performed using a virtual private network (VPN), and consequently the configuration is much the same as that used on a LAN. The linked article below outlines what to do if such a VPN is not available.

Try the link http://www.replicationanswers.com/InternetArticle.asp

Copy MS SQL table with Data

It is always a necessity to keep original table structure an data before modifying the table structure. Easiest way is to copy the table into another table and keep the backup, which can be restored when required.

To copy MS SQL table with data use following SQL command

SELECT * INTO newtable FROM oldtable

How do you know diskspace used by tables in a SQL database?

Use the following stored procedure to view space used by every table:

exec sp_spaceused

Or you can use the undocumented foreachtable function in combination with that.


exec sp_MSforeachtable 'exec sp_spaceused ''?'';'

How to reclaim the diskspace from SQL log file (.LDF)?

To reclaim disk space from SQL log file

- Detach the database using Enterprise manager or Stored procedure.
- Delete the log file physically
- Attach the database using Enterprise Manager which will create a new log file for the database