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();
}
}