Tuesday, May 31, 2011

How to get version info from SQL backup (.bak) file


Get version info from SQL backup
use this little code to find the .bak file information
USE NIRDB
RESTORE HEADERONLY
FROM
DISK = N'D:\VSS\NIR.bak'WITH NOUNLOAD;
 Version number to SQL Server version, here's two:
611 = SQL Server 2005
655 = SQL Server 2008

RESTORE HEADERONLY - contains a list of backup header information for a backup device
RESTORE FILELISTONLY - contains a list of the data and log files contained in the backup
RESTORE LABELONLY - contains information about the backup media
Following are sample outputs from each of the commands.  These first three outputs show you the
complete output from running the commands against a backup file containing one full backup of the
AdventureWorks database.  As you can see there is a lot more information in the files that may or
may not be useful to you.
RESTORE HEADERONLY FROM DISK='C:\Backup\Adv_Full.bak'
BackupName AdventureWorks-Full Database Backup
BackupDescription NULL
BackupType 1
ExpirationDate NULL
Compressed 0
Position 1
DeviceType 2
UserName EDGENB2\Sysadmin
ServerName EDGENB2\TEST1
DatabaseName AdventureWorks
DatabaseVersion 611
DatabaseCreationDate 38985.72449
BackupSize 173091840
FirstLSN 41000000054400000
LastLSN 41000000056800000
CheckpointLSN 41000000054400000
DatabaseBackupLSN 41000000041600000
BackupStartDate 1/3/2007 8:15:41 PM
BackupFinishDate 1/3/2007 8:15:41 PM
SortOrder 52
CodePage 0
UnicodeLocaleId 1033
UnicodeComparisonStyle 196609
CompatibilityLevel 90
SoftwareVendorId 4608
SoftwareVersionMajor 9
SoftwareVersionMinor 0
SoftwareVersionBuild 1399
MachineName EDGENB2
Flags 512
BindingID 5956B629-86DF-4000-BAC0-52194A773D3B
RecoveryForkID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
Collation SQL_Latin1_General_CP1_CI_AS
FamilyGUID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
HasBulkLoggedData 0
IsSnapshot 0
IsReadOnly 0
IsSingleUser 0
HasBackupChecksums 0
IsDamaged 0
BeginsLogChain 0
HasIncompleteMetaData 0
IsForceOffline 0
IsCopyOnly 0
FirstRecoveryForkID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
ForkPointLSN NULL
RecoveryModel FULL
DifferentialBaseLSN NULL
DifferentialBaseGUID NULL
BackupTypeDescription Database
BackupSetGUID 1389292F-F593-425D-BD36-325FCEA0E02A
RESTORE FILELISTONLY FROM DISK='C:\Backup\Adv_Full.bak'
LogicalName AdventureWorks_Data AdventureWorks_Log
PhysicalName C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Data.mdf C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Log.ldf
Type D L
FileGroupName PRIMARY NULL
Size 188678144 2097152
MaxSize 35184372080640 2199023255552
FileId 1 2
CreateLSN 0 0
DropLSN 0 0
UniqueId 94EDC99D-D0E0-4146-95DA-1756D6C92348 EB9DB2B3-BE70-4F76-8345-7FF07FB705C7
ReadOnlyLSN 0 0
ReadWriteLSN 0 0
BackupSizeInBytes 172163072 0
SourceBlockSize 512 512
FileGroupId 1 0
LogGroupGUID NULL NULL
DifferentialBaseLSN 41000000041600000 0
DifferentialBaseGUID 6493F201-EBBA-47DD-BBDA-83A2772A8DA3 00000000-0000-0000-0000-000000000000
IsReadOnly 0 0
IsPresent 1 1
RESTORE LABELONLY FROM DISK='C:\Backup\Adv_Full.bak'
MediaName NULL
MediaSetId 23979995-927B-4FEB-9B5E-8CF18356AB39
FamilyCount 1
FamilySequenceNumber 1
MediaFamilyId 86C7DF2E-0000-0000-0000-000000000000
MediaSequenceNumber 1
MediaLabelPresent 0
MediaDescription NULL
SoftwareName Microsoft SQL Server
SoftwareVendorId 4608
MediaDate 1/3/07 8:15 PM
MirrorCount 1
If we have a backup file that contains multiple backups, using the HEADERONLY option shows us
the information for each of the backups.  Following is a condensed view of the RESTORE
HEADERONLY output.  As you can see there are three backups in this file; one full backup and two
transaction log backups.  This information can be determined by the BackupType.
BackupName AdventureWorks-Full Database Backup AdventureWorks-Transaction Log Backup AdventureWorks-Transaction Log Backup
BackupDescription NULL NULL NULL
BackupType 1 2 2
Position 1 2 3
BackupSize 173091840 74752 8192
FirstLSN 41000000054400000 41000000054400000 41000000059200000
LastLSN 41000000056800000 41000000059200000 41000000059200000
CheckpointLSN 41000000054400000 41000000054400000 41000000054400000
DatabaseBackupLSN 41000000041600000 41000000054400000 41000000054400000
BackupStartDate 1/3/07 8:15 PM 1/3/07 8:39 PM 1/3/07 8:40 PM
BackupFinishDate 1/3/07 8:15 PM 1/3/07 8:39 PM 1/3/07 8:40 PM
BackupTypeDescription Database Transaction Log Transaction Log
BackupSetGUID 1389292F-F593-425D-BD36-325FCEA0E02A 1DAB6FAA-14AD-4C3C-8081-6A15CB170782 285DC2A1-1E89-44A5-B9ED-373821C94054
So how does this information help you restore your databases?
When your backup files contain multiple backups in one file you need to specify the position of the file
that you are restoring.  This option for the RESTORE command is FILE, but this number corresponds
to the Position value.  So if we want to restore these files using the RESTORE command we would
issue the following three commands one for each of the backups using the value that is in the Position
from the HEADERONLY output..
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 1NORECOVERY

RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = NORECOVERY

RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 3RECOVERY

In addition to being able to restore multiple backups from one backup file, we can also use the output
from the FILELISTONLY to determine where the default locations will be for the data and log files. 
If you take a look at the output above from the FILELISTONLY command and look at the values in
the LogicalName and PhysicalName you will see the directory where the database was stored was
in the "C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\" directory.  If you
just do a RESTORE the data and log files will be created in this directory.  If the directory does not
exist or if you want to specify another directory or file name you need to use the WITH MOVE option
of the RESTORE command.  This can be done as follows:
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 
1
RECOVERY

MOVE 
'AdventureWorks_Data' TO 'J:\SQLdata\AdventureWorks_Data.mdf'
MOVE 
'AdventureWorks_Log' TO 'X:\SQLlog\AdventureWorks_Log.ldf'

it's a nice artical of MSSQL- tips, thanks for giving such a valuable information.

How to map an existing database user to a SQL Server login

A. Showing a report of the current user to login mappings

The following example produces a report of the users in the current database and their
security identifiers (SIDs).

EXEC sp_change_users_login 'Report';

B. Mapping a database user to a new SQL Server login

In the following example, a database user is associated with a new SQL Server login.
Database user MB-Sales, which at first is mapped to another login, is remapped to login MaryB.

--Create the new login.
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database user MB-Sales to login MaryB.
USE AdventureWorks2008R2;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
 

C. Automatically mapping a user to a login, creating a new login if it is required

The following example shows how to use Auto_Fix to map an existing user to a login of the same name,
or to create the SQL Server login Mary that has the password B3r12-3x$098f6 if the login Mary does
not exist.

USE AdventureWorks2008R2;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO

Management Studio template
EXECUTE [master].[dbo].[sp_change_users_login]
@Action = '<Action,varchar(10),Update_One>',
@UserNamePattern = N'<UserName,sysname,>',
@LoginName = N'<LoginName,sysname,>',
@Password = NULL;

 

