Database shutdown(close) programmatically

Question

Database shutdown(close) programmatically

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 27, 2010 at 1:22 am

#146810

hi
i need to detach the DB programmatically and then move DLF and MDF to another shared location. What status of the Db i need to maintain in system tables/objects( like sys.databases) to do this activity. remember , i only have to do everything programmatically.So what status i nned to change in system table.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You'll need to set up an account that has the rights to do that, and then have the program use that account to issue the necessary T-SQL commands.

Books Online (or MSDN) has an article on all the steps and commands to issue to detach the database. Then move the files, and so on. Search for "detach" in there, and you'll find what you're looking for. (If you don't have BOL installed, you can either install it, or go to http://www.msdn.microsoft.com and use that instead. They have the same data.)

You don't have to change any data in any system tables directly. You'll be issuing Alter Database commands, and calling sp_detach_db. You can do that programmatically through any connection that has the rights.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

GSquared (8/27/2010)
You'll be issuing Alter Database commands, and calling sp_detach_db. You can do that programmatically through any connection that has the rights.

Thanks But

What if any user or application accessing that DB at the time detaching ? Dont we need to close all related connection ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You don't need to change a status in the system databases (you can't change system tables)

Alter database ... set offline

-- copy files

Alter database ... set online

Or, if you don't need the db on the original server afterwards

exec sp_detach <db name>

-- copy files

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

In addition to what Gail stated, after you offline the database - you need to alter the database and modify the filename, then copy the physical files to the new location.

After that, you can bring the database online.

ALTER DATABASE ... MODIFY FILE (Name = 'logical name', FILENAME = 'new path and name')

Lookup ALTER DATABASE in Books Online for exact syntax and samples.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

If the database is in use you will not be able to put it offline.

For that you do have two option:

1. Kill the connection to the respective database

2. In addition to Gila & Jeffery you can set database offline with ROLLBACK IMMEDIATE option.

But keep in mind both the way active transactions may result into data loss. Hence advisable not to use for production database.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 6

If you use SSMS and detach the database there is an option to close all connections.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 7

free_mascot (8/30/2010)
If the database is in use you will not be able to put it offline.
For that you do have two option:
1. Kill the connection to the respective database
2. In addition to Gila & Jeffery you can set database offline with ROLLBACK IMMEDIATE option.
But keep in mind both the way active transactions may result into data loss. Hence advisable not to use for production database.
HTH

The recommended method for moving database files - by Microsoft - is to take the database offline, alter the database files to the new location, copy the files, then bring the database online.

If you use ROLLBACK IMMEDIATE, in progress transactions will be rolled back. Yes, that could cause issues for your users - so you want to schedule it appropriately.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs