Archive for the ‘MS SQL’ Category

How to enable remote connections to MSSQL 2005 database

MSSQL

 

Open SQL server configuration Manager from start => all programs =>Microsoft SQl server 2005=>Configuration tools =>SQL server configuration manager , click protocols for MSSQL server . The protocols Shared Memory , Named pipes and TCP/IP should be enabled .
Right Click on TCP/IP protocols , Click the IP addresses Tab , the IP address should be active , enabled and the TCP port should be 1433 . Also the loopback address 127.0.0.1 should be active enabled and the tcp port should be 1433 .

Expand SQL native Client Configuration , Click client protocols Named Pipes , TCP/IP and Shared Memory protocols Should be enabled .

start => all programs =>Microsoft SQl server 2005=>Configuration tools = > SQL server 2005 Surface area Configuration => Surface area configuration for services and connections and for connections select Local and remote connections .

 

MSSQL 2005 connection string

Here is the MSSQL connection string to connect to MSSQL database to be used in the web.config file

<add name="example" connectionString="Data Source=IP or hostname of database server\SQLEXPRESS;
Initial Catalog=databasename;User ID=databaseuser;Password=#userpassword"
providerName="System.Data.SqlClient" />

Script to take the backup Of MS SQL databases

1) First Create a file Backup_Databases.sql .  Edit the file and type the following code

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
SET @BackupDirectory = ‘D:\Backup\DailyMSSQLbackup\MSSQLBackups\’
–Add a list of all databases you don’t want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> ‘tempdb’ AND name <> ‘model’ AND name <> ‘Northwind’ AND

name <> ‘master’
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

BEGIN
SET @Name = @DB + ‘( Daily BACKUP )’
SET @MediaName = @DB + ‘_Dump’ + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
SET @BackupFile = @BackupDirectory + + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
SET @Description = ‘Normal’ + ‘ BACKUP at ‘ + CONVERT(varchar, CURRENT_TIMESTAMP) + ‘.’

IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = ‘master’
BEGIN
SET @BackupFile = @BackupDirectory + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
–SET some more pretty stuff for sql server.
SET @Description = ‘Full’ + ‘ BACKUP at ‘ + CONVERT(varchar, CURRENT_TIMESTAMP) + ‘.’
END
ELSE
BEGIN
SET @BackupFile = @BackupDirectory + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
–SET some more pretty stuff for sql server.
SET @Description = ‘Full’ + ‘ BACKUP at ‘ + CONVERT(varchar, CURRENT_TIMESTAMP) + ‘.’
END
BACKUP DATABASE @DB TO DISK = @BackupFile
WITH NAME = @Name, DESCRIPTION = @Description ,
MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
STATS = 10
FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

 

2) Create a .bat file  say backup.bat and add the following code

sqlcmd -S . -i “C:\Backup_Databases.sql

 

You can now set the task from task scheduler to run the batch file as per your requirement

 

 

(adsbygoogle = window.adsbygoogle || []).push({}); //--> Feedjit Flag Counter