SQLTeam.com | Weblogs | Forums

Bakcup Sql server 2005 excluding certain users

sql2005

#1

We have an application called Finalbuilder that we use to deploy DB to other SQL servers. There is ADO executesql command in it like below

BACKUP DATABASE %DB_NAME%
TO DISK ='%DBFolder%%BAK_Targetname%'

I want to exclude certain users when the backup is done. I do not see any options or parameters in MSDN to exclude certain users when backing up. How can i do this?


#2

Not quite sure what you meant by "exclude certain users". Did you mean that when you restore the backup, you don't want to see certain users in the restored copy? If that is what you are asking, SQL backup does not have a provision for that.

There would be some issues with doing such a backup, even if that were possible. For example, users can own schemas and schemas can own objects. So if you exclude some users from the backups the schemas will become orphaned.

If you want to remove the users, it is easy to drop the users after you restore the backup.


#3

Thanks @JamesK. That is what I was asking. So there is no option within the RESTORE command to drop USERS and it has to be dropped after the DB is restored, correct?


#4

Yes, that is correct. You can append the script to drop the users to the restore script and run it.

Alternatively, you can take a backup, restore it to a temporary area, drop the users, and take a backup of the temporary database if that proves to be more convenient.