Upgrade SQL Express to Standard or Enterprise
"Microsoft so very graciously provides a free version of SQL 2005, SQL Express, with MOSS. If you install MOSS using the Basic option or Single Server under Advanced you get SQL Express automatically. So what if as a budding newbie SharePoint admin you chose the Basic option, but now as a wise aged SharePoint admin you've seen the error of your ways and want to use a more respectable version of SQL for your SharePoint backend?"
Todd Klindt made a post to upgrade SQL Express: http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=55
If you want to upgrade SQL Server 2005 express to SQL server 2008 Enterprise (or higher then express) you have first upgrade SQL Server 2005 Express to SQL server 2008 Express. You can use the following commands:
//First extract the contents of the SQL Express download into a directory.
en_sql_server_2008_express_with_advanced_services_x86 /EXTRACT
//Install Windows Installer 4.5 and Windows Powershell.
//Then remove SQL Tools 2005
msiexec /quiet /Uninstall {58D379F7-62BC-4748-8237-FE071ECE797C}
//Remove Management Studio
msiexec /quiet /Uninstall {20608BFA-6068-48FE-A410-400F2A124c27}
Make sure you activated the powershell feature in Windows 2008 enterprise (in earlier versions you have to download powershell): Administrative Tools --> Server Manager --> Features
Getting the Identity value by inserting a record in SQL Server
Often you want to get back the ID of a record you just inserted.Never do
something like: int id = items.count + 1; while the index is counting on by
removing a record!
1) Generate a GUID in C# and insert this as identity. Since you manage the
identities by yourself you know the id of the current record. This could be
something like this:
[code:c#]
System.Guid guid = System.Guid.NewGuid ();
String generatedId = guid.ToString();
String sql = "INSERT INTO problem (id, description) " +
"Values (@GeneratedIdField, @DescriptionField)";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
command.Parameters.AddWithValue("@GeneratedIdField", generatedId);
command.Parameters.AddWithValue("@DescriptionField", txtDescription.Text);
cmd.ExecuteNonQuery();
[/code]
2) Use ExecuteScalar(both the insert and the select @@identity all in one shot)
to get the generated ID from SQL Server (autoincrement):
[code:c#]
String sql = "INSERT INTO problem (description) " +
"Values (@DescriptionField)" + "SELECT @@identity";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
command.Parameters.AddWithValue("@DescriptionField", txtDescription.Text);
Int32 problemId = Convert.ToInt32(cmd.ExecuteScalar());
//You have to cast the object returned
//by the ExecuteScalar method to your type (e.g. int or long).
[/code]
Restoring a .bak file to a SQL Server database
Sometimes you have to restore a .bak file to a SQL Server database where the .bak file was not originally back-upped from. You can do this either by a script or using SQL Server Management studio. By using Management Studio, you have to follow the next steps:
- Open SQL Server Management Studio (either Enterprise or Express)
- Right Click Databases -> Restore Database…
- Select your To Database e.g. OrdersNew
- Select from device and select your .bak file
- Move to the item options (you're standard in General)
- Check or the database paths are correct, otherwise correct them
- Go back to the general tab
- Check your database and hit the OK button
[/code]
Download Microsoft SQL-Server management studio express