How to know when the database is last restored

 SELECT [rs].[destination_database_name], 
[rs].[restore_date], 
[bs].[backup_start_date], 
[bs].[backup_finish_date], 
[bs].[database_name] as [source_database_name], 
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf 
ON [bs].[media_set_id] = [bmf].[media_set_id] 
ORDER BY [rs].[restore_date] DESC
 

Learning resources

C# Fundamentals: Development for Absolute Beginners

 

http://channel9.msdn.com/Series/C-Sharp-Fundamentals-Development-for-Absolute-Beginners 

 

Check the videos in the given order. A good location to start learning C#.Net

The Windows 8* & Ultrabook™ App Innovation Contest

Visit the Original Link for any updates below.

This is posted for information sharing purpose only.

http://www.codeproject.com/Competitions/598/Windows-8-Ultrabook-Application-and-Article-Contes.aspx

 

Get your Ultrabook enabled Windows 8 app in the Intel AppUp^® store for your chance at one of 300 Ultrabooks and a cash prize pool of $140,000.

300 Ultrabooks, fifty $1,000 cash prizes, seven $10,000 cash prizes and a grand prize of $20,000.

Round 1 Deadline is October 24, 2012

The goal of this competition is to get your app into the Intel AppUp online store in time for Christmas.

• 300 round 1 winners will each be sent an Ultrabook, for free, to be used to test and fine-tune your app
• The first 50 successful round 2 entrants will receive a $1,000 prize for getting their app in the AppUp store
• All round 2 entrants are in the running for 1 of seven $10,000 cash prizes
• The final winner gets the $20,000 grand prize
• Every single entrant also goes in the fast lane for the app approval process.

That's a total prize package for the big winner of $31,000, plus additional promotion and visibility for you and your app, plus you are sent an Ultrabook!

What is the Challenge?

To write an Ultrabook enabled Windows 8 application and have it posted on the Intel AppUp store by December 1, 2012.

What sort of applications are acceptable?

Almost anything. This is an Ultrabook competition so you need to ensure your application takes advantage of the new Ultrabook capabilities. If it's a Windows application then it will work, obviously, and if you accept cursor input then the touchscreen input will work out of the box, too. But we want more:

• Make your app touch aware, not just touch enabled
• or maybe integrate location data
• or include smart connect support – have your apps respond to messages when the Ultrabook is sleeping
• or maybe just go crazy with the sensors – tilt it, shake it, lift it or spin your Ultrabook around and have your app know what's going on. Great for game developers or those who just want to make a fun widget.

The more you show off what an Ultrabook can do, the more you demonstrate that an Ultrabook isn't your Dad's old, tired PC, the better your chances. Make your existing app engaging on an ultra-light, ultra-portable Ultrabook.

Read the Ultrabook Overview for Software Developers for more info on what an Ultrabook running Windows 8 can do.

Judging will be divided into 7 categories, with each category winner being awarded a $10,000 prize.

Windows 8 ("desktop") apps

• Gaming
• Entertainment
• Productivity
• Retail
• Healthcare
• Education

Windows 8 Store ("metro") apps

• Total Innovation (i.e. show off what an Ultrabook can do no matter what category)

How do I enter?

First,Register!It takes 2 seconds and allows us to plan the rollout of the Ultrabooks. We're on a tight timeline so need to be organised.

Second, write your app and make your way through Round 1 and 2:

Round 1: Deadline is October 24, 2012

Write up your application and post an article in our Ultrabook section under "App Innovation Contest". Your article can either include your application (with or without source), or a write up of your app (throw in some code snippets if you want the votes!) or, if you're working on an existing commercial app that's a little hush-hush, an explanation of your app that will allow us to judge whether or not you'll be likely to make it through round 2.
As soon as each submission is posted we will review it and start sending out the Ultrabooks to those judged as being likely to be accepted into the AppUp store. We'll also review whether we feel it's possible for you to complete the app (assuming you submit a snippet or just an explanation).
So: Get your round 1 submission in as fast as you can! We only have 300 Ultrabooks to send out, and we expect they will go quickly. The sooner you make your submission, the sooner you can get the Ultrabook and start final testing. Your time is limited, so aim to get your app complete and in the AppUp store rather than adding just one more feature and missing out completely.

The deadline for round 1 entries is October 24, 2012

Round 2: Deadline is November 21, 2012

Winners from Round 1 who received an Ultrabook will now be one of a maximum of 300 contestants. Applications will need to be completed and tested using the Ultrabook they received in order to ensure the application works perfectly on the hardware, and then contestants must submit their final application to Intel AppUp for verification and publishing. Contestants must provide proof of acceptance to be eligible to earn a Category Prize, an Application Success Award, and/or the Grand Prize by emailing us at contest@codeproject.com.

If you don't submit your app to the AppUp store in time, and/or it's not approved, you won't be eligible for final judging. Check the submission requirements and give yourself plenty of time to get it in. We're working with Intel to have your apps go through the fast lane, but we can't offer miracles for late entries. The deadline for round 2 entries is November 21, 2012

