Siusic Dot Com

I was asked to write a SQL server job to restore nightly a database running on a server onto another server. The database on the original

server was backed-up nightly by a database maintenance plan. So the job I was going to write was to find the latest backup on the original server, copy it to the second server and then restore it on the second server.

The first thought in my mind when I wrote this job was that I could use the xp_cmdshell system store procedure to find the latest backup file name. I later found that there was another system procedure that could do that more conveniently. That was sytem store procedure xp_dirtree. This system store procedure is available on SQL 2000 and up. It takes three parameters. The first parameter is the directory in which you want to get a list of files and sub directories. The second parameter is a number to indicate how deep in the directory hierarchy you want to go when listing the files and subdirectories. The default is 0 which will list all subdirectories and files recursively until all files and directories are listed. Be sure not to use 0 or default option on a large directory for example the root directory. It could take substantial amount of time and cause performance problem. The third parameter instructs the store procedure whether to include files or only folders. Default is 0 which shows folder only.

Example
exec xp_dirtree ‘c:\mssql\’, 3, 1

It lists the files and folders contained in the folder C:\mssql\ up to three levels down.
So how do we get the last backup file and copy it to the second server?

Here is what we can do

Declare @DBName varchar(255)
Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @BKFolder varchar(255)
Declare @DestFolder varchar(255)

set @DBName = ‘TestDB’
set @FileName = null
set @cmdText = null
set @BKFolder = ‘D:\BackupFoler\’

set @DestFolder = ‘\\SecondaryServer\RestoreFolder\’

create table #FileList (
FileName varchar(255),
DepthFlag int,
FileFlag int
)

–get all the files and folders in the backup folder and put them in temporary table
insert into #FileList exec xp_dirtree @BKFolder,0,1

–get the latest backup file name
select top 1 @FileName = @BKFolder + FileName from #FileList where Filename like @DBName + ‘%.BAK’ order by filename desc

–build the copy command
set @cmdText=’copy ‘+ @FileName + ‘ ‘ + @DestFolder + @DBName + ‘.BAK’

–copy the latest backup file to the secondary server
if @cmdText is not null exec xp_cmdshell @cmdText

–clean up
drop table #FileList

Now the backup file is available on the secondary server and the file name is always TestDB.BAK. A job can be set up on the secondary server to restore the backup. To go further we can also use the system store procedure sp_start_job on the first server to remotely initiate the restoration job on the second server.

I come across an article that gives a very incisive analysis on the financial crisis we are experiencing today. It finds the root causes of the financial crisis and makes suggestions on how it should be corrected. The author is YST from Taiwan. Some of the opinions he presented here may be extreme nevertheless it is a good article deserves reading.

The nature of the 2008 financial crisis:

The basic economic principles are simple to understand. Any profound economic theories have to come back to their base to be tested. Because of that anyone who knows some mathematics can have their own evaluation on the economy and the results in many cases are as good as those of professionals or even better.

We can not blindly believe in what the economist says. The very best analysis on economy is based on experience and common sense because economics is very much like psychology. The mathematical models of economics are toys in the academic society which are built upon non-existing assumption. That’s why those models can not be accurate. The economic mathematical models are frequently misleading. Basically economics is a subject that you can tell your story and I can tell mine. That’s why two economists can often reach opposite opinions on the same economic problem.

Theoretically, the United States Government is already bankrupted because it no longer has the ability to pay its debt. But in fact since the debt of the United States Government is denominated in US dollar it can pay its debt by printing money. That is the only way the United States Government to avoid bankruptcy.

There are three causes of this financial crisis:

Greedy
Inability of the government
Influence of the academic society

Greedy is rationalized:
In 1986, Ivan Boesky, CEO of a big corporation said in the graduation ceremony of Business School of University of California, “Greed is alright” It became a classic statement in the business society. Directed by greedy managers of businesses try to maximize profit in the shortest period of time and play with the laws and regulations. The fiercest and the worst are to change the laws of the government to achieve greater advantage in their game.

Financial industry has always drawn the most intelligent people because it offers higher than usual monetary reward. Think about that, a group of the most intelligent people controlling the huge financial institutions, designing all kinds of financial games to pursue short term profit and their personal interest, there must be problems.

