Here’s a simple Powershell snippet to check the mirroring status on your SQL Server instances. ?View Code POWERSHELL# Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;   # Servers to check $sqlservers = @("server1", "server2", "server3"); foreach($server in $sqlservers) { $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server; # Get mirrored databases $databases = $srv.Databases | Where-Object {$_.IsMirroringEnabled -eq $true}; Write-Host [...]

In order to automate testing of backups it’s useful to be able to query backup files to access various bits of meta-data. We can do this with the RESTORE FILELISTONLY TSQL command. In the simplest format the command is as follows; ?View Code TSQLRESTORE FILELISTONLY FROM DISK = ‘c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\AdventureWorks_20110320.bak’; This doesn’t help [...]

In a previous post I showed how you can collect information on what is held in the data cache. The data collected here was just a simple summary of how much space each database was consuming. While useful we will need more detailed information on what is inside the cache to get a proper handle [...]

I’m currently building a CMDB at work and I wanted to include a bit of performance data in the information collected. The data cache is probably the biggest consumer of memory within SQL Server so It makes sense to collect this for future analysis. As a start I’m gathering the amount of space each database [...]

I was recently given a du-duping task which was much more difficult than I anticipated and taxed my SQL brain to its limits. I thought of using a CTE to do this but all of the examples I could find for deleting records with a CTE wouldn’t have worked in my situation. Essentially the table [...]

Just a quick post showing how to add a Postgres database server as a Linked Server in Microsoft SQL Server. Install the psqlODBC driver for Windows. Control Panel > Administrative Tools > Data Sources (ODBC). Click the "System DSN" tab and click the Add button. Choose "PostreSQL ANSI" and click Finish. Configure the data source by [...]

If you ever encounter this SQL Server error when selecting from a view then somebody has probably dropped columns from the base table. Here’s a quick run through of the problem. ?View Code TSQLCREATE TABLE dbo.Contact ( Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DOB DATETIME [...]

Today I received the following SQL Server error which I had never encountered before. Msg 4429, Level 16, State 1, Line 1 View or function ‘Table_1′ contains a self-reference. Views or functions cannot reference themselves directly or indirectly. Msg 4413, Level 16, State 1, Line 1 Could not use view or function ‘Test.Table_1′ because of [...]

Chuck Boyce Jr (blog | twitter) recently commented on a limitation of the script from my post Discover SQL Servers with Powershell. The script does require that the SQLBrowser service is running for discovery to occur which may be a major issue for some. Here’s an alternative method that does not have this limitation. All [...]

I’m currently building a lot of SSIS packages that are primarily date driven. Many of these involve periods of 100 days, to several years, so I wanted to automate the testing of these packages. I’d previously automated the testing of stored procedures over date ranges but wanted a solution for testing the system as a [...]