All database backup to directory

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name


-- specify database backup directory
SET @path = 'C:\Backup\' 


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)


DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases


OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  


WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 


       FETCH NEXT FROM db_cursor INTO @name  
END  


CLOSE db_cursor  
DEALLOCATE db_cursor

Remove All stored procedure (SP) in DB use query

use <db Name>
Go

declare @procName varchar(500)
declare cur cursor

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur

Get all table structure from database

Select  t.Table_Schema,
        t.Table_Name,
        c.Column_Name,
        IsNull(c.Column_Default, '') as 'Column_Default',
        c.Is_Nullable,
        c.Data_Type,
        IsNull(c.Character_Maximum_Length, IsNull(Numeric_Precision,'') + IsNull(Numeric_Scale, IsNull(DateTime_Precision,''))) as 'Size'

From Information_Schema.Tables t

Join Information_Schema.Columns c on    t.Table_Catalog = c.Table_Catalog
                                And     t.Table_Schema = c.Table_Schema
                                And     t.Table_Name = c.Table_Name

Where t.Table_Type = 'BASE TABLE'

Order by t.Table_Schema, t.Table_Name, c.Ordinal_Position

SQL Server Services Start

1. Windows + R:  mstsc
2. Login server
3. Windows + R: services.msc
4. Select Following Services 

1. Sql Server (MSSQLSERVER)
2. SQL Server Agent (MSSQLSERVER)
3. SQL Server Analysis Services (MSSQLSERVER)
4. SQL Server Browser
5. SQL Server Interation Services 10.0
6. SQl Server Reporting Services (MSSQLSERVER)
7. SQL Server VSS Writer

 All Services one by one Right click  and Select Properties

   5. Select Logon -> Enter  Password  and Confirm Password -> Click OK

6. Right click services --> Start services

7. 5 and 6 step repeat all  (7 ) services

Comparing data type of sql server and ms access in c#

		Jet Engine (Access)	Sql-Server	C#
		Text	char, nchar, varchar, nvarchar	string
		Memo	text, ntext, the above with len>255	string
		Byte	tinyint	byte
		Integer	smallint	short
		Long Integer	integer (int)	int
		Single	real	float
		Double	float	double
		Replication ID	float	Guid
		Decimal	decimal	decimal
		Date/Time	smalldatetime, datetime, datetime2	DateTime
		Binary     (8 bytes)	timestamp, rowversion (Since V2008)	byte[]    (8 bytes)
		Currency	smallmoney, money	decimal
		AutoNumber	int + identity property	int
		Yes/No	bit	bool
		OLE Object	image	byte[]
		Hyperlink	<no equivalent>	string
		<no equivalent>	binary, varbinary	byte[]

Delete All File in Folder from "*.bak" Format File

Method 1:

DECLARE @DateString CHAR(8)

SET @DateString = '04/10/2014'

EXECUTE master.dbo.xp_delete_file 0,  N'E:\10-04-2014\',N'bak',@DateString, 1

 
Method 2:


EXEC sp_configure 'show advanced options', 1
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

EXECUTE master.dbo.xp_cmdshell  'del E:\10-04-2014\Test_db.bak'

Create Folder

EXEC master.dbo.xp_create_subdir '<path>'

Ex: EXEC master.dbo.xp_create_subdir 'E:\10-04-2014\'