I recently got a request to do a "cold" backup of a small yet complex db environment, I say "small" because the sum of six db sizes is less than 60 GB, yet "complex" means the six dbs are on five physical servers. three of the which are clustered servers, the six dbs are involved in pretty complex replication framework, with multiple publishers to one susbscriber and mulitple subscribers to one publisher and also some subscriptions are updatable subscriptions (in the transactional replication).

Anyway, the environment is for QA team, and once the QA team is done with one test, they want the environment to be restored back to the state before the test starts. Because there is replication involved here, it will be pretty complex to restore the environment to a specific state. I finally come up with the following plan:

Before the environment is handed over to QA team, we will do a file level backup of all databases involved (including the distribution db), and we call this "cold" backup because we need to shutdown each sql server service, and then copy the db files to a backup folder.

It will be very inconvenient to go to each server to do the sql server service shutdown, so I explored the possiblity to shutdown each sql server instance remotely from a central place, and here is what I found

1. To shutdown no-clustered sql server services

sc \\<server_name> stop "SQLServerAgent" 

sc \\<server_name> stop "MSSQLServer"

for name instance

sc \\<server_name> stop "SQLAgent$<instance_name>" 

sc \\<server_name> stop "MSSQL$<instance_name>"

To start sql server service, just change the key word from stop to start in the above commands.

2. To shutdown clustered sql server service

cluster /cluster:<cluser name> res "sql server agent" /off

cluster /cluster:<cluser name> res "sql server" /off

(I have no clustered named instance so I cannot test the clustered named instance sql server service )

To start the clustered clustered sql server service, just change the key word from stop to start in the above commands.

Put these cmds in a batch file, together with a copy file command, then a cold backup will be done easily. When you need to restore, do the similar thing, stop the sql server services and then overwrite the current db files with the backup db files.