Copying a SQL Server Database to Another Environment

A couple of weeks ago I was troubleshooting a performance problem with the variations feature in MOSS 2007 and I needed to copy the content database to another environment for further analysis and testing. An easy (an unobtrusive) way to "snapshot" a database and copy it to another environment is to create a backup with the COPY_ONLY option:
BACKUP DATABASE [WSS_Content]
TO DISK = N'H:\WSS_Content.bak'
WITH NOFORMAT, NOINIT
, NAME = N'WSS_Content-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
, COPY_ONLY
From SQL Server 2005 Books Online:
Taking a backup normally changes the database, in turn affecting other backups and how they are restored. Sometimes, however, a backup must be taken for a special purpose that should not affect the overall backup and restore procedures for the database.

A data backup is normally a base backup for one or more differential backups taken after it. Microsoft SQL Server 2005 introduces support for creating copy-only backups, which do not affect the normal sequence of backups. Therefore, unlike other backups, a copy-only backup does not impact the overall backup and restore procedures for the database.
In other words, by using the COPY_ONLY option I avoided screwing up the scheduled differential backups on the database.
However, there are a couple of issues with this approach:
You cannot specify the COPY_ONLY option through the UI in SQL Server Management Studio, but this is no big deal -- you can start by configuring most of the backup options using the UI, script the action to generate the corresponding SQL, and then add the COPY_ONLY option as shown above
You cannot restore a backup created using the COPY_ONLY option through the UI in SQL Server Management Studio; in the Restore Database dialog, when you select the From device option and then specify the backup file previously created with the COPY_ONLY option, no backup sets are displayed
The second problem was puzzling to me. After specifying my backup file, when I attempted to change to the Options page, I encountered the following error:
You must select a restore source.
When I first encountered this problem, I thought I had a corrupt backup file. However, by once again reverting to SQL instead of the UI, I was able to verify the backup was, in fact, valid:
RESTORE FILELISTONLY
FROM DISK = N'E:\NotBackedUp\Temp\WSS_Content.bak'
To restore from a COPY_ONLY backup, use a command similar to the following:
RESTORE DATABASE [WSS_Content_TEST]
FROM DISK = N'E:\NotBackedUp\Temp\WSS_Content.bak'
WITH FILE = 1
, MOVE N'WSS_Content'
TO N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST.MDF'
, MOVE N'WSS_Content_Log'
TO N'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\WSS_Content_TEST_Log.LDF'
, NOUNLOAD, STATS = 10

Upload multiple files in HotMail style

Inside class declaration :
static public ArrayList hif = new ArrayList();
public int filesUploaded = 0;

if vb.net
Shared hif_array As New ArrayList

