Tuesday, August 23, 2016

when were databases last restored

    SELECT A.restore_history_id
         , A.restore_date  --date database was restored
         , A.destination_database_name
         , A.user_name
         , A.restore_type
         , B.backup_finish_date --date of backup used to restore database
         , C.physical_device_name  --backup file used to restore database
         , A.stop_at
         , A.stop_at_mark_name
         , A.stop_before
      FROM msdb.dbo.restorehistory A
 LEFT JOIN msdb.[dbo].[backupset] B
        ON A.backup_set_id = B.backup_set_id
 LEFT JOIN msdb.[dbo].[backupmediafamily] C
        ON B.[media_set_id] = C.[media_set_id]
     WHERE A.restore_date 
         = ( SELECT MAX(A1.restore_date)
               FROM msdb.dbo.restorehistory A1
              WHERE A1.destination_database_name = A.destination_database_name )

Thursday, August 11, 2016

Execute ps1 files at command line

1. Open regedit.exe

2. Export copy of the registry

3. Navigate to

HKEY_CLASSES_ROOT\Microsoft.PowerShellScript.1\Shell

4.  Change the default from 'Open' to '0'

5. Could change default parameters to powershell.exe in HKEY_CLASSES_ROOT\Microsoft.PowerShellScript.1\Shell\0\Command


Another option:
3. Navigate in registry to HKEY_CLASSES_ROOT\Microsoft.PowerShellScript.1\Shell\Open\Command\

4. Change Old Value:
"C:\Windows\System32\notepad.exe" "%1"

to New Value:
"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -NoLogo -NoProfile -NonInteractive -ExecutionPolicy "Bypass" -File "%1"


Why not do this:

This makes it easy for dangerous .ps1 files to be executed.  With great power comes great responsibility.

Wednesday, August 3, 2016

error accomodating a copy of the model database

Problem: Error encountered during installation of vendor software package that includes creation of database

Symptoms: Error message similar to "37000-1803: [Microsoft] [ODBC SQL Server Driver] [SQL Server] The CREATE DATABASE statement failed. The primary file must be at least XXXX MB to accommodate a copy of the model database."

Cause: Vendor has specified a database size in their create database script that is smaller than the size of the "model" database on that instance.

Solution: Shrink model database