Sql – How to backup SQL database without users and stored procedures

backupsqlstored-procedures

I have a database which i need to take backup monthly / or by running an script.
I need to backup the data and structure only. I don't want to backup stored procedures and users for example…

I have tried "generating script" … but i cannot restore to a test database to achieve my goal.
The goal is to pass the backup file to another company so I don't want them to have the stored procedures…
Is there any way to do this?

Thanks,

Best Answer

Unfortunately, no. You're probably going to have to:

  1. Back up the database.
  2. Restore it with a new name.
  3. Delete the users and stored procedures out of the new database.
  4. Back it up again to a new file.
  5. Send the resulting backup file to the other company.

You can use this query (found on Stack Overflow) to find the procedures:

select * 
  from DatabaseName.information_schema.routines 
 where routine_type = 'PROCEDURE'
Related Topic