Is it possible to delete data older than X days from a SQL 2014 database?

Hi, we are in the process of having our website re-developed and as part of the integration they will require a copy of our SQL database. This is to ensure that all of the functionality works correctly and that there will be no issues when the website is linked to our production database at launch.

We trust the web agency implicitly, they have signed a Non-disclosure agreement and we know they will not use any of the data in our database to their advantage but as a safeguard - our Directors have asked me to remove any data that has been added in the last 12-24 months.

The majority of this data consists of candidate CVs and client records - is it possible to delete all data from a number of different tables that is older than 18 months for example?

I am more than happy to release a copy of the data for the purposes of the website re-development, but ideally I would like to satisfy our Directors wishes and protect ourselves.

I look forward to hearing your thoughts and comments.

Thanks in advance!
Rob SamuelIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
HI,

You can delete the records table by table if you have datetime column in your transaction table.

Ex. Delete from Table where Transactiondate > '2014-01-01'

or the date you want.

You can create dynamic script for all your tables if then have some transaction date column.



SELECT 'DELETE FROM ' + NAME + ' WHERE TRANSACTIONDATE > ''2014-01-01''' FROM SYS.tables

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
For how long that you keep the backups? If you still have backups older than 12 or 24 months just provide them those backups and when they restore the database it will have only old data.
Another option is to mask the data but for that you'll need a 3rd party software and usually they  aren't free.
Jim HornMicrosoft SQL Server Data DudeCommented:
>they will require a copy of our SQL database
Just to be safe it would also be an excellent idea to remove all personal information from the database before giving it to the vendor.  See SQL Server:  T-SQL recipe to create a million sample people to create sample people, then you can add T-SQL to update your tables with this sample people to remove all personal information before giving it to the vendor.  I've used this method for a couple of clients for use with vendors, offshore developers, and QA with success.

>We trust the web agency implicitly, they have signed a Non-disclosure agreement
Ok, but not a rock-solid guarantee that this data won't be leaked elsewhere..
Scott PletcherSenior DBACommented:
Also keep in mind that they don't have to deliberately leak it, they could be hacked or have a rogue employee.

One tricky aspect would be foreign keys.  Unless you have CASCADE set (and most people don't, for good reason), you'd have to delete from the lowest child table up to the parent table, in order.  If you need it, I can provide code that will tell how you how deep any FK nesting goes for each table.  Then simply DELETE from highest nesting level to lowest.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.