FAQ

How do I write an Ultrabook app without an Ultrabook?

Initially you would use the Windows Simulator, but after you've submitted your app or your write-up, you'll be eligible to receive an Ultrabook. An actual real, live, Ultrabook will be delivered to you for free. Use the Ultrabook to do final testing and polishing and then submit your app to theAppUp store.

What if I miss a deadline?

Contestants who do not receive an Ultrabook in Round 1, or who do not meet the Round 1 deadline, are still encouraged to develop applications, submit them to Intel AppUp and submit articles to CodeProject for judging. At CodeProject's discretion and assuming any of the 300 remain, additional Ultrabooks may be awarded to Round 2 Contestants meriting recognition. Round 2 Contestants will be eligible to receive an Application Success Award on providing proof that (i) they have submitted their application to Intel AppUp and that (ii) it has been accepted to be included in the AppUp.

Where can I find the Windows Simulator?

The Windows Simulator is included in the freeVisual Studio Express 2012 for Windows 8. The simulator is accessed by setting the debug target to "Simulator". Start debugging and the simulator pops up and allows you to interact with the application as if you were on a touch- and sensor-enabled device.

Can you throw me some links on getting started?

Sure

• Visual Studio Express 2012 for Windows 8- Tools for Windows 8 development
• Ultrabook Overview for Software Developers
• Ultrabook™ Community on the Intel® Developer Zone
• Intel AppUp®
• Ultrabook and Tablet Windows 8* Sensors Development Guide
• Ultrabook and Windows 8 Support Forum

Can anyone enter?

As with any competition of this nature there are some restrictions. Employees at Intel, Microsoft, and CodeProject cannot enter, and laws in certain jurisdictions also restrict entrants. Please see the Terms and Conditions for a full list. Specifically, since a major component of this competition revolves around entrants receiving an Ultrabook, we've had to restrict entrants to the following countries: United States, Canada, India, Spain, Ireland, United Kingdom, Switzerland, Germany, Belgium, Denmark, Norway or Sweden. Yes, it sucks we can't open it to everyone because of various laws in different countries, but we had to make a call on whether it's better to offer it to a lot, but not all, of you, or none at all.

What Ultrabook will winners of Round 1 receive?

The Ultrabooks that are made available as part of this contest are the same units reviewed in our Ultrabook section. It's a preview 3rd generation "Ivy Bridge" based Ultrabook referred to as a Software Development Platform. That is, this is a unit that you'll never see on store shelves – it's a "no-one-else-in-your-street-will-have-one" Ultrabook. However, since it's not a commercial product these units will be sent to winners on a 3 year term.
Conditions of Entry | Current winners list

How to Shrink SQL DATABASE

Change the Database_name to the database to be shrink.
Execute the Select statement and use the file_id in the DBCC SHRINKFILE COMMAND.


USE <>

SELECT File_id, name FROM Sys.DATABASE_Files; DBCC SHRINKFILE(<>, TRUNCATEONLY)

How to drop database when used by other users.

When ever we are droping or deleting the databse if it is used by other users. Sql server displays the following error message.

"cannot drop database "Database_Name" because it is currently in use"

To avoid this error message and close all the existing connections and drop immedeiately you need to use the following syntax.

Change the Database_Name with your Database name to drop.


IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Database_Name')
BEGIN
PRINT 'DROP DATABASE [Database_Name']'
ALTER DATABASE [Database_Name'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Database_Name']
END
GO

All SQL Server Session information in a SQLServer

How to get all session id's information which are connected to the database



select * from sysprocesses

How to display System database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid =1

How to display User Created Database names in Sql Server

SELECT name , database_id FROM sys.databases WHERE owner_sid <> 1

How to restore master (system) database

• How to restore master database backup.
o Stop the Sql Server
o Start the sql server in Single user mode using the command sqlservr.exe – m from command prompt.
o Restore the master database

• How to restore master database when the master database is corrupted.
o Execute the following command by placing setup disk with the following options by navigating the folder where setup.exe is present.
start /wait \setup.exe /qn
INSTANCENAME= REINSTALL=SQL_Engine
REBUILDDATABASE=1 SAPWD=
o After executing the above command, stop the sql server.
o Start the SQL Server in single user mode using the command sqlservr.exe –m
o Restore the master database from the backup.

SQL SERVER 2012 -- IIF function

msdn link
IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.

In earlier versions of SQL Server, we have used IF..ELSE and CASE..ENDCASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE

In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value.

DECLARE @A INT=40
DECLARE @B INT=30
SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A')
GO;

Executing the above T-SQL will return the following result:
-------------------
A IS GREATER THAN B (1 row(s) affected)

