SQL Server 2000 Restricted Modes
Use Restricted Modes to Limit User Access During Backup and Recovery Operations
Description:
This exercise will discuss how to restrict users from accessing the database while you are performing backup and recovery operations. You must have exclusive access prior to restoring a database, and you may want to restrict immediately following a restore to prevent users from having access before the DBA has gone through all of the checks to make sure that the restore is complete to the DBA's satisfaction.
(back to top)Requirements:
- As with all of the exercises on this site, do not perform this exercise on a production server. Perform this exercise in a closed development environment
- You will need to have a backup device already created - for the exercise on creating a backup device, click here
Setup:
None.
(back to top)GUI Steps:
1) I am going to show a brief example of how to restrict database access using Enterprise Manager. The reason that this is a brief example is because it is imperfect, and you are better off using T-SQL to change the database access. To start, put the database into single user mode, you can do the following. Right click on the Northwind database in Enterprise Manager and choose "Properties".
2) Go to the options tab and select the "Single User" radio button in the Access portion of the screen. Then click the "OK" button. The database is now in single user mode, you will have sole access to the database. The problem with this method is that you can't terminate existing connections, and if there are already existing connections, you will get an error message letting you know that you can't restrict access while users are connected to the database.
(back to top)
T-SQL Steps:
1) From Query Analyzer, insert a row into the shippers table.
2) In Query Analyzer, backup the database using the BACKUP DATABASE command.
3) We need to determine which backup we need to restore. Run the RESTORE HEADERONLY statement on the verifytest device. We only have one backup on the restrictingdevice device, so file number 1 is the file number that we are going to check to make sure it is a good backup.
4) Now we are going to have to make sure that we have exclusive access to the database. The following command will kill all connections to the Northwind database except for the connection that we currently have to the database.
If you have existing connections, you should get a result similar to this in the results pane: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." I should also note here that you could also run an exec sp_who to find out who is in the database, then use a KILL command to kill the processes individually. This would take longer, but you would be able to contact the users directly to inform them of your operation.
5) We are now going to restore the database. Note, even though we were in single_user mode before we performed the restore, a restore will open the database up to all users, effectively putting the database into multi_user mode. To combat this, we use the "RESTRICTED_USER"clause so that only administrators have access. This will allow administrators to run their checks to make sure that the database has been fully recovered to their satisfaction.
6) Since we are an administrator. In our highly simplified example, we are going to run a query against the database to make sure that the Insert we made in this exercise has been successfully restored.
7) We are satisfied that we performed a full restore from the query results in the previous step. We will now open the database up to the users by running the following statement.
(back to top)