Sql-server – SQL Server Express on Windows 8 isn’t locking MDF database files

sql serversql-server-2012

Historically, if I go to rename a database that is currently being used by SQL Server, I get an exception.

On this Windows 8 desktop I'm in.. my primary desktop.. I'm able to MOVE SQL Server database files to a different partition.. without getting any errors.. even if the files are currently 'in use'. I don't understand WHY I can do this, I want to get an error when I go to move a file and it should be listed as being attached in SQL Server master.sys.databases.

I am also able to rename a file that is currently in use. The punchline is that I was talking to my network admin today, and he actually attempts to rename a .MDF file, in order to determine if this file is in use.

I don't like this. What I really want to be able to do is to MOVE files (using a DOS command) from one disk partition to another, but if they're currently attached to the default SQL Server instance, then don't try to move the file.

I was counting on this to work as advertised. I shouldn't be able to rename or move a file that is currently being used as an attached database in SQL Server.

Also, I'm sorry.. but I do have an non-standard hard-drive config.

Disk 0
System Reserved 350mb
D: SSD

Disk 1
C: (Primary volume, Windows 8 Enterprise, etc)

I'm specifically using this SSD solely for SQL Server Express on this machine, I have a half dozen attached databases at any one time and I'm actually working towards creating a MILLION detached databases (and occassionally re-attaching some of them casually when I need them).

Best Answer

SQL Express creates database with AUTO_CLOSE ON by default. Auto close databases automatically close themself (and thus release the file handles) whenever there are no sessions using the database. This works 'as advertised', you just have to read the small print:

True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, and False for all other editions, regardless of operating system.

If you don't like the default behavior, simply turn auto-close off on the databases:

ALTER DATABASE [<dbname>] SET AUTO_CLOSE OFF;

Related Solutions

Sql-server – Difficulty in moving a SQL Server database

If you wanted to make life easier, according to the Version and Edition Upgrades page, there exists an edition upgrade for SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer. You could just perform an edition upgrade and call it a day.

Alternatively, if you wanted to keep with your original plan of installing a new instance and migrating the database...

If you can't find files on your system, try installing Everything. It is an extremely fast indexing application that will run in your notification area.

Once you find the backup file, try running the following command to see if SQL Server can access it:

RESTORE HEADERONLY FROM DISK = 'drive:\path\to\mybackup.bak'

RESTORE FILELISTONLY FROM DISK = 'drive:\path\to\mybackup.bak'

If that does not work, then try moving the file to a location that the SQL Server service account has access to. If you don't plan on accessing any network resources with SQL Server, it would be easiest to run SQL Server as local system on your workstation.

Once your Developer instance can see the .bak file, you can either use the Restore Database task in SSMS (Right-click Databases in Object Explorer and select Restore Database) or you can script it. Just remember in either option you should change the destination path to the data and log files so they reside in the new default data path of the SQL Developer instance (or some other directory on your workstation). By default, it'll try putting them back in the original location when the backup was taken.

Here is an example restore script you could modify (use the RESTORE FILELISTONLY details for the logical file names):

RESTORE DATABASE [MyDB] FROM DISK = 'drive:\path\to\mybackup.bak' WITH MOVE 'logicalDataFilename' TO 'drive:\path\to\physicalDataFilename.mdf', MOVE 'logicalLogFilename' TO 'drive:\path\to\physicalLogFilename.ldf'

Sql-server – SQL Server 2012 Express with Reporting Services

Following up on my comment, you have to select ENU\x64\SQLEXPRADV_x64_ENU.exe for installation.

enter image description here

Download link.

Then run the installation package and follow instruction until you get feature selection page.

enter image description here

Follow rest of the steps and complete the installation.

Now Verify the RS installation :

Note: You must be logged in as a member of the local administrators group on the computer where SQL Server 2012 Express is installed.

Navigate to Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> Reporting Services Configuration Manager.
In the RS Configuration Connection dialog, make sure that your local report server instance (for example SQLExpess) is selected and click Connect.
In the RS Configuration Manager left pane, select Web Service URL.
Make sure that the Virtual Directory parameter is set to ‘ReportServer_’ (‘ReportServer_SQLEXPRESS’ for the SQLEXPRESS instance), and that the TCP Port is set to 80. Click Apply.
In the Reporting Services Configuration Manager left pane, select Database.
On this page, ensure that the SQL Server Name and Database Name fields contain correct values. If not, click the Change Database button and complete the Report Server Database Configuration wizard.
In the Reporting Services Configuration Manager left pane, select Report Manager URL.
On this page, ensure that the Virtual Directory parameter is set correctly, and that the URL is valid.
Click Exit to close the RS Configuration Manager.

Best Answer

Related Solutions

Sql-server – Difficulty in moving a SQL Server database

Sql-server – SQL Server 2012 Express with Reporting Services

Related Question