February 27, 2020 at 8:24 pm
There is SO MUCH that I DON’T know…
Testing a db move to new drive on local with local admin rights and sysadmin permissions, win10 and SQL 2016 dev
-- Detach Script --
ALTER DATABASE [test001] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'test001'
Records for this database are gone from both databases and master_files.
I cannot rename, delete or copy the source files.
-- Attach Script --
CREATE DATABASE [test001] in (Filename=N’<path>’), (Filename=N’<path>’)
The database is back and the records for this database are back in both databases and master_files.
---------Now I try SET OFFLINE
ALTER DATABASE [test001] SET OFFLINE WITH NO_WAIT
Records for this database are still in both databases and master_files, BUT master_files shows it as ONLINE and databases shows it correctly as OFFLINE and state=6.
Start a ROBOCOPY to move the files (AND IT WORKS – I HAD FULL CONTROL FOR THE FILES NOW)
ALTER DATABASE [test001] MODIFY FILE (NAME = [test001_L], Filename=N’<path>’)
ALTER DATABASE [test001] MODIFY FILE (NAME = [test001_D], Filename=N’<path>’)
ALTER DATABASE [test001] SET ONLINE
And everything works fine.
I have read some other posts where people have had problems with locked files after detach, but I did not see a solution.
SO, what am I missing?
I’ve seen before where master_files does not always reflect the correct state. Is this a well-known thing?
And the BIG Question; what does detach do that locks those files? And those records are gone form the system tables, but the file is still locked??
Then SET OFFLINE keeps the records and releases control of the files???
February 27, 2020 at 10:28 pm
Two different views where sys.databases status has the database status and sys.master_files status has the file status.
Detach doesn't do anything to lock files. With a lot of the posts, the files weren't locked but the permissions had changed to the principal that detached the database. Otherwise they could be locked by the OS due to things such as the files not excluded from antivirus software. But it's usually been a permissions issue.
Taking a database offline and detaching a database can be used for different things so you probably don't want to think of them as being the same. You can set a database from online to offline and back to online again without specifying the files. Just ALTER DATABASE [test001] SET ONLINE, ALTER DATABASE [test001] SET OFFLINE, ALTER DATABASE [test001] SET ONLINE. With sp_detach_db, you may not ever attach again so the meta data about the files no longer exists. You need to specify the files in the attach process. You can take a database offline to move files, you may set a database offline before decommissioning just in case the database really was being used and you need to quickly set it online. Things like that where usually your intention is to bring the database back online on the same instance.
Sue
March 2, 2020 at 4:18 pm
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply