Tuesday, August 17, 2010

[QA] Linked server for QA - faking a prod server

In case you use linked server a lot. This creates a linked server named ServerB that actually connects to the local server. I needed it for testing, as a lot of code references linked servers (no comments on _that_, please)

EXEC sp_addlinkedserver
@server = 'ServerB',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = 'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=True;'

(allows you to query [prodbox01].yourbigdb.dbo.prodtable, but actually get data from [qabox01].yourbigdb.dbo.prodtable)

EXEC master.dbo.sp_addlinkedserver @server = N'PRODBOX01', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'QAbox01'

Friday, August 13, 2010

[Tricks] Single-user-mode with a twist

Had to restart a SQL Server in single-user mode. Two problems. One, the configuration was preventing it from starting properly, and there were automatic processes connecting to that single user.

The normal way to do it is:
sqlservr -m
(that's single user mode)
but for this we needed the configurations to not screw us up.
sqlservr -f
and we needed to make sure that it was only us connecting.
sqlservr -f"sqlcmd"
(Yes, you could also do sql server management studio)