Month: March 2008

This is really quite cool, especially since it’s a tool feature, and one that will help SQL Server 2005 installs. Suppose you have many servers, all registered in Management Studio, in the Registered Servers window. Now you can right-click on the Local Server Group, and pick the “New Query” option. Write a query, and it gives you a result set including the results from all the servers, as if you’d done a UNION ALL using linked servers. So now if you want to do something like select name, database_id from master.sys.databases; on a query against multiple servers, you get three … Continue reading Local Server Group queries

Sick of how slow backups can run, even though you’re using compression from some tool like Hyperbac? Greg Linwood‘s had a good idea to help. A large amount of the data in any database system is the data stored in indexes (copies of the data in tables, just ordered differently) to help your queries run faster, so if you can tell the system to backup the database without that data, then it should work a whole lot faster. Of course, when you restore a smaller backup, it should a lot quicker – but you won’t have the indexes in place … Continue reading Quicker backups by ignoring index data

Installing SQL Server 2008 gives you SQLPS.exe, an application you can run which is a PowerShell including the PSDrives for SQL Server (and compatible with SQL Server 2005 as well!). But how do you make this work with your current copy of PowerShell? Well, I just worked this out. Darren Gosbell was lamenting with me about the fact that it’s not a SnapIn for PowerShell, but it turns out it is. We ran: Get-PSSnapin -reg …and got the following results: Name        : SqlServerCmdletSnapinPSVersion   : 1.0Description : This is a PowerShell snap-in that includes various SQL Server cmdlets. Name        : SqlServerProviderSnapinPSVersion   … Continue reading SQL Server 2008 PowerShell SnapIn

I’ve written before that SQL Server 2008 (February CTP) gives you SQLPS – a PowerShell interface to SQL Server. So I was trying to get the ScriptingOptions happening, and this is the only way I’ve found so far. It’s ugly, and if you have a better way of doing this, please post a comment for me. [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions$so.DriAllConstraints = $true(get-item Sales.Store).Script($so) And this gives me the Sales.Store table with all the default constraints thrown in. Much better. Ugly way of doing it, but at least it works.

So I’m at the Heroes launch in Melbourne today. I’m going to hang out at the Experts booth, so if you’re here and have a question about SQL Server 2008, why not come over?