Skip to main content

Posts

Showing posts from October, 2010

Bulk Insert Permissions

In order to be able to execute bulk operations you need to certain level of privilege both on the database (table) you are performing the operation against and the instance you are performing the operation on.
If you don't have the necessary permissions you will get an error similar to the following:
Msg 4834, Level 16, State 4, Line 5
You do not have permission to use the bulk load statement.
This is what one of my users got earlier today when trying to perform a BULK INSERT. To be able to do this successfully they will need the ability (permission) to INSERT records into the table in question.
In our case the following script will do the trick:
USE orderdemo goGRANT INSERT ON dbo.orders_BULK TO bulktest go

They will also need Administer Bulk Operations permission, you can give that permission by executing the following, this is a server level permission:


USE master goGRANT ADMINISTER BULK OPERATIONS TO bulktest


The user will need ALTER table permissions in certain circumstances.


What I Did This Weekend

About two weeks ago my good friend and former colleague Justin Hostettler-Davie -JHD (Blog | Twitter) asked me via my wife on  Facebook if I fancied climbing Scafell Pike, the highest peak in England, with him on Saturday the 16th October and for some reason I said OK why not. Justin is training to climb Kilimanjaro in the new year (January 2011) to raise money for Velindre Cancer Centre. You can read more about Justin’s challenge and make a donation if you want to here. He has climbed several mountains in preparation for his challenge including Cadair Idris, Pen Y Fan, and Snowden and this Saturday we attempted Scafell Pike he has been accompanied by several friends over the course of his training, but Lance Thomas (No Blog | No Twitter) has been on most of his training climbs with him.We set off from South Wales Friday lunch time, slightly late because JHD missed my junction on the motorway, again! He never remembers where I live, so instead of leaving at 1230 we left at 1315. JHD, …

What I Did This Weekend

About two weeks ago my good friend and former colleague Justin Hostettler-Davie -JHD (Blog | Twitter) asked me via my wife on  Facebook if I fancied climbing Scafell Pike, the highest peak in England, with him on Saturday the 16th October and for some reason I said OK why not. Justin is training to climb Kilimanjaro in the new year (January 2011) to raise money for Velindre Cancer Centre. You can read more about Justin’s challenge and make a donation if you want to here. He has climbed several mountains in preparation for his challenge including Cadair Idris, Pen Y Fan, and Snowden and this Saturday we attempted Scafell Pike he has been accompanied by several friends over the course of his training, but Lance Thomas (No Blog | No Twitter) has been on most of his training climbs with him.We set off from South Wales Friday lunch time, slightly late because JHD missed my junction on the motorway, again! He never remembers where I live, so instead of leaving at 1230 we left at 1315. JHD, …

TSQL Tuesday Misconceptions in SQL Server

I haven’t written a post for a TSQL Tuesday for a little while and I know that I’m late getting this one out too. This month’s party is being hosted by Sankar Reddy This topic  I think, maybe expect, really I know, that this topic has been covered already, I know that the Steve Jones has posted on this misconception already today and Paul Randal (Blog | Twitter) in his misconceptions series back in April covered this too much better than I have here so go and check out Paul’s post . The misconception I will look at here is - SQL Server will truncate the the transaction log after a full backup when the database is in full recovery mode. After my blog post around recovery models earlier today I think this maybe quite apt for me.

The misconception I am going to talk about here is SQL Server truncates the log of a database in full recovery mode when a full backup is taken. It doesn’t.
This one of the biggest problems I come across, a database in full recovery mode and regular full backup…

SQL Server Recovery Models

A SQL Server database can be in one of three recovery models. FULL, BULK_LOGGED and SIMPLE. The recovery model you choose for your databases will  affect your restore plans in the event of failure or need to restore, the recovery model you pick will also influence how you mange the transaction log of your database. So you need to choose carefully. FullThe full recovery model offers your database the most protection. Full recovery mode means all database operations are fully logged. It allows point in time and point of failure restores. It does come with some overhead though, you need to implement both Full and Log backups as part of your backup plan. You need the transaction log backups for two reasons. 1. Transaction log backups allow you to perform point of failure or point in time restores. 2. If you don’t take transaction log backups of your database when its in Full recovery mode then your transaction log will grow uncontrollably and will eventually use up all available drive spa…