The Windows Server Update Services (WSUS) role, which first appeared in Windows Server 2008 (previously WSUS was installed as a separate app, Software Update Services), allows system administrators to manage the installation of updates and security patches for Microsoft products on computers in the corporate network. The WSUS database can use the Windows Internal Database (WID) or a separate SQL Server instance. This selects the administrator when installing the WSUS role.
In this article we are going to show you how to backup this database in Windows Server 2012 R2/2016 environment.
WSUS does not have built-in utilities for backing up and restoring. You can use ntbackup (wbadmin) or third-party products to backup WSUS.
The WSUS role architecture consists of a database (it stores information about WSUS settings, update metadata, clients, approvals, and other data) and the directory WsusContent with the update files (it contains the cab update files themselves).
When using the built-in WID as a WSUS database (used in most WSUS installations), its files are stored in the C:\Windows\WID\Data directory. Database files are called SUSDB.mdf and SUSDB_log.ldf. You cannot remotely connect to the WSID WID db via SQL Server Management Studio. Therefore, to backup the WID database, you will have to use the sqlcmd.exe command-line utility, which must run a specific SQL script.
To backup WSUS database you just need to complete 4 easy steps:
- Download and install SQL Server 2012 SP1 Feature Pack. It will give you an opportunity to use SQL command line;
- Download and install ODBC driver for SQL Server 11;
- Create and run Backup WSUSdb script;
- Create a task in Windows Task Scheduler to run the SQL script.
We are going to miss the first 2 steps, because they really easy. So let’s start from the 3 step.
At first we need to create BackupWSUSdb.sql file that contains two lines:
BACKUP DATABASE [SUSDB] TO DISK = N’f:\wsus_db_backup\WSUS_DB.BAK’ WITH NOFORMAT, INIT, NAME = N’WSUSDB – Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,STATS = 10
F:\wsus_db_backup\WSUS_DB.BAK is the path to the backup file that you want to create (you can change the path and file name, it’s really up to you).
Running SQL script
To run the script you should use sqlcmd.exe utility:
sqlcmd.exe -S np:\.\pipe\MICROSOFT##WID\tsql\query -if:\WSUS_DB_Backup\backupWSUSdb.sql
Action to Launch the Script
It’s quite trivial, just do everything like on next screenshot. Start Task Scheduler and create a new job to run the above command according to schedule.
That’s all. The WSUS database backup is created.
If you want to create a backup copy of the update files, and not to download them again when reinstalling or moving WSUS to another server, you need to stop IIS and WSUS Service and copy the contents to the E:\wsus\WsusContent directory.
When using a full-fledged SQL Server instance, you can configure a backup of the WSUS database using the built-in SQL Server Scheduler tasks from the SQL Server Management Studio console GUI.
You can use the special PowerShell module for SQL (SqlServer) to create a backup copy of the database, you can manually download the module from the PowerShell Gallery (https://www.powershellgallery.com/packages/SqlServer/21.0.17224) or install it using the PoSh command:
Install-Module -Name SqlServer
To back up the SQL database from PowerShell, run the PoSh console as an admin and run the command:
Backup-SqlDatabase -ServerInstance "\.\pipe\MICROSOFT##WID\tsql\query" -Database "SUSDB" -BackupFile e:\backup\SUSDB.bak
If you want to reinstall WSUS using your WSUS database backup file, you can install SQL Server Express, restore the database from the backup with the same name SUSDB. And during the WSUS installation, it’s enough to specify that the existing database will be used.
In this case, all WSUS settings will be saved.