In this example, we will evaluate the age of John and Julie and identify who is older between them. Please observe the use of sub functions within IIF.
DECLARE @JOHN_AGE INT=35
DECLARE @JULIE_AGE INT=29
SELECT IIF(@JOHN_AGE > @JULIE_AGE
            , 'JOHN IS OLDER THAN JULIE BY '+LTRIM(STR(@JOHN_AGE-@JULIE_AGE))+' YEARS'
, 'JULIE IS OLDER THAN JOHN BY '+LTRIM(STR(@JULIE_AGE-@JOHN_AGE))+' YEARS')
GO;

Executing the above statement will return the following result:
--------------------------------------------
JOHN IS OLDER THAN JULIE BY 6 YEARS (1 row(s) affected)

Note: Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels.

How to know installed Sql Server instances on a server

We can install multiple instances of Sql Server on a given server.
How to know what are the sqlserver instances present on the server.

Open the Run command and type Regedit and then press enter.
go to the following location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
one entry is present for each instance.

Another way is from the services and find the services as below.
Open the Run command and type services.msc and then press enter.
It will open the Services window and then look for the following
"Sql Server (Instance Name)".

How to open Run command in Windows 8 Developer Preview

You can open the Run command in Windows 8 Developer Preview by clicking on Windows key in the keyboard and then pressing "R" key.

How to enable .Net 3.5 in Windows 8 machine

Use the following command to enable .net 3.5 in Windows 8 developer machine.
1. Open the windows exploer
2. From the file menu open the command prompt in administrator mode
3. Execute the following command

Where "f" is the drive name for Windows 8 developer OS dvd

dism.exe /online /enable-feature /featurename:NetFX3 /Source:f:\sources\sxs /limitaccess

How to enable CLR features in SQL Server

To enable CLR feature set the value to 1.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


To disable CLR feature set the value to 0.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO


How to change table level compression in SQL Server

If we want to remove table level(ROW/PAGE) compression.
You can use the following syntax.

ALTER TABLE Table_Name
REBUILD PARTITION = ALL
WITH( DATA_COMPRESSION = NONE)

Replace "Table_Name" with actual table name before executing the script.

To learn more about Table compression use the following MSDN link Table Compression

How to know the space used by each table in SQL SERVER and generate summary report

I want to know the following things.

How many rows are present in each table in a database
What is the index size of each table
What is the data size of each table.
Total how many rows are present in all the tables in a database.
Total hom much space is used by data and index by all tables in a database.

In SQL Server sp_spaceused stored procedure is used to know the size and rows count for a single table. If you want to know the summary of all tables you need to use a cursor to loop all the tables.

We can use sp_MSforeachtable stored procedure to execute a command. But this will give you resultset for each execution.

Combined these two commands in the following way you can get a summary of each table in the current database.

This will help us in understanding Production data sizes and plan for the capacity planning.


/*Declare temp table to hold the data*/
DECLARE @t TABLE(Name NVARCHAR(MAX),
Rows BIGINT,
Reserved NVARCHAR(MAX),
Data NVARCHAR(MAX),
Index_Size NVARCHAR(MAX),
Unused NVARCHAR(MAX))

/*Insert the data output from sp_spaceused command to temp table */
insert into @t
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

/*Display the raw data for all the tables*/
select * from @t order by name

/*Remove the 'KB' present in the data*/
update @t
Set reserved = RTRIM((REPLACE(reserved,'KB','')))
,data = RTRIM((REPLACE(DATA,'KB','')))
,index_size = RTRIM((REPLACE(index_size,'KB','')))
,unused = RTRIM((REPLACE(unused,'KB','')))

/* Get the total of all tables data to know the summary*/
SELECT 'Total' AS TOTAL
, SUM(rows) AS 'Rows'
, SUM(CONVERT(BIGINT, reserved)) AS 'Reserved (KB)'
, SUM(CONVERT(BIGINT, data)) AS 'Data (KB)'
, SUM(CONVERT(BIGINT, index_size)) AS 'Index Size (KB)'
, SUM(CONVERT(BIGINT, unused)) AS 'Unused (KB)'
FROM @t


Free SQL training by Microsoft on Feb 25 2012 (Saturday) in Redmond WA USA

Microsoft is conducting free SQL & Data warehouse training in Redmond for one day.

You can register this event online at the following location

SQL Saturday

Event Date: 25th February 2012 Saturday

Location: 15255 NE 40th Street, North Commons, Redmond, WA 98052

For Topics and Schedule check the link Schedule

For future events and locations, check the following link

Future Events

Creating Bing Maps Developer Account

Go to Bing Maps Portal Site and click on Create link.
bingmapsportal

Click on "Create or View Keys" after creating/login to bingmapsportal site.

Provide the application name and the url.

Use the generated Credentials in developing the applications.

For additional resources, you can check the following link

Bing Maps

Microsoft SQL Server 2012 RC0 released

SQL Server 2012 is a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization and quickly build solutions to extend data across on-premises and public cloud backed by mission critical confidence.

You can download SQL SERVER 2012 RC0 from the following link.

Download Sql server 2012 RC0




Developer Tools are available at the following location.

Developer Tools