Instead of detaching you could stop the SQL Server service and backup .mdf and .ldf files.
To detach the database you need exclusive access to it:
To detach a database
In SQL Server Management Studio Object Explorer, connect to the instance of the SQL Server Database Engine and then expand the instance.
Expand Databases, and select the name of the user database you want to detach.
Detaching a database requires exclusive access to the database. If the database is in use, restrict access to a single user:
Right-click the database name and point to Properties.
In the Select a page pane, select Options.
In the Other options pane, scroll down to the State options.
Select the Restrict Access option, and in its drop-down list, select Single.
Click OK.
A message box appears to inform you that this action will close all connections to the database. To proceed, click OK.
Right-click the database name, point to Tasks, and then click Detach. The Detach Database dialog box appears.
The Databases to detach grid displays the name of the selected database in the Database Name column. Verify that this is the database you want to detach.
By default, the detach operation retains any out-of-date optimization statistics when detaching the database; to update the existing optimization statistics, click the Update Statistics check box.
By default, the detach operation keeps any full-text catalogs that are associated with the database. To remove them, clear the Keep Full-Text Catalogs check box.
The Status column displays the current database state (either Ready or Not Ready).
If the status is Not Ready, the Message column displays hyperlinked information about the database. When a database is involved with replication, the Message column displays Database replicated. When a database has one or more active connections, the Message column displays <number_of_active_connectio
To obtain more information about a message, click the hyperlink.
When you are ready to detach the database, click OK.
Note:
The newly detached database will remain visible in the Databases node of Object Explorer until the view is refreshed. You can refresh the view at any time: Click in the Object Explorer pane, and from the menu bar select View and then Refresh.
Source: MS SQL Help
Main Topics
Browse All Topics
by: ged325Posted on 2009-06-04 at 07:48:41ID: 24547565
don't use -d DB_name, use master.
Essentially you are connecting to the database, then trying to detach it. But since you're connected you block yourself.
The other (better) way of doing this is to run a backup job with sqlserveragent. The output of this is a BAK file which you can then do a file backup. (EG: run the db backup at 8, then the filebackups come along at 9).