In my post entitled “Testing and backing up is (not) hard to do” I expressed the need for people to test or create database copies and backup their databases to avoid data loss. In that article I focused on SMS 2003 and now I would like to turn my focus onto SQL 2005.
As I mentioned in the previous post the SMS 2003 database and backup procedures are not much different than its predecessor. The same can be said of SQL 2005 because it is not much different that its predecessor for creating test databases.
Note: To create a test database you can also refer to the previous articles links for my posts from the past.
The first step is to create the database. There are however several ways to create a database. You can use a SQL query or a script such as a WMI or Vbs script or simply import an existing databases data into a new database “shell”
The easiest way to accomplish this is to use the SQL Server Management Studio interface by right mouse clicking on the databases folder from the object explorer and select New Database. At this point the new database wizard is started and brings up the new database screen.
From the general tab you specify the database name as well as the owner of the database. The database files is where you specify additional options such as the initial size of the database and can set the autogrow properties for the newly created database.
On the options tab you set the collation, recovery model and set the compatibility level. You are also given the chance to further fine tune the database by modifying the other options from the bottom pane. In the final tab you find the filegroups screen for the database.
Note: If you want to see how the existing SMS or MOM database is set right mouse click the database and select properties and the database properties screen will be displayed providing you with all that databases configuration.
Once you have your database shell created and configured you need to create the tables to put your captured or manually entered data into. If you simply want to make a copy of your SMS or MOM database there is no need to create the tables manually with SQL 2005. You can simply use the copy database option from the task menu and you will have an exact copy of the existing database as a new database on your test server.
The easiest way to create a new table in your database is to use the new table wizard which is found when you select your database and right mouse click on tables and select new table to begin the new table wizard.
First you enter the name of the new table in the Column name row and then tab over and set the Data type for the column. By default the Allow nulls is check but you can uncheck the box if you do not want that option set. Continue doing so until all the tables you require are created.
No Comments