Find Projects To Backup Before a Refresh

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 top

Leave a Comment

Yay! You've decided to leave a comment. That's fantastic! Please keep in mind that comments are moderated and rel="nofollow" is in use. So, please do not use a spammy keyword or a domain as your name, or else it will be deleted. Let's have a personal and meaningful conversation instead. Thanks for dropping by! Your email address will not be published.