How to backup SQL database without users and stored procedures

Question

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,

What kind of database are we talking about? mysql, postgresql, sqlite, sql server, oracledb? — gnur, May 27 '15 at 07:00
Oops. Sorry forgot to mention it. @Markus its MS SQL 2008 R2 — Narbeh Davoodian, May 27 '15 at 07:52

score 1 · Accepted Answer · edited May 23 '17 at 12:41

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

Back up the database.
Restore it with a new name.
Delete the users and stored procedures out of the new database.
Back it up again to a new file.
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'

How to backup SQL database without users and stored procedures

1 Answers1