Hello SQL Folks.
Being a MVP gives me the chance to
freely use and test some fancy tools and this time I would like to share my
thoughts about one of such. Today I would like to bring your attention to
the Stellar SQL Database Toolkit.
Stellar SQL Database Toolkit
The tool is
able to help you in three types of problems:
There is a
good explanation in what business scenarios the toolkit is really helpful so I
strongly recommend you to visit the page: http://www.stellarinfo.com/sql-database-toolkit.php.
the program then. The initial screen looks like on the picture below. You can pick one of the three mentioned
I started with the SQL Password Recovery as this particular option was the
most interesting to me. The idea behind that is if you accidentally lost a
password (could be sa password or
any user password) than you would be able to change it.
prepared a login (Login4Recovery) that I am going to check. I stopped my SQL
Server instance and copied the master data file into other location.
As you can
see the list of all logins is displayed. I selected the “Login4Recovery” and
pressed the Change Password button. I typed a new password and that is all!
I moved the
master data file to its previous location and restarted SQL Server. I tested if
the operation was really successful by using the new password to my
Login4Recovery login. Everything went fine. But – you should ask yourself a
question in what circumstances this operation should ever be done.
option I have tested was Database Repair.
I took a sample database that has a corruption that prevents me from
attaching it to the server:
the tool and the first information is as follow:
sense as it is hard to perform any operations on database files if those are
under the control of the database engine. The only way is to take the data file
or files away from the SQL Server.
The next step is to select the data files. There are two
possible ways to do so as you can see on the picture below:
You can point in to the database
You can search for it
When you try to use the “Search in Folder” option that you
specify the location (“Look in”
select the file type
optionally select the “Search in
The search operation is started when you press “Find”
button. As you can see on the screen there are 28 primary data files found on
my laptop. That does not mean all of them are corrupted. The tool finds all
data files and it is up to you to check if they are corrupted or not. This step
is just for selecting a file to be examined.
What is really worth to notice is the last checkbox
“Include Deleted Records”. Checking this
options allows the tool to search for deleted records and possibly to recover
them. I have selected the option before going to the next step.
I started with the first option (“Select Database”) and
clicked the “Repair” button.
Now the application started to work out and after a while
I was informed that it is possible to repair my database. The applicaton
presented the short report of all actions it had taken.
It is possible to take a look at the database, examine all
objects and even to see the data. You can even see that for the table
HumanResources.Employee two entities are displayed:
That means that for this particular table it is possible
to recover the deleted records.
At this point it is possible to repair and save the
database (means also to attach) to the given instance of SQL Server. Remember
that you can pick (by selecting the checkboxes) only these objects you really
need to be saved.
To do it you have to press “Save” button from the ribbon:
You can then decide how the repaired database be saved.
There are 4 options to choose:
I have selected “MSSQL”. Now I had to choose how the
database is saved:
New database means that the restored file will be created
on the given server. In that case you need to specify the server and instance
name (or better – to select them from the list) . Then you need to decide where
the data file is located – that could be the default location for the instance
or new one.
After you press “Connect” the database is going to be
created on the server and data are copied:
magic happened J The
database is up and running!
interested how the Live database option works? You have to specify the server name
and an existing database. The recovered data will be saved to the database
I was also
wondering how other save options work. For example the option to save data to
the CSV file:
configured the file location to be stored on my local drive and after clicking
“OK” the fileswere created. You will notice that under the AdventureWorks_Data
folder a new folder is created. It will be created each time you perform the
There are a
lot of files in this directory – one file for each object like a table or a
one of the file just to check if it is properly formatted:
Now I can
see that the tool can be also used to easily export data in some particular
back to the application once again. The very last thing to mention are the
other options that are available in this Database Repair tool:
There are a
lot of options on the top menu, but please check the one described as KB –
clicking on this option will open a new browser window and you will be moved to
Stellar Phoenix Knowledge Base. You can
also register your product, purchase new ones or for example update it:
Take a look
also on the “Find Tree Item” search. That will search through all objects so
you can very quickly navigate to them. The “Match case” and “Match whole word”
check boxes are very intuitive and most of us are familiar with these options
form other tools so I so not spend time describing them.
functionality offered by the Stellar
SQL Database Toolkit is the SQL Backup Recovery. As you can see the
options are very similar to the previously described SQL Database Recovery.
two options of searching for a database backup:
a) Selecting a file
b) Searching in a directory
already described both options. I have selected a damaged database backup file and
tried to fix it
have to pick a backup from all that exist in the file. In my case it was just
one backup in the backup file but it is nothing strange to see more then one. I
will not deliberate about backups in this post but I assume you know which one
has to be restored first.
After I had
pressed the “Next” button I saw an information that the backup can be fixed by
There is a
chance to review the objects and data
that exist in the backup file. You can take a look on the tables, views and
other objects and see the data or the object’s definition. You can pick only
the needed objects to be restored by checking the checkboxes on the left side
of the objects:
point you have to decide what to do with the recovered file. There are options to
save it to a database (for example as a new one or to overwrite the existing
one) or to other types of files.
with restoring the backup as a new database on one of my instances. The idea is
exactly the same as previously when I was describing the data file fixing.
Simply fill the necessary fields:
a) Server \ instance name
b) File location
the “Connect” button and the restoring process is taking place. If everything
is fine you should see a confirmation message at the end of the process and the
database is restored.
As you see
the database is now present in my instance.
I have also
tried to save the objects to the CSV file. This is one of the options you can
select before the file is saved.
was created exactly as I expected
I like the
tool. It is very intuitive and does not
ask to many questions. It definitely be a helpful in many cases for bot
experienced and novice DBA’s. What I would really like to have in the tool in
the future would be the possibility to have more description in the log file of
the operation that are in progress. I know that this could be a very technical
description but I think people would like it. I would!