Friday, May 27, 2011

Double to Current format with the currency Symbol

CultureInfo cultureInfo = new CultureInfo("en-US");
cultureInfo
.NumberFormat.CurrencySymbol = "Rs.";
Thread.CurrentThread.CurrentCulture = cultureInfo;
decimal devimalValue = 3.45M;
this.Text = devimalValue.ToString("C2");

Get the sub-domain from the provided URL

///Get the sub-domain from the provided URL
/// </summary>
/// <param name="url">URL to retrieve sub-domain from</param>
/// <returns></returns>
public string RetrieveSubDomain(Uri url)
{
string subDomain = "";
if (url.HostNameType == UriHostNameType.Dns && 
               (!(url.HostNameType ==
UriHostNameType.Unknown)))
{
string host = url.Host;
if (host.StartsWith("www."))
{
host = host.Remove(0, 4);
}

int length = host.Split('.').Length;if (length > 2)
{
int last = host.LastIndexOf(".");
int idx = host.LastIndexOf(".", last - 1);
subDomain = host.Substring(0, idx);
}
}
return subDomain;
}

protected void btnFindSubDomain_Click(object sender, EventArgs e)
{
string subdomain = RetrieveSubDomain(new Uri(txtUrl.Text));
Response.Write(subdomain);
}

WebGrid in MVC 3

Efficient Paging with WebGrid Web Helper - ASP.NET MVC 3 RC
WebGrid WebHelper in ASP.NET MVC 3 RC

Thursday, May 26, 2011

Grid Control in MVC 3.0

It is the code of the StoreController which display List of albums in the Genre.
To Display these items in the grid pass the to the view as list of items type of
Album. Here i am using Linq to SQL for getting data from the tables.

//
// GET: /Store/Browse?genre=Disco

public ActionResult Browse(string genre)
{
//var genreModel = new Genre { Name = genre };
//var albums = from m in storeDB.Albums
// where m.Genre.Name == genre
// select m;

var albums = from m in storeDB.Albums
            
join v in storeDB.Genres
            
on m.GenreId equals v.GenreId
             
where v.Name == genre
            
select m;

var albumList = albums.ToList<Album>();
//return view with list of albums to the View.
return View(albumList);
}

It is the Code at the View of this Method. where i have specified the
Model type in the firld line and after that created the grid and
assigned the data ( list of albums) the grid.
then the html is fetched at the page to display on the view..

@model IEnumerable< MvcMusicStore.Album>
@{
ViewBag.Title =
"Browse";
var data = Model;
var grid = new WebGrid(data);
}
<h2>Browsing Genre: @Model.Count()</h2>
<
div>
@grid.GetHtml()
</div>
@*<ul>
@foreach (var album in Model)
{
//<li><a href="/Store/Browse?genre=@genre.Name">@genre.Name</a></li>
//<li>@Html.ActionLink(genre.Name,"Browse",new {genre=genre.Name})</li>
<li>@album.Title</li>
}
</ul>
*@

 

JOIN two tables using LINQ to SQL

// GET: /Store/Browse?genre=Disco

public ActionResult Browse(string genre)
{
//var genreModel = new Genre { Name = genre };
//var albums = from m in storeDB.Albums
// where m.Genre.Name == genre
// select m;

var albums = from m in storeDB.Albums
join v in storeDB.Genres
on m.GenreId equals v.GenreId
where v.Name == genre
select m;

var albumList = albums.ToList<Album>();

return View(albumList);

}

///Acess this list at View of MVC Application
@model IEnumerable< MvcMusicStore.Album>
@{
ViewBag.Title =
"Browse";
}

<h2>Browsing Genre: @Model.Count()</h2>
<
ul>
@foreach (var album in Model)
{

//<li><a href="/Store/Browse?genre=@genre.Name">@genre.Name</a></li>
//<li>@Html.ActionLink(genre.Name,"Browse",new {genre=genre.Name})</li>
<li>@album.Title</li>
}
</ul>

 

