Create database using an existing data files and no log file

If you only have a data file and do not have a log file, you can use the following command to create the database.

This  command will create a log file in the default log file location for the instance.

CREATE DATABASE "DB_NAME" ON (FILENAME = 'FILE_LOCATION') FOR ATTACH_REBUILD_LOG

  

Query to get the List of all Foreign Keys in a database

Please change the name of the database and run it.

USE "database name"

go

SELECT

      OBJECT_NAME(constraint_object_id) FOREIGN_KEY_NAME,

      OBJECT_NAME(parent_object_id)     TABLE_NAME,

      (SELECT name FROM sys.columns WHERE object_id = parent_object_id AND column_id = parent_column_id) COLUMN_NAME,

      OBJECT_NAME(referenced_object_id) REFERENCED_TABLE_NAME,

      (SELECT name FROM sys.columns WHERE object_id = referenced_object_id AND referenced_column_id = column_id) REFERENCED_COLUMN_NAME

FROM sys.foreign_key_columns

ORDER BY REFERENCED_TABLE_NAME

go

Changing SQL Server Instance Name after renaming the physical machine or virtual machine

After a long timeout, I have decided to register something that I learned today.

Here is the scenario.

There is a named SQL Server instance running on a virtual machine. An Image of the entire machine was taken and restored with a different virtual machine name. (This was done as an exercise to create a new staging environment as a look a like copy of the production environment).

After the new virtual machine was created, there is a  named SQL Server instance is now running on the new virtual machine. But the @@SERVERNAME property is still displaying the old machine name.

Here is what you do to change the @@SERVERNAME property.

sp_dropserver  'OLD_HOST_NAME\INSTANCE_NAME'

sp_addserver @server = 'NEW_HOST_NAME\INSTANCE_NAME', @local = 'local'

Restart the SQL Server Instance using SQL Server configuration Manager.

Now execute SELECT @@SERVERNAME or 
                    SELECT SERVERPROPERTY('SERVERNAME')

You will notice that the new host name is displayed correctly.