Automatic SQL Server Backup

Automatic SQL Server Backup

I am running Windows Server 2008 with MS SQL Server and put togther this HOW-TO about how to backup a database automatically. I figured I would share it with you:

I am using Microsoft SQL Server 2008 Express but you should be able to use these instructions for all versions of Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

CREATE A SQL FILE and save it as "backup.sql"

declare @filepath varchar(255)
declare @filename varchar(255)
declare @file varchar(510)

set @filepath='D:\Praticle Test\'
declare @date varchar(8)
set @date=CONVERT(varchar(8), GETDATE(), 112)
set @filename= '-Backup.bak'
set @file=@filepath+ @date + @filename

Backup Database EHSolution To Disk = @file

CREATE A BATCH FILE With following content


sqlcmd -E -S pserver -i D:\backup.sql

SET BATCH FILE TO RUN IN WINDOWS TASK SCHEDULER

Start -> Run.

CODE

schtasks /create /sc Daily /st 03:30:00 /tn "MyTask" /tr "cmd /c D:\Backup.bat"

Sending HTML Mail

MailMessage mail = new MailMessage(FromEmail, Toemail);
mail.Bcc.Add(AdminEmail);

mail.Subject = "Subject goes here";

string strBody = "";
strBody = "Message Body";

mail.IsBodyHtml = true;
mail.Body = strBody;

SmtpClient client = new SmtpClient();
client.Host = SMTPServer;
client.Port =SMTPPort;

System.Net.NetworkCredential basicAuthenticationInfo = new System.Net.NetworkCredential(SMTPUser, SMTPPassword);
client.UseDefaultCredentials = false;
client.Credentials = basicAuthenticationInfo;

try
{
client.Send(mail);
}
catch (Exception ex)
{

}

How to Configure Source Control for Visual Studio 2008

When you have first installed Microsoft Visual Studio 2008 and Micrsoft Visual SourceSafe 2005, there is not initially a configured relationship between the two applications.
Source Control is not a menu item at this state until you configure the Source Control plug-in by using the Options dialog screen.

You can open the Options dialog screen by selecting Tools in the main menu then selecting Options submenu item. Drill down to the Source Control and Plug-in Selection in the options treeview.
There is a drop down list which lists available Source Control options and applications and an option for indicating not to use any of the source control software. In order to use Microsoft Visual SourceSafe 2005, you can select "Microsoft Visual SourceSafe" in the available source control plug-in combobox.

[caption id="attachment_53" align="alignnone" width="480" caption="plug-in-selection for Visual studio 2008"][/caption]

If you select Visual SourceSafe plug-in two more branches will appear in the options treeview below the Source Control, "Environment" and "Plug-in Settings".

Use APPLY

Use APPLY
The apply statement was created for the situation where you put multiple inline nested queries in the one statement. For example, take the following statement:
SELECT soh.SalesOrderID,
Quantity=(SELECT TOP 1 (Quantity)
FROM Sales.SalesOrderDetails
WHERE  SalesOrderID = soh.SalesOrderID),
UnitPrice=(SELECT TOP 1 (UnitPrice)
FROM Sales.SalesOrderDetails
WHERE  SalesOrderID = soh.SalesOrderID)
FROM Sales.SalesOrderHeader soh
This performs an extra query, retrieving data from another table using the same criterion. This can now be replaced with the following:

SELECT soh.SalesOrderID, soh.OrderDate, a.*
FROM Sales.SalesOrderHeader soh
CROSS APPLY (
SELECT TOP (1) sod.UnitPrice, sod.Quantity
FROM Sales.SalesOrderDetail sod
WHERE sod.SalesOrderId = soh.SalesOrderId
ORDER BY sod.Quantity DESC
) as a

URL Rewriting in the IIS 7.0

follow the below mention steps for URL Rewriting in the IIS 7.0.

1. Open Properties of the Virtual Directory "website1" from IIS.


2. Click on “Configuration”, from Virtual Directory Tab.


3. Click on “Insert” from Mappings Tab.


4. Add the following path,

a. “X:\WINDOWS\Microsoft.NET\Framework\v2.0.507\aspnet_isapi.dll” in Executable. (X is the Windows Drive. i.e. where the framework is installed”)
b. Uncheck the Option “Check that file Exist”.

[gallery link="file"]

URL Rewriting in the IIS 6

[gallery link="file"]

