Moving the WSUS 4 Database
"If it wasn’t for that last minute, nothing would ever get done."
Why might you want to do this in the first place? Having a database on the system drive is generally a bad idea. WSUS uses the Windows Internal Database which is located in the Windows folder. Having a database on a separate volume is generally a good idea, if only for performance reasons.
Preparation
1) Designate a new location for the database. Perhaps SSD or NAS based storage.
2) Like any WSUS administrator sooner or later realises, I had been given a database but no means to administer it. I downloaded and installed the SQL Express Management Studio (SQL Server 2012 SP2, SQLManagementStudio_x64_ENU.exe)
3) Stop the WSUS service (services.msc)
4) Locate your WSUS Windows Internal Database (WID)
C:\>dir c:\windows\WID\Data
Volume in drive C has no label.
Volume Serial Number is ****-****
Directory of c:\windows\WID\Data
02.03.2015 22:18 <DIR> .
02.03.2015 22:18 <DIR> ..
02.03.2015 00:40 4.194.304 master.mdf
02.03.2015 00:40 1.048.576 mastlog.ldf
02.03.2015 00:40 2.162.688 model.mdf
02.03.2015 00:40 524.288 modellog.ldf
02.03.2015 00:40 13.107.200 msdbdata.mdf
02.03.2015 00:40 524.288 msdblog.ldf
02.03.2015 22:19 3.816.882.176 SUSDB.mdf
02.03.2015 22:19 25.296.896 SUSDB_log.ldf
02.03.2015 21:51 40.960.000 tempdb.mdf
02.03.2015 21:05 22.675.456 templog.ldf
10 File(s) 3.927.375.872 bytes
2 Dir(s) 112.296.820.736 bytes free
C:\>
Making the move
In Management Studio, I connected to
\\.\pipe\Microsoft##WID\tsql\query
I expanded Databases and detached the SUSSB
In this case the destination is a folder on volume d:. d:\WSUS DB
Moving the DB (elevated command prompt):
C:\>move c:\windows\wid\data\susdb* "d:\WSUS DB"
c:\windows\wid\data\SUSDB.mdf
c:\windows\wid\data\SUSDB_log.ldf
2 file(s) moved.
C:\>
It is too soon to re-attach the db. It would result in a read error or the database would be mounted as read-only. I needed to copy the NTFS permissions (ACL) first
PS C:\Windows\WID\data> Get-ACL | fl
Path : Microsoft.PowerShell.Core\FileSystem::C:\Windows\WID\data
Owner : BUILTIN\Administrators
Group : BUILTIN\Administrators
Access : NT AUTHORITY\SYSTEM Allow FullControl
BUILTIN\Administrators Allow FullControl
NT SERVICE\MSSQL$MICROSOFT##WID Allow Write, Delete, Read, Synchronize
NT SERVICE\MSSQL$MICROSOFT##WID Allow -1073676288
Audit :
Sddl : O:BAG:BAD:PAI(A;OICI;FA;;;SY)(A;OICI;FA;;;BA)(A;;0x13019f;;;S-1-5-80-1184457765-4068085190-3456807688-22009523
27-3769537534)(A;OICIIO;SDGWGR;;;S-1-5-80-1184457765-4068085190-3456807688-2200952327-3769537534)
PS C:\Windows\WID\data>
This entry is most important: NT SERVICE\MSSQL$MICROSOFT##WID , but I copied the ACL as a whole.
PS C:\Windows\WID\data> Get-Acl C:\Windows\WID\Data | Set-Acl -Path 'D:\WSUS DB'
PS C:\Windows\WID\data>
In SQL Management Studio, I re-attached the database.
Next I started the WSUS service.
Sources
(1) http://systemspecialist.net/2013/05/15/move-or-delete-a-wsus-4-windows-internal-database-wid-on-windows-server-2012/
(2) https://technet.microsoft.com/en-us/library/hh849810.aspx
(3) https://social.msdn.microsoft.com/Forums/sqlserver/en-US/345031e7-88c5-40e5-bfda-3880122045e0/error-17204-fcbopen-failed-how-to-set-persmission-correctly-on-a-datalog-file-for-sql-server?forum=sqldatabaseengine
