Tuesday, August 30, 2011

Tuesday, August 23, 2011

Get all the DB's programatically

To get all the DB's programatically on a local instance of SQL Server using SMO.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
 
namespace ConsoleApplication1
{
    class Program
    {       
        static void Main(string[] args)
        {
            Server s = new Server(".\\INSTANCENAME");
            foreach (Database d in s.Databases)
            {
                Console.WriteLine(d.Name.ToString());
            }
            Console.ReadKey();
        }
    }
}

Points of Interest

Don't forget to add the references via Project >> Add Reference... >> Browse tab

Tuesday, August 16, 2011

C# User Wizard


I need to build a wizard in C# to walk a user through gathering some information.

I went poking around Google, and found this little fellow on Code Project.

http://www.codeproject.com/KB/miscctrl/DesignTimeWizard.aspx


Tuesday, August 9, 2011

Backing Up Laptop

I use SQL Server on my laptop. Surprising, I'm sure.

However, in order to backup my local databases, I use a basic file copy method. (SyncToy and xcopy are examples of MicroSoft free/included utilities that do this)

In order to make a copy/backup of the databases I use, I first have to turn off SQL Server. This is because the basic file-copy utilities I use can't copy a file currently in use. A running instance of SQL Server automatically means there are DB files in use.

Therefore, it is necessary to kill all going SQL Server instances. This has a secondary benefit of reducing CPU overhead from the running SQL Server processes, and is useful while running the laptop on battery. Say, while writing blog posts on the couch after hours.

So, you need to kill all open processes.

The easiest way I've found to do this is
  1. Press windows+R to get a "Run" window
  2. Type "cmd" and press enter to get a command shell
    1. Note that you may have to gain an administrator shell to do this...
    2. Since I'm currently running enhanced permissions on my user account, YMMV
  3. Type "NET START" to get the list of all currently running processes
  4. Copy all SQL Server entries from the windowed results
    1. Alternately, you can type something like
    2. "NET START > C:\RUNNING_LIST.TXT"
    3. This will generate a text file containing all the processes you're currently running.
  5. No matter how you get your process list, you have to:
    1. Get the list into a text file
    2. Edit it so it doesn't contain any processes but SQL Server processes
  6. For each SQL Server process listed, prepend the line with "NET STOP"
You should wind up with text that looks like this:
NET STOP "SQL Server Analysis Services (MSSQLSERVER)"
NET STOP "SQL Server Browser"
NET STOP "SQL Server Reporting Services (MSSQLSERVER)"
NET STOP "SQL Full-text Filter Daemon Launcher (BLANCO)"
NET STOP "SQL Full-text Filter Daemon Launcher (MSSQLSERVER)"
NET STOP "SQL Server (BLANCO)"
NET STOP "SQL Server (MSSQLSERVER)"
NET STOP "SQL Server (SQLEXPRESS)"
NET STOP "SQL Server VSS Writer"
Personally, I just put this into a file named KillSQL.cmd, and I run it right before I back up my PC.

Don't forget to make a file full of "NET START" commands to bring everything back to life again.

Tuesday, August 2, 2011

Lost my hibernate option

powercfg.exe /hibernate on

That's all it took to bring back Hibernate on my PC from the dead. 

WHY it disappeared as an option is another matter entirely.