Follow the below mention steps for URL Rewriting in the IIS.

1) Open Properties of the Virtual Directory  from IIS.

2) Click on “Configuration”, from Virtual Directory Tab.

3) Click on “Add” from Mapping Tab.

4) Add the following path,

a. “X:WINDOWSMicrosoft.NETFrameworkv2.0.50727aspnet_isapi.dll”  in Executable.  (X is the Windows Drive. i.e. where the framework is installed”)

b. Type “.*” in Extension.

c. Uncheck the Option “Check that file Exist”.

Add the following code in Global.asax file

string[] arrPath;
string strPath = string.Empty;
string strUrl = string.Empty;
string strQS = string.Empty;
string strQrystr = string.Empty;

string strLocal = ConfigurationManager.AppSettings["Local"];

protected void Application_BeginRequest(object sender, EventArgs e)
{
string str1 = "UrlRequest";
string str2 = str1;

strQrystr = string.Empty;
strUrl = string.Empty;
setPath();

if (strPath.IndexOf(".") > 0)
{
return;
}
try
{
// ### Start - If main page(like http://localhost/websiteName/) then exit sub
if (strPath == "" || strPath == "/")
{
HttpContext.Current.RewritePath(strLocal + "/Default.aspx", "", "");
return;
}

strQS = Request.QueryString.ToString();

if (System.IO.File.Exists(Server.MapPath("~/" + arrPath[1] + ".aspx")))
{
strUrl = ConfigurationManager.AppSettings["Local"] + "/" + arrPath[1] + ".aspx";

if (arrPath.Length > 0)
{
for (int i = 0; i < arrPath.Length; i++)
{
if (arrPath[i] != null && arrPath[i] != "")
{
strQrystr = strQrystr + "q" + i + "=" + arrPath[i] + "&";
}
}
}
if (strQrystr.Length > 0)
{
strQrystr = strQrystr.Substring(0, strQrystr.Length - 1);
}
}
else
{
Response.Redirect(strLocal + "/Default.aspx");
}

//### Start - Actual Url Rewrite

if (strQS == "")
HttpContext.Current.RewritePath(strUrl, "", strQrystr + strQS);
else
{
if (strQrystr != "")
{
HttpContext.Current.RewritePath(strUrl, "", strQrystr + "&" + strQS);
}
else
{
HttpContext.Current.RewritePath(strUrl, "", strQS);
}
}
}
catch (IndexOutOfRangeException ex)
{
ex.Message.ToString();

}
catch (Exception ex)
{
ex.Message.ToString();
}
}
private void setPath()
{
strPath = HttpContext.Current.Request.Path.ToString().ToLower();

if (HttpContext.Current.Request.Url.AbsoluteUri.ToLower().IndexOf("localhost") != -1 || strLocal != "")
{
strPath = strPath.Replace(strLocal.ToLower() + "/", "");
}
else
{
strPath = strPath.Substring(1);
}
arrPath = strPath.Split('/');
}

Add following lines in web.config

<add key="Local" value="/VirtualdirectoryName"/>

Making Store Procedure Generic

Making Store Procedure Generic

Consider creating stored procedures in the master database.

Use the special "sp_" prefix for these procedures, and use the undocumented system stored procedure "sp_MS_marksystemobject" to mark the stored procedure as a system stored procedure.

System stored procedures, although residing in the master database, operate on the database objects from where the stored procedure is executed.

All the usual caveats about using undocumented procedures and adding stored procedures to a system database apply.

Example:
***********
CREATE PROCEDURE sp_counts AS

DECLARE @TABLENAME VARCHAR(50)
DECLARE @SQL VARCHAR(200)
create table #temp
(
tablename varchar(300),
noofrows int
)

DECLARE C1 CURSOR FOR
select table_name from information_schema.tables where table_type='base table'

OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME
WHILE @@FETCH_STATUS=0
BEGIN

SET @SQL = 'select isnull(null,'''+@TABLENAME+'''),rows from sysindexes where indid<2 and id = object_id('+ '''' + @TABLENAME +'''' +')'

insert into #temp exec(@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME
END
close C1
deallocate C1
delete from #temp where tablename = 'dtproperties'
select * from #temp order by noofrows desc
drop table #temp
GO

********
exec sp_MS_marksystemobject 'sp_counts'
********

Now from any database you can give EXEC SP_COUNTS which gives you the tablename and total rows in the table.