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 = 1, NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 2 , NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 3, RECOVERY
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;

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

http://www.dotnetcurry.com/ShowArticle.aspx?ID=618

WebGrid WebHelper in ASP.NET MVC 3 RC

http://www.dotnetcurry.com/ShowArticle.aspx?ID=615

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>