Command to open SQL Server Configuration Manager

I believe most of us already know to type "ssms" (for 2008) or "sqlwb" (for 2005) or "isqlw" (for 2000) in the "Run" command box from the start menu to open SSMS studio or Query Analyzer.

Here is one more, to open SQL Server Configuration Manager, you need to type "SQLServerManager10.msc" to get there. If you want to see the list of all the windows services, "services.msc" is the command you need.

Error: The partner transaction manager has disabled its support for remote/network transactions

I bumped into this error message when our developers created a trigger on a Windows 2003 server that updates a table in a linked server (a Windows 2008 server). The second part of the error message is: "linked server was unable to begin a distributed transaction". I put the whole error message text here purely for the sake of Google keywords and the essential issue behind is actually how to enable the distributed transaction function on a Windows server.

If you Google the title, there is a useful link from MSDN, which provides the following solution:

First verify the "Distributed Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distributed Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK(In my case, we didn't reboot the server)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

Basically speaking, to enable distributed transaction for linked server, besides making sure the Distributed Transaction Coordinator service is up and running, you have to enable network DTC access. And there is a very good article (for Windows server 2003) from Microsoft's knowledge base officially documenting exactly the same set of actions.

Here are the screen shots you have to encounter during the settings for Windows server 2003:

Component Services:


My Computer Properties


Security Configuration


If you tried to enable network DTC access for Windows server 2008 (or Windows vista if you are fan of it), Microsoft has already provided another article in its knowledge base for your viewing pleasure as well. The settings are almost the same as those for Windows Server 2003, the slight change lies in where you can find the "Security Configuration" dialog box; in Windows Server 2008, it is in the "Local DTC" properties instead of "My Computer" properties in the component services MMC.

One screen shot speaks more than 1000 words.

Good tutorial script for full recovery and transaction log backup

--Creating the database for the demo
create database DemoDB
go

--Make sure that the database is in full recovery model
alter database DemoDB set recovery full
go

--Making the first full backup. From now on I can use log backups
backup database DemoDB to disk = 'c:\DemoDB_1.bak' with init
go

--Creating a new object in the database
use DemoDB
go
create table DemoTable (i int)
go

--backup the log
backup log DemoDB to disk = 'c:\DemoDB_1.trn' with init
go

--Inserting a record that doesn't exist in all the backups that were taken into the table
insert into DemoTable (i) values (1)
go

--Creating a second full backup. I won't be using this backup when I'll restore the database
backup database DemoDB to disk = 'c:\DemoDB_2.bak' with init
go

insert into DemoTable (i) values (2)
go

--Creating the second log backup that includes the second record in DemoTable
backup log DemoDB to disk = 'c:\DemoDB_2.trn' with init
go

--Dropping the table (to mimic an error)
drop table DemoTable
go

--Now I'll restore the database. I'll use only the first full backup
--with the 2 log backups. This proves that I don't need to use the
--copy_only switch, if I have a full backup with all log backups that
--were taken since then even if another full backup was taken between
--the log backups
use master
go
restore database DemoDB from disk = 'C:\DemoDB_1.bak' with norecovery, replace
restore log DemoDB from disk = 'c:\DemoDB_1.trn' with norecovery
restore log DemoDB from disk = 'c:\DemoDB_2.trn' with recovery
go

Use DemoDB
go
--Notice that I have the table with 2 records
select * from DemoTable
go


--cleanup
use master
go
drop database DemoDB

How to know the logical files' names from a database backup?

To use the "move" clause in a restore script, we need first to know the logical files inside the backup. The following sql command can do the trick:

restore filelistonly
from disk = N'the backup location'