Entity Framework Feature CTP4: DbContext & Databases

This post is largely targeted at using the Code First approach with DbContext to generate a
database, if you are mapping to an existing database then this is covered at the end of the post.

Default Conventions

First let's look at the default behavior of DbContext and how it uses convention rather than
configuration to reduce the amount of code we need to write to get an application up and
running. Below is a complete application that uses DbContext to persist and query data
using DbContext. No additional code or configuration is required; DbContext will automatically
create a database for us based on our domain model. The database will be created on our
localhost\SQLEXPRESS instance and will be named after the fully qualified type name of
our derived context (in the following example this would be PI.DbDemo.ProductCatalog).

using System;
using System.Collections.Generic;
using System.Data.Entity;

namespace PI.DbDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new ProductCatalog())
            {
                // Persist Data
                var food = new Category { Name = "Food" };
                context.Categories.Add(food);
                context.SaveChanges();

                // Query Data
                foreach (var cat in context.Categories)
                {
                    System.Console.WriteLine(cat.Name);
                }
            }

            Console.WriteLine("Press any key to exit.");
            Console.ReadKey();
        }
    }

    public class ProductCatalog : DbContext
    {
        public DbSet<Category> Categories { get; set; }
        public DbSet<Product> Products { get; set; }
    }

    public class Category
    {
        public int CategoryId { get; set; }
        public string Name { get; set; }

        public ICollection Products { get; set; }
    }

    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public decimal UnitPrice { get; set; }

        public Category Category { get; set; }
    }
}

Of course this convention is useful to get up and running but it's not going to get us all the
way to deploying in a production environment, you probably aren't going to be using a local
SQL Express instance in production and if you're an enterprise developer your DBA probably
isn't going to overjoyed at the idea of you application having permissions to create databases
(with good reason). In the next sections we'll look at how you can start to take control over
the database as your requirements progress.

Connection Factories

Under the covers there is a convention that is taking the name of your context and turning it
into a database connection, this is an AppDomain wide setting that can be changed via a
static property; System.Data.Entity.Infrastructure.Database.DefaultConnectionFactory.
Connection factories implement the System.Data.Entity.Infrastructure.IDbConnectionFactory
interface which defines a single CreateConnection method. When you use the default
constructor on DbContext the fully qualified name of your context is passed to the default
connection factory to obtain a database connection.

public interface IDbConnectionFactory
{
    DbConnection CreateConnection(string nameOrConnectionString);
}

Changing the Database Name

If you just want to change the name of the database that is generated then you can control
the string that is passed to the default connection factory by using the DbContext constructor
that specifies the nameOrConnectionString parameter. Here is our derived context updated to
specify a database name:

public class ProductCatalog : DbContext
{
    public ProductCatalog()
        :base("DemoProductStore")
    { }

    public DbSet<Category> Categories { get; set; }
    public DbSet<Product> Products { get; set; }
}

Changing the Database Server

If you want to have your database on another Microsoft SQL Server Instance then you can
tweak the settings on the SQL Client connection factory that is included in CTP4;
System.Data.Entity.Infrastructure.SqlConnectionFactory. This connection factory includes
a constructor that allows us to override pieces of the final connection sting, such as
username, password and server. We need to make changes to the default convention before
any contexts are created in our AppDomain, in the case of our console application we can
just do this at the start of the Main method:

static void Main(string[] args)
{
    Database.DefaultConnectionFactory =
         new SqlConnectionFactory("Server=MyDatabaseServer");
 
   ...

}

Changing to SQL Compact

Along with the SQL Client connection factory we also include the
System.Data.Entity.Infrastructure.SqlCeConnectionFactory which will generate connections to
SQL Compact databases. Because the SQL Compact providers aren't backwards compatible
you will need to specify the invariant name of the provider version you want to use. Currently
the SQL Compact 4.0 provider is the only one that supports Code First database creation
and it is available for download as a separate CTP.

static void Main(string[] args)
{
    Database.DefaultConnectionFactory =
         new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");

    ...

}

