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.