Note : Shared keyword plays a vital role (it allows us to keep the arraylist in memory. And Arraylist helps us to store set of objects.

Add Button
Private Sub Addfile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Addfile.Click
If (Page.IsPostBack = True) Then
'checks valid report type and uploads into temprory folder of server
strFileName = Upload1.PostedFile.FileName
FILE_EXTENSION = System.IO.Path.GetExtension(strFileName)
If FILE_EXTENSION = ".doc" Or FILE_EXTENSION = ".ppt" Or FILE_EXTENSION = ".xls" Or FILE_EXTENSION = ".htm" Or FILE_EXTENSION = ".html" Or FILE_EXTENSION = ".mdi" Or FILE_EXTENSION = ".pdf" Then
hif_array.Add(Upload1)
ListBox1.Items.Add(Upload1.PostedFile.FileName)
Else
LBL_Err.Text = "File you have uploaded is not permitted in opsreview. Upload a valid opsreview report"
End If
End If
LBL_Err.Text = hif_array.Count()
End Sub

Remove Button Private Sub RemFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemFile.Click
If (ListBox1.Items.Count <> 0) Then
hif_array.RemoveAt(ListBox1.SelectedIndex)
ListBox1.Items.Remove(ListBox1.SelectedItem.Text)
End If
End Sub

Upload Button
public void Upload_ServerClick(object sender, System.EventArgs e)
{
string baseLocation = "C:\\temp\\";
string status = "";

if((ListBox1.Items.Count == 0) && (filesUploaded == 0))
{
Label1.Text = "Error - a file name must be specified.";
return;
}
else
{
foreach(System.Web.UI.HtmlControls.HtmlInputFile HIF in hif)
{
try
{
string fn = System.IO.Path.GetFileName(HIF.PostedFile.FileName);
HIF.PostedFile.SaveAs(baseLocation + fn);
filesUploaded++;
status += fn + "
";
}
catch(Exception err)
{
Label1.Text = "Error saving file " + baseLocation + "
"
+ err.ToString();
}
}

if(filesUploaded == hif.Count)
{
Label1.Text = "These " + filesUploaded + " file(s) were uploaded:
"
+ status;
}
hif.Clear();
ListBox1.Items.Clear();
}
}

Upload multiple files in HotMail style

Inside class declaration :
static public ArrayList hif = new ArrayList();
public int filesUploaded = 0;

if vb.net
Shared hif_array As New ArrayList

Note : Shared keyword plays a vital role (it allows us to keep the arraylist in memory. And Arraylist helps us to store set of objects.

Add Button
Private Sub Addfile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Addfile.Click
If (Page.IsPostBack = True) Then
'checks valid report type and uploads into temprory folder of server
strFileName = Upload1.PostedFile.FileName
FILE_EXTENSION = System.IO.Path.GetExtension(strFileName)
If FILE_EXTENSION = ".doc" Or FILE_EXTENSION = ".ppt" Or FILE_EXTENSION = ".xls" Or FILE_EXTENSION = ".htm" Or FILE_EXTENSION = ".html" Or FILE_EXTENSION = ".mdi" Or FILE_EXTENSION = ".pdf" Then
hif_array.Add(Upload1)
ListBox1.Items.Add(Upload1.PostedFile.FileName)
Else
LBL_Err.Text = "File you have uploaded is not permitted in opsreview. Upload a valid opsreview report"
End If
End If
LBL_Err.Text = hif_array.Count()
End Sub

Remove Button
Private Sub RemFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RemFile.Click
If (ListBox1.Items.Count <> 0) Then
hif_array.RemoveAt(ListBox1.SelectedIndex)
ListBox1.Items.Remove(ListBox1.SelectedItem.Text)
End If
End Sub

Upload Button
public void Upload_ServerClick(object sender, System.EventArgs e)
{
string baseLocation = "C:\\temp\\";
string status = "";

if((ListBox1.Items.Count == 0) && (filesUploaded == 0))
{
Label1.Text = "Error - a file name must be specified.";
return;
}
else
{
foreach(System.Web.UI.HtmlControls.HtmlInputFile HIF in hif)
{
try
{
string fn = System.IO.Path.GetFileName(HIF.PostedFile.FileName);
HIF.PostedFile.SaveAs(baseLocation + fn);
filesUploaded++;
status += fn + "
";
}
catch(Exception err)
{
Label1.Text = "Error saving file " + baseLocation + "
"
+ err.ToString();
}
}

if(filesUploaded == hif.Count)
{
Label1.Text = "These " + filesUploaded + " file(s) were uploaded:
"
+ status;
}
hif.Clear();
ListBox1.Items.Clear();
}
}

Send Email thru SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_sendMail]
(
@chMailFrom VARCHAR(100), @chMailTo VARCHAR(100), @chMailSubject VARCHAR(500),
@chMessage VARCHAR(8000), @chMailCC VARCHAR(1000)=NULL, @chMailBCC VARCHAR(1000)=NULL,
@chAttachedFile VARCHAR(1000)=NULL, @chAttachFile VARCHAR(1000)=NULL, @chAttachFile3 VARCHAR(1000)=NULL)
AS
DECLARE @Object INT, @HResult INT, @varerror INT
EXEC @HResult = dbo.sp_OACreate 'CDONTS.NEWMAIL',@Object OUT
EXEC @HResult = sp_OASetProperty @Object, 'BodyFormat', 0
EXEC @HResult = sp_OASetProperty @Object, 'MailFormat', 0
EXEC @HResult = sp_OASetProperty @Object, 'From', @chMailFrom
EXEC @HResult = sp_OASetProperty @Object, 'To', @chMailTo
EXEC @HResult = sp_OASetProperty @Object, 'Subject', @chMailSubject
EXEC @HResult = sp_OASetProperty @Object, 'body', @chMessage
IF REPLACE(isNull(@chMailCC,''),' ','') <> ''
EXEC @HResult = sp_OASetProperty @Object, 'CC', @chMailCC
IF REPLACE(isNull(@chMailBCC,''),' ','') <> ''
EXEC @HResult= sp_OASetProperty @Object, 'BCC', @chMailBCC
IF REPLACE(isNull(@chAttachedFile,''),' ','') <> ''
EXEC @HResult = sp_OAMethod @object, 'AttachFile', NULL, @chAttachedFile
IF REPLACE(isNull(@chAttachFile,''),' ','') <> ''
EXEC @HResult = sp_OAMethod @object, 'AttachFile', NULL, @chAttachFile
IF REPLACE(isNull(@chAttachFile3,''),' ','') <> ''
EXEC @HResult = sp_OAMethod @object, 'AttachFile', NULL, @chAttachFile3
EXEC @HResult = sp_OAMethod @Object, 'Send'
-- SELECT @varerror = @@error
-- IF @varerror != 0
-- PRINT 'E R R O R W H I L E S E N D I N G M A I L'
EXEC @HResult = sp_OAdestroy @Object

Visual Studio .NET

1. What is Visual Studio .NET 2005?
The release of Visual Studio 2005 and the .NET Framework 2.0 will make strides in all dimensions of application development. First, Visual Studio 2005 will set a new bar for developer productivity by tailoring the software development experience to the needs of the individual developer. This "personalized productivity" will deliver features across the development environment and .NET Framework class libraries to help developers overcome their most pressing challenges in minimal time. Second, Visual Studio 2005 will enable developers to apply existing skills across a broader range of application development scenarios through improved integration with the Microsoft Office System and SQL Server 2005. Finally, Visual Studio 2005 will deliver a new set of tools and functionality that will help satisfy the application development needs of today's large-scale enterprises.
2. what are the Basic of Visual Studio .NEt 2005 ?
o Edit and Continue:
Visual Basic has always been about Rapid Application Development (RAD). One key feature is the ability to fix runtime errors on the fly. With Visual Basic .NET 1.0 and Visual Basic .NET 1.1, this powerful feature wasn't included. This feature is on-board for Whidbey. If you run into an exception at runtime, you get an exception helper that provides tips for fixing common errors, but more importantly, you can edit the code, select F5, and it continues right where you left off. Edit and Continue is VB .NET only feature
o ClickOnce
ClickOnce make it easy to install applications and provide ongoing updates (Self-Updating), rather than forcing to distribute new versions of application, can just deploy the portion of the application which has changed. In the .NET Framework 1.0 and 1.1, href-exes were not able to solve many deployment issues. Href-exes are also known as no-touch deployment, or zero impact deployment. Essentially, with versions 1.0/1.1, you can deploy an application to a Web server, allowing users to browse to the URL for the exe.When the user clicks the link, the application downloads to their Internet files cache and runs. To keep this from being a huge security hole, the application permissions are restricted based on the URL (Intranet applications get different permissions than Internet applications, for example), or other factors. This means that some applications no longer need to be deployed in the traditional sense; no more setup.exe or MSI
o SmartTags
This provides access to information and common tasks without forcing you to constantly navigate away from your work area
o Code Snippets
With code snippets, you can insert generic "template" code that requires only that you fill in the blanks. You can access code snippets by right-clicking in the code editor and navigating in the context menu to Insert Snippets.
3. What is Refactoring?
Making changes to your code like, "pulling a large stretch of inline code into its own method" or "converting a field to be a property." The Refactoring support makes this easy to do The key tenet of Extreme Programming created by Kent Beck is constant Refactoring. Under this programming model, you are developing code rapidly and iteratively, but to keep your code from becoming a jumbled mess, you must constantly Refactor. Refactoring is a C# only feature
o Extract Method
This is to split a method into many fine grained methods which are reusable.
o Rename
This is used to rename an identifier i.e. field, variable, method etc
o Encapsulate Fields
Creating a property to encapsulate a field
o Extract Interfaces
Creating an interface which is implemented by current class.
o Add Using Unbound Types
Automatic inclusion of namespace for an unbound type.
4. What are Visual studio 2005 Solution and Project related Features?
o Test Projects
This option is very useful for experimenting etc. User can just create a temporary project and start coding without saving it.
o Simplified Build
This option provides build option similar to Visual Basic 6.0. This is a useful option for VB programmers.
o Stand-alone projects
This is applicable for solution which has only one project. User will not see the solution in the Solution Explorer as well as the commands that act on solutions. However the solution files are still created.
o Project Properties
This is the central location for all project properties and settings. This provides improved access to resources and strong-name signing within the IDE.
o Visual Studio Conversion Wizard
Using this option, developers can convert their projects in Visual Studio .NET 2002 or Visual Studio .NET 2003 to Visual Studio 2005. While converting, this provides an option to create a backup of the solution or project.
o Projects and Solutions options
This feature contains separate pages for General and Build and Run options.
o Custom Project and Project Item Templates
This feature allows creation or modification of custom template for projects or project items. These custom templates appear in the New Project dialog box and Add New Item dialog box. These templates contain a project file, code files and an XML file containing metadata for the template, which is used by Visual Studio to define how to display and create the project in the development environment. These files are compressed in a .zip file so that it can easily be shared with others.
5.What are Visual Studio Project Building Features?
o Building VS 2005 introduces a new xml based transparent build system for all managed client application called MS.Net Build Engine (MSBuild). Developers can specify the as what to build and how to build under different platforms and configurations in the XML file associated with the build engine. Also they can author re-usable rules to achieve consistent builds across projects. The MSBuild build process is defined by atomic units of build operation (tasks) which can be re-authored by the developers in any .Net languages. However MSBuild cannot be used to build Web projects, deployment projects, and Visual C++ projects.
6. What are Visual Studio Debugging Features?
o Edit and Continue
This feature is only supported in VB.Net. This allows the developer to edit the code while debugging the application (Like in Visual Basic).
o Visualizers
This is a very useful feature. It can be launched from a Watch window or from the new enhanced DataTips and enables viewing of data in an intuitive and natural format i.e. now a string can be viewed as an HTML or an XML document. Also developers can write their own visualizers.
o Tracepoints and Improved Breakpoint UI
Tracepoints are a new way of using breakpoints to perform a custom action i.e. displaying a messagebox or executing a Visual Studio automation macro to determine whether to break or continue when it hits a tracepoint. The user interface is also enhanced to set these breakpoints easier and faster.
o Better Tools for Multiprocess Debugging
Now developers can see all processes attached to for debugging in the Processes window. Breakpoint can be attached to a specified processes, threads, and machines. Attach to Process dialog box is simplified and attached processes information is moved from the dialog box to the Processes window.

.NET Remoting

1. What is .NET Remoting?
.NET Remoting is an enabler for application communication. It is a generic system for different applications to use to communicate with one another. .NET objects are exposed to remote processes, thus allowing interprocess communication.
2. What’s a Windows process?
It’s an application that’s running and had been allocated memory.
3. What are the consideration in deciding to use .NET Remoting or ASP.NET Web Services?
Remoting is a more efficient communication exchange when you can control both ends of the application involved in the communication process. Web Services provide an open-protocol-based exchange of informaion. Web Services are best when you need to communicate with an external organization or another (non-.NET) technology.
4. What are channels in .NET Remoting?
Channels represent the objects that transfer the other serialized objects from one application domain to another and from one computer to another, as well as one process to another on the same box. A channel must exist before an object can be transferred.
5.
What security measures exist for .NET Remoting in System.Runtime.Remoting?
None. Security should be taken care of at the application level. Cryptography and other security techniques can be applied at application or server level.
6. What is a formatter?
A formatter is an object that is responsible for encoding and serializing data into messages on one end, and deserializing and decoding messages into data on the other end.
7. Can you configure a .NET Remoting object via XML file?
Yes, via machine.config and application level .config file (or web.config in ASP.NET). Application-level XML settings take precedence over machine.config.
8. Choosing between HTTP and TCP for protocols and Binary and SOAP for formatters, what are the trade-offs?
Binary over TCP is the most effiecient, SOAP over HTTP is the most interoperable.
9. How do you define the lease of the object?
By implementing ILease interface when writing the class code.
10. What’s SingleCall activation mode used for?
If the server object is instantiated for responding to just one single request, the request should be made in SingleCall mode.

BizTalk

1. What is BizTalk?
Biztalk is a messaging based integration tool.Bonus:It consists of several different pieces including Business Processes (Orchestrations), BAM, Rules Engines, and HAT.
2. What is a Message Type (i.e. BTS.MessageType) and how is it used in BizTalk?
Message Type is a BizTalk System property that is promoted inside a Pipeline. It is made up of Document Name Space # Root Node Name.
3. What is the default mapping for Auto Mapping?
The default is by structure.This can be change to by node name on the map properties.
4. How do you call a Non-Serializable .Net helper class inside an Expression Shape?
o Add a reference to that class.
o Make sure your Orchestration is Long Running transactional.
o Add an Atomic scope.
o Create an Orchestration variable of that class inside the scope.
o Create an instance on that object inside the scope.
o Call the method.
o Bonus: Mention the class must be strongly signed and in the GAC.
5. What if the class is Serializable?
No transactional Orchestration or Atomic scope is needed.
6. What does the Value Mapping Functoid do?
Returns the second parameter if the first parameter is true
7. What is the difference between a Distinguished field and a Promoted Property?
o Distinguished fields are light weight and can only be used inside an Orchestration.
o Promoted Properties are defined inside a property schema, are tracking in SQL, can be tracked in HAT, and can be used for content based routing.
8. How do you achieve First-In-First-Out message processing of messages received from multiple sources using an Orchestration?
o Use a Sequential Convoy to process the messages in the order they are received into the Message Box.
o Make sure Ordered Delivery is set to True inside the Orchestration Receive Port.
9.
At high level, what do Receive Ports and Orchestration Send Port really do in terms of messaging? What about Send Ports and Orchestration Receive Ports?
o Receive Ports and Orchestration Send Port are both publishers.
o Ports and Orchestration Receive Ports are both subscribers.
10. When working with Schemas, Maps, Pipelines, and Orchestrations how should the projects be structured?
o Schemas and Maps in its own project.
o Or Schemas and Maps together in its own project.
o Orchestrations in its own project.
o Pipelines in it own project.
11. What is direct binding?
o Direct binding has three types: direct to message box, self correlating, and partner ports.
o Used to route message between the message box and Orchestrations without using bindings or from one Orchestration to another Orchestration.
12. What is BAM used for?
BAM is used to monitor business milestones and key metrics in near real-time throughout a process in BizTalk.
13. What is the Rules Engine?
Rules are used to provide highly efficient, easily changeable business rules evaluation to Business Processes. This allows rules to be changed without rebuilding and redeploying .net assemblies. The Business Rules Engine (BRE) can also be called from any .net component through the API’s
14. What are Persistence Points and what causes them?
o Persistence is when the state of a running Orchestration is stored into SQL.
o It is good enough to know various shape and actions cause persistence. More specifically, it occurs: end of a transactional scope, at a send shape, at a start Orchestration shape, during dehydration, if the system shuts down expectedly or unexpectedly, or the business process suspends or ends.
15. What group does a user need to belong to in order to submit messages to the message box?
The user needs to be a member of the hot group or isolated host group (assuming a default installation).
16. What user rights to you need to perform most actions in HAT?
o BizTalk Server Administrator
When installing Biztalk in a multi-server configuration with a remote SQL and Analysis Services