By default SQL Compact files will be created in the |DataDirectory| directory, for executables this
is the same directory as the executable, for web applications this is the 'App_Data' directory.
The SQL Compact factory also includes constructors to override the directory that databases are
created in, or any other part of the connection string.

App.config/Web.config

All these conventions are great but if our database server changes between dev, test and production
then we really want to be able to change it easily without having to recompile code. No problem, just
add a connection string to your applications config file with a name that matches the name of your
context (either fully-qualified or not). Because the shape of the model comes from your code rather
than xml files this is just a plain connection string rather than an EntityClient connection string used
in other areas of EF.

<configuration>
  <connectionStrings>
    <add name ="ProductCatalog"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=ProductCatalog;
           Integrated Security=True" />
  </connectionStrings>
</configuration>

Note that if you pass a string to a DbContext constructor (for the nameOrConnectionString parameter)
then that string should match the name of the connection string added to your config file.

 
Read Complete Article from MSDN Blog..  really a nice article..
http://blogs.msdn.com/b/adonet/archive/2010/09/02/ef-feature-ctp4-dbcontext-and-databases.aspx
 

Wednesday, May 25, 2011

Performing a Bulk Copy Operation in a Transaction

Using External Transactions with SqlBulkCopy

Performing Atomic SqlBulkCopy Imports

To perform atomic SqlBulkCopy imports that span across all batches (and, optionally, across
other database statements) we need to use transactions. The following steps outline the
process of using a transaction with SqlBulkCopy:
  1. Create a SqlConnection to the destination database server.
  2. Open the connection.
  3. Create a SqlTransaction object.
  4. Create the SqlBulkCopy object passing in the SqlTransaction object into the constructor.
  5. Perform the import - the call to WriteToServer - within a Try...Catch block.
  6. If the operation completes, commit the transaction; if it fails, roll it back.

' STEP 1 - Create a SqlConnection to the destination database server
Using destinationConnection As New SqlConnection(ConfigurationManager
.ConnectionStrings("InventoryDBConnectionString").ConnectionString)
   ' STEP 2 - Open the connection.
   destinationConnection.Open()

   ' STEP 3 - Create a SqlTransaction object.
   Dim myTransaction As SqlTransaction = 
                               destinationConnection.BeginTransaction()

   ' STEP 4 - Create the SqlBulkCopy object passing in the 
                             SqlTransaction object into the constructor.

   Using bulkCopy As New SqlBulkCopy(destinationConnection, 
                              SqlBulkCopyOptions.Default, myTransaction)
      bulkCopy.DestinationTableName = "InventoryItems"

      'Define column mappings
      For Each col As DataColumn In excelData.Columns
         bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName)
      Next

      'With external transactions, the entire operation occurs under 
       the umbrella of one transaction,
      'regardless of the BatchSize value
      bulkCopy.BatchSize = 100


      ' STEP 5 - Perform the import - the call to WriteToServer - within 
                 a Try...Catch block.

      Try
         bulkCopy.WriteToServer(excelData)

         ' STEP 6a - Success! Commit transaction
         myTransaction.Commit()

         Display some sort of success message...

      Catch ex As Exception
         ' STEP 6b - Failure - rollback the transaction
         myTransaction.Rollback()

         Display some sort of error message...
      End Try
   End Using

   destinationConnection.Close()
End Using

If you had other statements that needed to be performed under the umbrella of this
transaction you would execute them within the Try...Catch block.

Read / Write XML in Memory Stream

Use the XmlWriter to write XML to Memory Stream:
MemoryStream stream = new MemoryStream();
XmlWriter writer = XmlWriter.Create(stream);
http://msdn2.microsoft.com/en-us/library/ms162617.aspx 

or you can use the XmlDocument to save xml document to specific stream:
MemoryStream stream = new MemoryStream();
XmlDocument xDocument = new XmlDocument();
xDocument.Save(stream);
http://msdn2.microsoft.com/en-us/library/aa335927(vs.71).aspx

