From the monthly archives:

December 2009

How to Drop a SQL Server Database

December 29, 2009

in DBA,General,SQL Basics

 Running a DROP statement on an SQL Server is never an easy task as far as the numbers of thoughts that run in our mind at the time of doing that are considered.

So, here is writing up on the process that we need to follow while dropping a SQL Server Database.
Firstly, the decision process that you have to really go ahead and drop the database, if yes then create a checklist of your own with the below as a starting point to it.
1.       Check if any application is using the database,the chance is that they need to be pointed to a new upgraded database or the application is obsolete now and simply needs to be removed.
2.       Check if any other databases are using objects of this database.
3.       Check if you have any Database Level Triggers on this particular database.
4.       Check if you have any Maintenance plans, exclusively referring this database.
5.       Check if you have any MS SQL Server Agent Jobs referring this database exclusively.
6.       Check if you have any Database Snapshots, if yes, Drop the database snapshots on the database.
7.       Check if you have log shipping configured, If the database is involved in log shipping, remove log shipping before dropping the database.
8.       Get a thumbs up from the owner of the database
9.       Check if there are any existing users. If yes, notify them, or if they are long running check what operation they are trying to do and kill them.
10.   Preferably, get the database to SINGLE_USER mode.
11.   Take a full backup with “verify the backup” option on, rather take multiple copies with at least one being offsite.
12. Create a script of all the Logins in that database, in case you may miss the logins at the place of Restoration.
13.   Take a backup of the master database.
14.   Drop the database.
15.   Take a backup of the master database.
 
Now for some basic points
Syntax: DROP DATABASE Database Name
For deleting a database you need to at least have CONTROL permission on the database.
We can delete a database regardless of its state – Offline, Read-only or Suspect or any other.
 
If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. if you have any comments, then the below form is for you, go ahead!!!

Tagged as: Database, Drop

{ Comments on this entry are closed }

Using Temporary Tables with SSIS

December 3, 2009

in Business Intelligence,SSIS

Many people keep on asking on different SQL Server forums on how to use a temporary table using in an SSIS Package, below mentioned is a mechanism that I have learned and been using for long time for now, below it is…

We are going to take this to achieve this goal, we have to create a temporary table, insert some data into it, and using data flow task which we shall dump this data into a permanent table.
We shall take the below script for this,
Create table ##TempTable(Id int identity(1,1),FName nvarchar(100),LName nvarchar(100))
 
Insert into ##TempTable(Fname,Lname)Values ( ‘John’,‘Smith’)
Insert into ##TempTable(Fname,Lname)Values ( ‘Aaron’,‘James’)
Insert into ##TempTable(Fname,Lname)Values ( ‘Albert’,”)
 
DROP TABLE ##TempTable
To be done on SSIS Front.
1.       Create a new data source
2.       Create a connection manager based on this data source
3.       Select the connection manager created in step to and go to properties, and mark the “RetainSameConnetion” as “TRUE”
4.       Now in the control flow – drag an execute sql task in to designer space.
5.       Copy paste the above SQL statements from the above script into that, except the drop table once.  J
6.       Now select the execute sql task, go to properties and make the “Delay Validation” property under execution as “True”.
Before we create the Data Flow Task, we shall first go to the Management Studio and Create a Global Temporary Table so that we have a basic table to map the columns with
7.       Drag the data flow task into the designer
8.       In this Data Flow task, create and OLEDB Source Connection, and have this select query in place.
select * from ##TempTable
9.       Now create an OLEDB Destination connection with a suitable data source, use the create new table option to create a table for the dump.
That’s it, now right click on the package and say execute, zoom it goes…
Now let’s see how we have to convert the global temporary table to a local temporary table.
10.   Select the execute sql task, and then properties and under expressions we have our sql query, here, at every place replace”##” with “#”.
11.   Now go to the data flow task, and in the properties, custom properties, you will find the sql query in “sqlCommand” property, change it here as well.
That’s it, now we have a package with Local Temporary Table as well.
Horray!!! Reached our Goal….

Tagged as: SSIS

{ Comments on this entry are closed }