Move Data from one server database to another server database(Import-Export utility))

Hello Friends,

Most of the time we have experienced that we need to move data from one Server(xxx) to another server(yyy).
For this we need to use Import-Export process from SSMS. This is good utility on SSMS to move data and just monitor it.

E.g. I got source server as xxx, source database as DB1 and i need to move data for particular table from this source to destination server(yyy),destination database DB2.

1. Connect to source server(xxx) go to database DB1 ad right click task->Export option.

It will open window to select source details :-

Select Server name -xxx
Database- DB1

use Authentication which is working on for server.
e,g, i am using here Windows authentication because i am having access on both server
click next




2.Choose destination data source ;-

 Select Server Name- yyy
Database -DB2

 click Next
Here you can see 2 options-
Copy tables from source to destination as it is
and write a query to select specific data from tables and put it in specific table




I will go with Option 1 as of now to move data from one table to destination table as it is

Select Option 1 and click Next.

You can see here that i am moving account table from source to Account table of destination databse.
If this Account table is not present on destination then it will create it automatically.
If you want to move table into already existing table then select table from destiantion table drop down.
Make sure soruce and destination table structure is same.You can also proview data which is moving to destination table.

Click Next-Click Next and Finish.
It will start moving data from soruce to destination.
At the end you can see all messages in Green color. And row count which is moved to destiantion table.

Hope this will help you in moving tables from one table to table instead of taking full backup and restoring it.

Will show you automated version of this process using SSIS package in next blog.

SQL Script to move files from one folder to another(newly created) folder

Hello All,

Back with another SQL script.
you have might observed that you have to manually copy files from one folder another folder(newly created folder) on windows .This might take lot of time and you have to keep monitoring it.

You will feel like this process must be automated so here SQL script which will create new folder and move files from one folder to newly created folder on same machine.

I came across a situation where i need to move files from X folder to Y folder but I need to create  folder with current date name in Y folder and move files in that folder only.

EXEC

master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC
master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

DECLARE @cmd nvarchar(500), @folderName varchar(100),@move varchar(100),@destinationpath varchar(50)

SET @folderName = Newfolder_' + REPLACE(CONVERT(varchar(10), GETDATE(), 101), '/', '') 

SET @cmd = 'mkdir D:\destination\' + @folderName

EXEC master..xp_cmdshell @cmd   --- this will create folder(newfolder_mmddyyy)

set @destinationpath='D:\destination\'+@folderName
set @move ='move d:\source\* '+ @destinationpath

exec master.dbo.xp_cmdshell @move  ---this will move files to newly created folder


This script will move all files from D:\Source to D:\Destination (Newly created folder)
Newly created folder will be Newfolder_mmddyyyy.

YOu can use this process and make more processed automated which will reduce your manual work.


Get back to me if you need more help in this.

SQL Script to send mail with embedded image

Hello All,

If you are working on SQL then most of the time you think that we should get automated mail in our mailbox on any event/alert.

There is Database mail configuration facility is available in SSMS 2008. If this Database mail configuration is  setup with your SMTP profile account then you will get mails automatically.

If you want to send out mail to your team member or friend using this Database mail facility then you will need below code. Which will make your work easier and simpler. This code imbedd image in your mail too which will make your mail more customized and attractive

DECLARE @tableHTML  NVARCHAR(MAX) ;
    SET @tableHTML =
N'<H1>Going home!!!</H1>'+
N'<img src="2.gif" />';

    EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profilename',  --this is a profile name which is used in Database mail configuration setting
@recipients = 'user@gmail.com',
@subject = 'Sending Mail from SQL Server Test',
@file_attachments =N'C:\Users\Pictures\2.gif',  --this should be location where 2.gif picture is located
@body=@tableHTML,
@body_format = 'HTML';

You have keep 2.gif picture on server c$ or any drive from which you are going to send mail using SQL script.

you can do lot of modification in this content also e.g. you can add friend name after
N'<H1>Going Home!!!</H1> code so that it will look like more customized.

Get back to me if you need any help or any doubt while implementing it.