SQL Server: Backup/Restore to/from NAS Shares with Username/Password

Traditional Permanent NAS Share We setup NAS shares all the time for SQL Server backups and the procedure is quite simple. Find the SQL Server Engine Service account Grant full-control privileges to the share for the account Restart the SQL Server Engine Service for permissions to take effect (if needed) #Get the service account name(s). …

Advertisement

SQL Server: DROP All But A Few Tables With PowerShell (or DROP ALL)

Recently, I received a request to backup a dozen tables or so tables out of 12 thousand tables. I had to retain all the indexes, statistics etc. The goal was to hand this over to the vendor for analysis as a database backup. I could have copied the selected tables over to a new database …

PowerShell: Backup All SSAS Databases To A Network Share

I am working on downgrading SQL Server Enterprise Edition (EE) s/w to Standard Edition (SE) where EE features are not used (to save money on licensing). We have a big list of these to go through. That required all SSAS databases to be backed-up to the network and restored after the edition downgrade. I will …

SQL Server – Simple Tip On Altering SQL Server Tables With Data – Avoid “Saving changes not permitted”

This is a very simple yet handy tip that most SQL Server users are not aware of. Can't alter tables with data?! If you use SQL Server, you must have seen this dialog when you try to alter a table in SSMS (like adding a new column to an existing table containing data). What does …

Using PowerShell to clone production SQL Server databases in non-production

We all know how to restore SQL Server databases from a backup but being the automation fanatic that I am, I wanted a method in PowerShell to quickly build a "clone" instance with all the databases as backed up from production. I wanted to do this with minimal number of parameters possible. The criteria that …