Showing posts with label SQL/Oracle. Show all posts
Understanding Microsoft SQL Server 2005 System Databases
Microsoft SQL Server 2005 System Databases
A database server application such as Microsoft SQL Server 2005 uses a lot of information in order to operate. Considering the purpose of a database is to store information, it only makes sense that SQL Server dips into its own technology for storing and accessing the information required for operating in a set of its own databases. Each of the databases used plays a specific role in the operation of the SQL Server. The system databases use the same components of databases created by users with: tables, views, stored procedures, and other database objects
Microsoft SQL Server 2005 uses five system databases:
- master – uses master.mdf for data and masterlog.ldf for logging
- model – uses model.mdf for data and modellog.ldf for logging
- msdb – uses msdbdata.mdf for data and msdblog.ldf for logging
- resource – uses mssqlsystemresource.mdf for data and mssqlsystemresource.ldf for logging
- tempdb – uses tempdb.mdf for data and templog.ldf for logging
A default installation of SQL Server stores all the system databases under
Here’s a Closer Look at Each System Database …
The master Database
The master database stores all the system-level information for SQL Server. The data stored by the master database includes information for: configuration settings, logon accounts, linked servers and endpoints, user database file locations, and properties. Due to the nature of the data stored, SQL Server cannot operate without the master database. So it is a very good idea to backup this database after changing the SQL Server configuration, modifying, adding, or removing any databases.
The model Database
SQL Server uses the model database for creating new databases. When the “create database” statement is used, SQL Server copies the contents of the model database to the newly created database. If there are any common database objects that would prove useful in all databases created by SQL Server, it is possible to add those objects to the model database. Then when a database is created by the SQL Server instance, the user defined objects will be copied to it along with the default objects. Since SQL Server recreates the tempdb database every time it is started, the model database is required in order for SQL Server to start. The msdb Database
The msdb database is used by SQL Server to store information on operations performed by SQL Server. This includes information for: the SQL Server Agent, Database Mail, the Service Broker, log shipping, backup, and maintenance plan job parameters. The resource Database
The resource system database was introduced with SQL Server 2005. This database is used for storing all the system views and stored procedures. Logically, each SQL Server database will contain all these system objects, however, they are physically stored within the resource database. The resource database is read-only and does not include any user data. In previous versions of SQL Server, the system objects were stored in the master database. The motivation behind moving the objects to a separate database is to make updating the SQL Server more efficient. Improvements and fix-ups to the SQL Server system generally manifest mostly on the system objects. A separate database to store the system objects reduces the number of files that need to be replaced with an update.
The tempdb Database
As the name implies, SQL Server uses the tempdb database for storing temporary data and data objects. The tempdb database is used when an operation requires a temporary table, stored procedure, or other database object to be performed. Intermediary data for large sort operations is also stored in the tempdb database as well as temporary data for internal SQL Server operations. Every time SQL Server is restarted, the tempdb system database is recreated thus clearing any temporary data stored during the last SQL Server session. In cases where a high volume of users and operations are performed with SQL Server the tempdb database can grow to use a significantly large amount of disk space. It is important to plan accordingly in these scenarios since running out of disk space where the tempdb database is stored will have catastrophic effects on the operation of SQL Server.
System Database Maintenance is Essential …
SQL Server’s system databases are crucial to the operation of SQL Server. If any of the system databases were to become corrupted, chances are SQL Server will no longer be able to function. For this reason it is just as important (if not more important) that you backup the system databases as regularly as the user databases. This applies to all the system databases except for the tempdb and resource databases. Users are restricted from performing many operations on the system databases such as: changing the database owner, dropping the guest user, or mirroring. The databases cannot be dropped or set to offline, nor do the database files support renaming.
Tag :
SQL/Oracle
TrainSignal Advanced SQL Server 2008 Database Administration
SQL Server 2008 Database Administration
If you want to develop hands-on experience as an SQL Server 2008 Administrator, or prepare for the 70-432 exam then this course is for you! We are excited to announce our long awaited SQL Server 2008 Administration Training course.
Download Links :-
DVD1
SQL Server 2008 Database Administration Part 2
SQL Server 2008 Database Administration Part 3
SQL Server 2008 Database Administration Part 4
SQL Server 2008 Database Administration Part 5
SQL Server 2008 Database Administration Part 6
SQL Server 2008 Database Administration Part 7
SQL Server 2008 Database Administration Part 8
SQL Server 2008 Database Administration Part 9
DVD2
SQL Server 2008 Database Administration Part 2
SQL Server 2008 Database Administration Part 3
SQL Server 2008 Database Administration Part 4
SQL Server 2008 Database Administration Part 5
Tag :
SQL/Oracle,
Train Signal
How to Create a Simple Backup Job in SQL Server
Create a Simple Backup Job in SQL Server
There are several ways to create a backup job in SQL Server 2008 and I’m going to show you how to create a very simple one for both a single database and for multiple databases. And since there are so many ways to do this, I’m going to briefly discuss most of them at the end of this article so you’ll have a good idea of what other options you have.
First you start by opening a query window in SSMS and connect to the server you want to backup. It’s worth noting that it doesn’t matter which database you’re connect to, but I typically do it from master just to be consistent.
The basic backup syntax that you need to type is here:
BACKUP DATABASE databaseName
TO DISK = 'file location'
OK, let me explain a couple of these items.
databaseName – this is the name of the database you want to backup.
To disk = 'file location' – Backups are written to files. This is the full path to the file. Typically, a full backup such as this one will have the extension of .bak, but that’s just what everyone does. It can have any extension you like or no extension at all if you like.
Now let’s see a real example with all of the values filled in. Here we’re going to backup the Model database to a file called ModelBackup.bak on C:\.
BACKUP DATABASE Model
TO DISK = 'c:\ModelBackup.bak'
backup database Model
To disk = 'c:\ModelBackup.bak'BACKUP DATABASE MODEL
To disk = 'c:\ModelBackup.bak'bacKup DataBasE ModeL To dIsk = 'C:\MODELBACKUP.BAK'
However, you don’t want to backup your database by hand every time so you want to schedule this to run on its own. And for this you’re going to put the above code into a job and schedule it. The easiest way to do this is to create a job in SSMS. Follow these steps to create the job and put your backup statement inside of it.
Expand the ‘SQL Server Agent’ tree and right-click on ‘Jobs’. Then choose the top item, ‘New Job…’
Now you’ve got the new job dialog box. Filling in the info is pretty easy. You need to give your job a name, and everything else is optional. Here I’m going to fill in the name of the job as ‘Backup user database’.
Next click on the ‘Steps’ pane on the left and you’ll be presented with this screen. It’s blank because you haven’t created any steps yet. So go ahead and click on the ‘New’ button at the bottom.
This is where the real magic happens. Again, you have to fill in a name so you know what the step is called. Make it something descriptive. There are several step types to choose from, but the default is T-SQL and since we’re running a T-SQL command that’s clearly the one we want to go with. The database defaults to ‘master’ and that’s just fine with us. Here’s what we have so far. You see the only thing we’ve had to do is fill in the step name.
The only thing left to do is to copy your backup statement into the query window. Of course it’s always a good idea to make sure your code will parse before you try to run it. Just click the ‘Parse’ button I’ve circled. Here’s what that looks like.
Now click OK and it’ll take you back to your new job window and now you’ll see your job step listed. And you can stack as many as you like inside there.
To schedule your job, just click on ‘Schedules’ on the left and then choose the schedule that’s right for you. It works similar to the the way it does in Windows so there’s really not much need for me to rehash it here.
Once you click OK all the way out until the new job box is closed, your job will be added to the job tree. You may have to right-click on ‘Jobs’ and refresh the display for it to show up.
Now that you’ve created the job to backup your database I’d like to go back to the command again and add one more part. See, when you backup a database to a file it won’t overwrite the file by default. By default it will append to the file, so your backup file will just get bigger and bigger. So what you want to do is add a special flag to the command that tells it to initialize (or overwrite) the file every time. That makes our backup command look like this
now:
BACKUP Database Model
With INIT
This is a good thing to keep in mind while you’re creating your job.
But what if you wanted to backup more than one database? Well, again, there are several ways to do this and I’m going to show you the easiest just to get you going.
There are 2 methods we’re going to discuss here:
1. In the job step you just created, you can just stack backup commands one after another. So the window that holds your backup code inside the step could easily hold the code for several databases. That would look something like this:
2. The other way you could do it is to add each one of your backup commands to their own step by repeating from step 3 above.
Here I’ve shown you how to create a simple backup job. Most tutorials will show you how to use the backup wizard, but that’s ridiculous because you’ll never do that in production. Nobody wants to manually backup their databases every day.
BACKUP Database Model
To disk = ‘c:\ModelBackup.bak’
This is a good thing to keep in mind while you’re creating your job.
But what if you wanted to backup more than one database? Well, again, there are several ways to do this and I’m going to show you the easiest just to get you going.
There are 2 methods we’re going to discuss here:
1. In the job step you just created, you can just stack backup commands one after another. So the window that holds your backup code inside the step could easily hold the code for several databases. That would look something like this:
2. The other way you could do it is to add each one of your backup commands to their own step by repeating from step 3 above.
Here I’ve shown you how to create a simple backup job. Most tutorials will show you how to use the backup wizard, but that’s ridiculous because you’ll never do that in production. Nobody wants to manually backup their databases every day.
Tag :
SQL/Oracle