Read the xml document from tream:
MemoryStream stream = new MemoryStream();
XmlDocument xDocument = new XmlDocument();
xDocument.Load(stream);
http://msdn2.microsoft.com/en-us/library/aa335923(VS.71).aspx

Remember to add the following using statements:
using System.IO;
using System.Xml;

Example:
MemoryStream unzippedFileStream = WCFService1.Classes.Zip.Decompress(zippedFile);
unzippedFileStream.Position = 0;
XmlDocument xDocument = new XmlDocument();
if (unzippedFileStream != null)
{
xDocument.Load(unzippedFileStream);
}

Save a file into MemoryStream and access the contents of MemoryStream

using System;
using System.IO;

namespace nsStreams
{
    public class MemStrm
    {
        const string USA = "[USA]";
        const string PhoneEntry = "Phone_number=";
        static public void Main ()
        {
            FileStream cfg;
            try
            {
                cfg = new FileStream ("./config.ini",
                                      FileMode.Open,
                                      FileAccess.ReadWrite);
            }
            catch (FileNotFoundException)
            {
                Console.WriteLine ("Cannot find ./config.ini");
                return;
            }
            catch (Exception e)
            {
                Console.WriteLine (e.Message);
                Console.WriteLine ("Cannot find ./config.ini");
                return;
            }
            MemoryStream mem = new MemoryStream ((intcfg.Length);
            cfg.Read (mem.GetBuffer()0(intcfg.Length);
            int pos = FindInBuffer (USA, 0, mem.GetBuffer());
            if (pos < 0)
            {
                Console.WriteLine ("Could not find match in buffer");
            }
            else
            {
                pos = FindInBuffer (PhoneEntry, pos, mem.GetBuffer());
                if (pos < 0)
                {
                    Console.WriteLine ("Could not find phone number");
                }
                else
                {
                    const string NewPhone = "1888555-9876";
                    mem.Seek (pos + PhoneEntry.Length, SeekOrigin.Begin);
                    for (int x = 0; x < NewPhone.Length; ++x)
                    {
                          mem.WriteByte ((byteNewPhone[x]);
                    }
                    cfg.SetLength (0);
                    cfg.Write (mem.GetBuffer()0,
                              (intmem.GetBuffer().Length);
                }
            }
            cfg.Flush ();
            cfg.Close ();
            mem.Close ();
        }
//
// Find a string of characters in a buffer of type byte
        static int FindInBuffer (string ToFind, int start, byte [] buf)
        {
            for (int x = start; x < buf.Length; ++x)
            {
                if (buf[x== (byteToFind[0])
                {
                    int y;
                    for (y = 1; y < ToFind.Length; ++y)
                    {
                        if ((x + y>= buf.Length)
                            break;
                        if (buf[x + y!= (byteToFind[y])
                            break;
                    }
                    if (y == ToFind.Length)
                    {
                        return (x);
                    }
                }
            }
            return (-1);
        }
//
// Convert a buffer of type string to byte
        static void StringToByte (out byte [] b, string str)
        {
            b = new byte [str.Length];
            for (int x = 0; x < str.Length; ++x)
            {
                b[x(bytestr [x];
            }
        }
//
// Convert a buffer of type byte to a string
        static string ByteToString (byte [] b, int start)
        {
            string str = "";
            for (int x = start; x < b.Length; ++x)
            {
                str += (char[x];
            }
            return (str);
        }
    }
}

//File: config.ini
/*
[PROGRAM]
Program Directory=C:\TEMP\
Data Directory=

[DEFAULTS]
Phone_number=800-555-2345
Wallpaper=wallppr.bmp
sshow=default

[Modem]
Initialization String=ATX4L1
Dial Type=1

[Countries]
1=USA
2=Canada
3=United Kingdom

[USA]
Phone_number=1800555-1234
TOLLFREE=1

[Canada]
Phone_number=1800555-2345
TOLLFREE=1

[United Kingdom]
Phone_number=08009872345
TOLLFREE=1

*/