The greedy operation of the financial industry: Financial engineering and financial derivatives:
Continue Reading »

SQL Server permissions can be granted on server, database, schema, and database object level. In SQL server 2005 the permissions can be controlled at a more granular level compare to SQL Server 2000. For example in SQL 2000 if you want to grant someone the permission to run profiler you have to put it into the “Server Administrators” server role. And that role implies other permissions. In SQL 2005 you may just grant trace to specific user using

Grant Alter Trace to Someone

To list all the permissions that can be controlled you can use the function fn_my_permission. Remember to login using an account that has sysadmin role. The function actually list all the permission you have.

This query lists all permissions on server:
select * From fn_my_permissions(NULL, NULL)

This query lists all permissions on database:
select * From fn_my_permissions(NULL, ‘database’)

This query lists all permission on the dbo schema:
select * From fn_my_permissions(’dbo’, ’schema’)

This Query lists all permission on a table
select * From fn_my_permissions(’dbo.test’, ‘object’)

What if you want to know what permissions someone has in your database? Since you are sysadmin you can impersonate anyone. The following query will impersonate another user to run the fn_my_permissions function

execute (’select * From fn_my_permissions(NULL, ”database”)’) AS USER = ‘Someone’

If a user has access to a database. The above query at least should return the following

entity_name permission_name
------------ ----------------
database CONNECT

If you don’t see that then a login can not access a particular database even when it is configured as a database user. If it happen that the database is configured as the default database for that login then it will appear that the login fails to connect to the server. It will report this message “cannot connect to [server name]. Cannot open user default database. Login failed. Login failed for user [user name]. (Microsoft SQL Server Error: 4064)

I make a living on a job and finding a good job is really important to me. Because of that how to find a good job is something I would like to know, and to get better on. It is also something I like my son, Marvin to be good at when he grow up.

But what defines a good job? A job which pays well? The answer is not that simple. For me when I look for a job a few months ago. I was thinking it would be good to find a job that paid me the same or close to my previous job, and that was close to my home, and that provided me a relax working environment without needing me to work overtime. I found a job in a regional bank and I was happy with it. But now because of the ongoing financial crisis banks were shedding work forces. The chance of being layoff is higher and at the same time work load is increased. Nevertheless I still feel good about it.

So how to find a good job? I summarized my limited experience in a few points and share them with you. The first and most important thing to remember is that to be confident and to believe that your next job will be better. It doesn’t matter how you leave your current job. May it be a layoff in which case you most likely get a good reference from your previous boss or may it be a fire in which case you may get a negative reference from your previous boss. My experience is that it would be better to have boss’ reference but it is not critical. The hiring managers usually have their own feeling and judgment towards you and will not be affected much by those references.

The second thing to remember is to learn from your interviews. There are usually two types of interviews for IT jobs, technical interviews and personality interviews. There is no right or wrong answer for personality interviews. The point is to answer the question in way that makes your hiring manager to feel comfortable to work with you. Technical interview is like an exam. It doesn’t matter if you fail this time. You can do some research to find the answer later on and prepare for it in your next interview.

The third thing is to remember the most important way to keep a good job for long is to perform well on you job function. So always learn from your job and improve your skills in your specialty. That single point will be enough to work well with most bosses. But for certain type of boss if are capable of doing more you will get more without rewarding more and the worst is that you may be mistaken as overtaking your boss if you are performing too well. So it may be necessary to play stupid sometime. On the other hand if you have a boss like this most likely your job won’t to be a good one so a better way is to get out of it.

Drop some comments if you have interesting job searching experience to share or discuss.

Different database system has different level of control on the truncate statement.

In Sybase you can grant the truncate privilege to user. In SQL or Oracle database you can not do that. In Oracle in order to truncate a table you have to have the right to drop any table which is not good in terms of security. So in order to grant the truncate privilege in Oracle you have to run this statement: “Grant drop any table to user” and you need DBA access to grant that.

In SQL Server it is more restrictive. The minimum privilege need to truncate a table is to alter table. So in order to truncate a table you can run this statement: “grant alter on yourtable to someone”

I am no expert on Sybase and Oracle. So drop a few lines if you find this information not accurate. I didn’t do any post for a long while and I am trying to find more topics to write on and post more frequently.