Find Projects To Backup Before a Refresh

• Created: July 14, 2016
• Last Modified: July 25, 2016

This script will list all of the recent projects in a non-production environment, that may need to be backed up before a refresh.

If your development environment is about to be refreshed with a copy of production, you may have the same nagging feeling I get: what if I have a development project that I forgot to back up.  True, you can have your admin dba backup development before the refresh, but restoring the backup may not be time well spent for your admin dba.

I built the following script to show all project definitions, in descending order of last touched, to show me which projects I may want to backup on my own.  It also has the last target database that was used.  If this field is blank (Last Target DB), and the project name is important work, you will want to pay close attention to these.

SELECT P.LASTUPDDTTM, P.PROJECTNAME, P.PROJECTDESCR, P.LASTUPDOPRID, O.OPRDEFNDESC, P.TGTDBNAME "Last Target DB", COUNT(*) "Count of Project Items"
FROM PSPROJECTDEFN P LEFT OUTER JOIN PSOPRDEFN O ON O.OPRID = P.LASTUPDOPRID, PSPROJECTITEM I
WHERE P.PROJECTNAME = I.PROJECTNAME
AND P.LASTUPDDTTM >= '04/20/2016'
GROUP BY P.LASTUPDDTTM, P.PROJECTNAME, P.PROJECTDESCR, P.LASTUPDOPRID, O.OPRDEFNDESC, P.TGTDBNAME
ORDER BY P.LASTUPDDTTM DESC

↑ Back to topShare on Twitter