-
How to Open PowerShell as Administrator
This post covers most various ways for how to open PowerShell as Administrator on Windows, ensuring you have the necessary permissions for advanced operations.
Running PowerShell commands with elevated privileges is often necessary when configuring system settings, installing software, or troubleshooting issues. I think most tasks in my PowerShell Scripts Blog Post Tag need this elevated access which might be good for reference.
Methods Covered in This Guide:
1. Open PowerShell as Administrator by Command Prompt
2. Open PowerShell as Administrator Using the Start Menu
3. Open PowerShell as Administrator with Windows Terminal
4. Open PowerShell as Administrator Using Task Manager
1. Open PowerShell as Administrator by Command
The following is a very quick way to open PowerShell as Administrator, from a PowerShell Terminal window:
# open powershell as administrator Start-Process powershell -Verb runas
A User Account Control (UAC) prompt will appear, click Yes to confirm and the PowerShell Admin terminal will open.
2. Open PowerShell as Administrator Using the Start Menu
Next up, we’ll open PowerShell as Admin using the Start Menu:
Windows 10/11:
1. Hit the Windows Key.
2. Type PowerShell in the search bar.
3. Click Run as Administrator when the option appears.
Windows 8:
1. Click the Start button and type PowerShell.
2. Right-click Windows PowerShell and select Run as Administrator.Windows 7:
1. Click Start and navigate to All Programs > Accessories > Windows PowerShell.
2. Right-click Windows PowerShell and choose Run as administrator.3. Open PowerShell as Administrator with Windows Terminal
Windows Terminal is the terminal app for PowerShell on Windows 11. Check out my other post for a quick guide for installing Windows Terminal on your machine.
To open PowerShell as Administrator using Windows Terminal:
1. Open Windows Terminal.
2. Hold CTRL and click the + (plus) icon in the top bar (if PowerShell is the default shell).
3. Alternatively, right-click Windows Terminal and select Run as administrator.
4. Open PowerShell as Administrator Using Task Manager
Lastly, here’s an alternative option sometimes useful if you’re stick with no other option available. We’ll open PowerShell as Admin via Task Manager:
1. Press Ctrl + Shift + Esc to open Task Manager.
2. Click Run New Task.
3. Type PowerShell, check Create this task with administrative privileges, and click OK.
Hope all this was useful for you!
-
How to Filter Objects in SSMS
SQL Server Management Studio (SSMS) offers a quick filtering feature that lets you narrow down your search by criteria such as name, schema, or creation date, which will definitely save you some time.
When working with large databases with a lot of objects in SQL Server, finding specific objects like tables, views, or logins can be more of a challenge. It can take a while scrolling through a big list manually. While we do have the option of using the Object Explorer Details Pane in SSMS, this filtering guide is often a quicker way for finding a specific object.
Steps to Filter Objects in SSMS
While the example demonstrates filtering SQL logins, the process is the same for other SQL objects, such as tables, views, stored procedures (SPs), and more.
1. Open the SSMS Object Explorer and expand the container your object resides in..
2. Right-click Logins and select Filter Settings.
3. Apply the Filter in the prompted window. Click OK and the Object Explorer will show only the objects that match your criteria.
4. You should now see your filtered object(s) in the SSMS Object Explorer.
5. To remove the filter, right-click the object type again and select Remove Filter.
-
The Evolution of Microsoft SQL Server
Microsoft SQL Server is a Relational Database Management System (RDMS) developed by Microsoft. It has undergone many evolutions since its initial release in 1988 (the year I was born). I thought today I’d write about the history of MSSQL, and include whatever thoughts I have along the way from my experience as a SQL DBA.
SQL Server is the system I use daily. I’m a Database Reliability Engineer (officially a Site Reliability Engineer), looking after critical SQL Server databases that have various workloads and importance. The most important SQL Servers run with Enterprise Edition, configured with Always On Availability Groups to ensure the database systems are Highly Available incase of disaster. Other SQL Servers, still important servers, run the SQL Server Web Edition which operates without the SQL Server Agent.
Anyway, there’s some background information for this. Below in this post I’ll run through the history of Microsoft SQL Server since its release, until today. This year we’re getting SQL Server 2025!
The History of Microsoft SQL Server
In June 1988, Microsoft, Ashton-Tate, and Sybase collaborated to create a variant of Sybase SQL Server for IBM OS/2. This birthed Microsoft SQL Server, the company’s foray into the enterprise-level database market competing against industry giants like Oracle and IBM.
SQL Server Legacy Versions
SQL Server 1.0 to SQL Server 7 (1989-1998)
The initial versions of SQL Server were designed for OS/2, with the first version released in 1989. The transition to Windows NT in 1993 marked the divergence of paths between Microsoft and Sybase. SQL Server 6.0, released in 1995, was the first version specifically designed for Windows NT. A major rewrite of the Sybase engine, SQL Server 7.0 introduced User Mode Scheduling, multi-dimensional database products, and ended support for the DEC Alpha platform. This version laid the foundation for future advancements in High Availability (HA).SQL Server 2000 (2000) – Version 8.0
Building upon Sybase code, SQL Server 2000 introduced several enhancements, including support for IA-64 architecture and the introduction of key complementary systems like SQL Server Integration Services and Reporting Services.SQL Server 2005 (2005) – Version 9.0
Notable for native XML support, CLR integration, and the introduction of Database Mirroring for High Availability. SQL Server 2005 set the stage for advancements in performance, security, and management tools.SQL Server 2008 (2008) – Version 10.0
Bringing self-tuning and self-organizing data management, SQL Server 2008 introduced support for structured and semi-structured data, better compression features, and enhanced indexing algorithms.SQL Server 2008 R2 (2010) – Version 10.50
Building upon its predecessor, SQL Server 2008 R2 added features like Master Data Services and Multi-Server Management. It was the last version to run on the Itanium platform.SQL Server 2012 (2012) – Version 11.0
Introducing Always On SQL Server Failover Cluster Instances and Availability Groups, SQL Server 2012 focused on improving database availability. It marked the last version to natively support OLE DB.SQL Server 2014 (2014) – Version 12.0
Extended Support End Date: Jul 9, 2024 – MSSQL 2014 Lifecycle
With in-memory capabilities (Hekaton) and improved Always On solutions, SQL Server 2014 (12.x) enhanced performance and disaster recovery. It was the last version available on the x86/IA32 architecture, and we got Transparent Data Encryption (TDE).Currently Supported SQL Server Versions
SQL Server 2016 (2016) – Version 13.0
Extended Support End Date: Jul 14, 2026 – MSSQL 2016 Lifecycle
SQL Server 2016 (13.x) brought the introduction of Query Store for monitoring and troubleshooting performance issues and the SQL Server R Service integration. We also got built-in JSON support for imports, exports, parsing, and storage, PolyBase Query Engine Integration for SQL Server with external data in Hadoop or Azure Blob storage, and the Stretch Database feature for archiving data to Azure SQL database dynamically, which has already been retired.This release marked the beginning of Microsoft’s shift away from Service Packs, and brought support for x64 processors only. Security feature improvements in SQL Server 2016 included Always Encrypted, Dynamic Data Masking to hide masked data from most users, and Row Level Security. One other note-able change with SQL Server 2016 was that database compression for backups is now usable with the Standard Edition rather than it being an Enterprise Edition only feature.
SQL Server 2017 (2017) – Version 14.0
SQL Server 2017 (14.x) was the first release that included Linux support, expanding the platform’s versatility. Other MSSQL 2017 Feature releases include Automatic Database Tuning, Scale Out for SSIS, and SSRS supports OpenAPI compliant RESTful APIs.During this release SQL Server R Services is renamed to SQL Server Machine Learning Services, reflecting support for Python in addition to R. Python ML and AI libraries become available, including revoscalepy, microsoftml, and Python operationalization with T-SQL.
SQL Server 2019 (2019) – Version 15.0
Introducing Big Data Clusters, In-Memory Database technologies, and other various improvements, SQL Server 2019 (15.x) further expanded its capabilities, addressing some of the evolving needs.Using the PolyBase feature, users are now equipped with the ability to integrate and query data from diverse external sources, including SQL Server, Oracle, Teradata, MongoDB, and ODBC, using external tables.
SQL Server 2022 (2022) – Version 16.0
SQL Server 2022 (16.x) introduced Azure Synapse Link for SQL, enabling real-time analytics with Azure Synapse Analytics, object storage integration supporting S3-compatible storage, and Data Lake Virtualization. This means we can use CREATE EXTERNAL TABLE AS SELECT (CETaS), a feature designed to help archive and store cold data in Azure Storage (saved in Parquet). This is what we use instead of the deprecated Stretch Database feature.Beginning with MSSQL 2022, runtimes for R, Python, and Java, are no longer installed with SQL Setup. Instead, install any desired custom runtime(s) and packages. This release also removes SQL Server Native Client – watch out for this one, I’ve spent hours troubleshooting an issue only to find that it was due to this client being needed.
SQL Server 2025 (2025) – Version 17.0
At the time of writing, SQL Server 2025 is not yet released and is in early preview. We have this SQL Server 2025 announcement post from Microsoft which contains all the things to expect.Cloud capabilities expand with Microsoft Fabric Database Mirroring and Azure Arc connectivity, enabling more hybrid management styles. We should really benefit from Copilot in SSMS for coding assistance, I hope. I also wonder where the Microsoft Entra Integration will go, if my world will change with that at all.
I made a blog post for SQL Server 2025 which I’ll update with good notes I learn along the way. So far, all I know is what’s available via online search.
Wrapping Up; Keep Informed & Software Updated
SQL Server will always be my primary focus throughout my career. While it’s impossible to know everything, dedication and continuous learning, especially with the help of Microsoft Docs, can make us great SQL Server Database Administrators.
One common challenge for businesses is keeping SQL Server up to date. This includes upgrading to stay within Microsoft’s Extended Support Periods for security and regularly applying the latest Cumulative Updates (CU) to ensure optimal performance and stability.
I hope you enjoyed my take on this, and no doubt there’s some new information for any reader in this post with all the links to MS Docs & Wikipedia!
-
SQL Server Script: Enable or Disable All SQL Agent Jobs
SQL Server Agent jobs automate tasks within SQL Server, but there are times when you need to enable or disable multiple jobs, such as during maintenance, troubleshooting, or system upgrades. This guide provides a script that generates SQL commands to enable or disable all SQL Server Agent jobs by changing a flag in the script.
Enable or Disable SQL Server Agent Jobs with a Single Script
The script below iterates through all SQL Agent jobs and generates commands to either enable or disable them. The
@enabledflag determines whether jobs are enabled (1) or disabled (0).
Execution Steps:
1. Set@enabledFlagto1to enable jobs or0to disable them.
2. Run the script in SQL Server Management Studio (SSMS).
3. Copy and save the output (you want to keep a copy of what you’re disabling).
4. Execute the generated commands to apply the changes.
In the example above, we have the refreshed SQL Server Agent job list in the SSMS Object Explorer which shows all jobs are disabled.
Why This Script is Useful
This script to generate SQL for disabling/enabling Agent Jobs is particularly useful in the following cases:
Always On Availability Group Failover
In environments with Always On Availability Groups (AG), each SQL Server instance may have its own set of SQL Server Agent jobs. When failing over from the primary to the secondary replica, it’s essential to disable jobs on the secondary to ensure that only the active server executes them. This prevents duplicate job executions and maintains smooth operations during failover events.Server Builds, Migrations, and Maintenance
During server builds, migrations, or maintenance activities, it’s often necessary to temporarily disable all SQL Agent jobs to avoid execution conflicts. This ensures that critical tasks, such as database maintenance or testing, proceed without interference. Always remember to back up job configurations before making any changes, especially in environments with a large number of jobs.Patch or Upgrade Processes
When performing patches or upgrades, it’s crucial to prevent automated job execution that might interfere with the process. This script helps maintain control over job execution, minimizing the risk of issues during system updates.Testing and Development
For test environments, this script provides a quick way to disable SQL Agent jobs, ensuring that no maintenance or background tasks disrupt your testing or development procedures.
By customizing this script, amending the
@enabledFlagfrom 0 to 1, we can use it to enable jobs instead of enabling them. We can also customize further to filter out any key jobs you don’t want to change – this would require an additional condition at line 9 in the script, using the columns in thedbo.sysjobssystem table.We can also use a SQL Script to list all SQL Agent jobs in SQL Server for a more detailed review of all jobs including their schedules.
Remember to backup the list of jobs you are disabling, by copying the generated SQL to a file. I hope this post was useful for you and what you needed!
-
Database cannot be opened, it is in the middle of a Restore
Database '[DatabaseName]' cannot be opened. It is in the middle of a restore.
If you encounter this error in SQL Server, it means the database is still in the RESTORING state, typically because:
> A restore operation is still in progress.
> A previous restore failed due to corruption or other issues.
> The database is inNO RECOVERYmode and waiting for additional restore steps.When in this state, most operations (including queries, alterations, and drops) will fail unless the database is fully restored.
The Fix: Bring the Database Out of Recovery Mode
To bring the database online and make it accessible, execute the following SQL command:
USE master; GO RESTORE DATABASE [DatabaseName] WITH RECOVERY;
This finalizes the restore process, making the database operational.
Still Having Issues?
If you were getting this error message while trying to drop the database, once the database has been restored to the Online state we can proceed with the drop. If you’re still having issues dropping the db, you could then try run a script to kill all active sessions on the database, if there are any.
Is your database now In Recovery mode? Check out my other post on this as linked which provides more information on troubleshooting this.
Additionally, consider these troubleshooting steps:
> Check the SQL Server Error Log for any underlying issues preventing recovery.
> Verify Backup Integrity to ensure your restore source is not corrupted.
> Restart SQL Server Services if the issue persists after executing the restore command.If none of these solutions work, feel free to comment below with details, and I’ll try help troubleshoot!
