Set Primary Key and Foreign Key to Table using SQL Query

Primary Key:

ALTER TABLE Table_name ADD PRIMARY KEY (SID);

Foreign Key:

ALTER TABLE table_name
ADD CONSTRAINT FK_table_name1_Id FOREIGN KEY (tablename1Id) REFERENCES table_name1(Id)

Backup Database using C#.net

private void button1_Click(object sender, EventArgs e)
{
BackUpDatabase("testdb", "D:\\1.bak");
}
public void BackUpDatabase(string DBname, string setBakUpPath)
{
SqlConnection sCon = new SqlConnection("Data Source=.; Initial Catalog=testdb; Integrated Security=true;");
SqlCommand sCom = new SqlCommand("BACKUP DATABASE " + DBname + " TO DISK = '" + setBakUpPath + "' WITH NOFORMAT, NOINIT, NAME = 'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10", sCon);
sCom.CommandType = CommandType.Text;
try
{
sCon.Open();
sCom.ExecuteNonQuery();
MessageBox.Show("Database " + DBname + " : BackUp Done!", DBname + " BackUp", MessageBoxButtons.OK, MessageBoxIcon.Information);
sCon.Close();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Backup ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
sCom.Dispose();
}

Backup Database SQL SERVER 2005 Query

BACKUP DATABASE DBname
TO DISK = 'setBakUpPath' WITH NOFORMAT, NOINIT,
NAME = 'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10


NOINIT

Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied. NOINIT is the default.

NOSKIP

Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. This is the default behavior.

SKIP

Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets. For information about the interactions between { INIT | NOINIT } and { NOSKIP | SKIP }, see "Remarks," later in this topic.

REWIND

Specifies that SQL Server will release and rewind the tape. REWIND is the default.

NOREWIND

Specifies that SQL Server will keep the tape open after the backup operation. You can use this option to help improve performance when performing multiple backup operations to a tape.

NOREWIND implies NOUNLOAD, and these options are incompatible within a single BACKUP statement.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the backup is finished. UNLOAD is the default when a session begins.

NOUNLOAD

Specifies that after the BACKUP operation the tape will remain loaded on the tape drive.

STATS [ = percentage ]

Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.

Get all SP and Tables and Views from the Database

To get All Stored Procedures:

use [database_name]
SELECT * FROM sys.procedures;

To get All Tables:

select * from Database_name.information_schema.tables

To get All Views:

select * from Database_name.information_schema.views

Create Role and Assign the user to the role

To Create a Role:

Create Role [role_name]

Responsibilities to the Role:

Grant Execute on sp_name to [role_name]
Grant select,insert,update,delete on table_name to role_name


Assign the user to the role:

exec sp_addrolemember N'role_name', N'login_name'

Create Login and user in SQL Server 2005

To Create a Login :

CREATE LOGIN [username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[TestDB],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF


To Create a user:

CREATE USER [username] FOR LOGIN [login_name]