Join Adam Wilbert for an in-depth discussion in this video What you should know, part of Database Foundations: Administration.
- This course is picking up from where we left off at the end of Database Fundamentals: Storage. In order to make sure that everyone is on the same page, I'd like to refresh the database files that we've been working with. If you haven't, come over here in your optic explorer inside of SQL Server Management Studio and right click on the database called H+Active and choose delete from the menu. I'll go ahead and make sure that this check box down here is active, to close the existing connections, and we'll go ahead and say OK. That'll remove the database from my system, and now I'll go ahead and recreate it.
I'll right click on Databases and choose New Database. We'll give the database a name of H+Active and then press the OK button down on the bottom. Next, I want to populate this database with some tables and data. In order to do that, I'll select it from the list here and press the New Query button here. In the exercise files, in the Chapter 0 folder, there's a file called DatabaseBackup.txt. Go ahead and copy that to your clipboard and then inside of the SQL window, I'll press Ctrl-V to paste all that text in.
Now we can scroll through this here and see what's going to happen here. If I go up to the top, we'll see we've got some CREATE TABLE statements up here, and then if I scroll down a little bit further, we'll see we have some INSERT statements where we're inserting data into these tables. Let's go ahead and execute this query here and when we get the messages down here that say 1 row(s) were affected, we know that the script executed successfully, I can come back over here to my database, right click on it and choose Refresh, and then take a look inside of the tables and see that we have some new tables here. I can right click on one of the tables and say Select Top 1000 Rows to see that we also have some data.
So now everyone has a clean and consistent copy of the database and we're ready to go.
Note: This course will also prepare certification candidates for the "Administer a database" domain of the Microsoft Technology Associate (MTA) Exam 98-364, Database Administration Fundamentals.
- Securing the database server
- Understanding Windows authentication vs. SQL Server authentication
- Assigning fixed server roles and fixed database roles
- Granting object-level permissions
- Understanding ownership claims
- Creating backups
- Restoring a database