1

I just installed Microsoft ServiceBus and it created 5 databases. Now I want to move them to some specific location so I can join them to SQL Server AlwaysOn Availability group.

I stopped service bus farm and tried to execute the following queries one by one:

ALTER DATABASE [SbManagementDB] SET OFFLINE;
ALTER DATABASE [SbManagementDB_log] SET OFFLINE;

ALTER DATABASE [SbManagementDB] MODIFY FILE ( NAME = [SbManagementDB], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB.mdf' );
ALTER DATABASE [SbManagementDB_log] MODIFY FILE ( NAME = [SbManagementDB_log], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB_log.ldf' );

ALTER DATABASE [SbManagementDB] SET ONLINE;

I brought SbManagementDB offline successfully, but when I tried to do that to SbManagementDB_log I've got the following error:

Msg 5011, Level 14, State 5, Line 2
User does not have permission to alter database 'SbManagementDB_log', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

I executed the following query SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files and made sure that this DB exists and I tried sa login, but got the same error.

Any ideas what am I doing wrong?

4

You need to do several steps to modify the location of a database's files.

  1. Alter the catalog entries for the database in question using the `ALTER DATABASE' command
  2. Take the database itself offline.
  3. Move the physical files to their new location
  4. Bring the database back online.

For instance, for the SbManagementDB database, you'd do something like:

ALTER DATABASE SbManagementDB 
MODIFY FILE (
    NAME = 'SbManagementDB'
    , FILENAME = 'F:\path\to\new\location\SbManagementDB.mdf'
);

ALTER DATABASE SbManagementDB 
MODIFY FILE (
    NAME = 'SbManagementDB_Log'
    , FILENAME = 'F:\path\to\new\location\SbManagementDB.ldf'
);

ALTER DATABASE sbManagementDB SET OFFLINE;

Now you physically move the files, probably using Windows Explorer, into F:\path\to\new\location, and bring the database back online.

ALTER DATABASE sbManagementDB SET ONLINE;

In your question, you are attempting to take database files offline, which is not necessary, and causing you grief.

2

It's because [SbManagementDB_log] is the logical name of one of the 2 files that make up the [SbManagementDB] database. The DB is made up of one or more data files and at least one log file (usually only 1)

To find the list of databases you should query sys.databases and not sys.master_files, sys.master_files will tell you the names of all the files that make up each database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.