Database back-up

Hi there,
What we basically want is seperating the development and production
servers. I need to transfer all the data to the new server from the
existing server.
My question is that what's a good way to transfer schema, tables and
data from one SQL Server to another. I have tried export option in the
enterprise manager, but I guess it doesn't transfer things like pk,
relationships etc or does it?
Once I have all data on the other server, I can set up a Trans
Replication, so that I get same data on both servers.
Thanks in advance.
Regards,
Ricky Singh
--
Posted via http://dbforums.comYou could create backups of your main databases and restore them into other
environments. Make sure you desensitize info like credit card numbers etc.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3059366.1057005218@.dbforums.com...
Hi there,
What we basically want is seperating the development and production
servers. I need to transfer all the data to the new server from the
existing server.
My question is that what's a good way to transfer schema, tables and
data from one SQL Server to another. I have tried export option in the
enterprise manager, but I guess it doesn't transfer things like pk,
relationships etc or does it?
Once I have all data on the other server, I can set up a Trans
Replication, so that I get same data on both servers.
Thanks in advance.
Regards,
Ricky Singh
--
Posted via http://dbforums.com|||Originally posted by Quentin Ran
> IMO backing up and then restore on the new machine is a easy,
> convenient
> way.
> Quentin
> Thanks for the replies. I m a newbie on SQL Server. Can I back-up
> using Tools-Backup database on enterprise manager and then save the
> file over WAN' I was trying doing that but cudn't see the mapped
> network drive over the WAN!!
>
> "Ricky_Singh" wrote in message
> news:3059366.1057005218@.dbforums.com"]news:3059366.1057005218@.d-
> bforums.com[/url]...
> > Hi there,
> > What we basically want is seperating the development and
> production
> > servers. I need to transfer all the data to the new server from
> the
> > existing server.
> > My question is that what's a good way to transfer schema, tables
> and
> > data from one SQL Server to another. I have tried export option
> in the
> > enterprise manager, but I guess it doesn't transfer things like
> pk,
> > relationships etc or does it?
> > Once I have all data on the other server, I can set up a
> Trans
> > Replication, so that I get same data on both servers.
> > Thanks in advance.
> > Regards,
> > Ricky Singh
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.com|||Ricky,
I never tried that. I believe you can backup to a mapped LAN drive. If you
have this option, you can then transfer the file over to the WAN site.
Quentin
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3059937.1057009483@.dbforums.com...
> Originally posted by Quentin Ran
> > IMO backing up and then restore on the new machine is a easy,
> > convenient
> > way.
> >
> > Quentin
> >
> > Thanks for the replies. I m a newbie on SQL Server. Can I back-up
> > using Tools-Backup database on enterprise manager and then save the
> > file over WAN' I was trying doing that but cudn't see the mapped
> > network drive over the WAN!!
> >
> >
> > "Ricky_Singh" wrote in message
> > news:3059366.1057005218@.dbforums.com"]news:3059366.1057005218@.d-
> > bforums.com[/url]...
> > > Hi there,
> > > What we basically want is seperating the development and
> > production
> > > servers. I need to transfer all the data to the new server from
> > the
> > > existing server.
> > > My question is that what's a good way to transfer schema, tables
> > and
> > > data from one SQL Server to another. I have tried export option
> > in the
> > > enterprise manager, but I guess it doesn't transfer things like
> > pk,
> > > relationships etc or does it?
> > > Once I have all data on the other server, I can set up a
> > Trans
> > > Replication, so that I get same data on both servers.
> > > Thanks in advance.
> > > Regards,
> > > Ricky Singh
> > > --
> > Posted via
> http://dbforums.com/http://dbforums.com
>
> --
> Posted via http://dbforums.com|||Originally posted by Quentin Ran
> Ricky,
> I never tried that. I believe you can backup to a mapped LAN
> drive. If you
> have this option, you can then transfer the file over to the WAN site.
> Quentin
> Thanks a lot Quentin, I ll try that. Another question I have is that
> if I make a copy of the database file, Can I use that copy for restore
> at another SQL Server location.
> Thanks again!!
> "Ricky_Singh" wrote in message
> news:3059937.1057009483@.dbforums.com"]news:3059937.1057009483@.d-
> bforums.com[/url]...
> > Originally posted by Quentin Ran
> > > IMO backing up and then restore on the new machine is a
> easy,
> > > convenient
> > > way.
> > >
> > > Quentin
> > >
> > > Thanks for the replies. I m a newbie on SQL Server. Can I
> back-up
> > > using Tools-Backup database on enterprise manager and then
> save the
> > > file over WAN' I was trying doing that but cudn't see the
> mapped
> > > network drive over the WAN!!
> > >
> > >
> > > "Ricky_Singh" wrote in message
> > > news:3059366.1057005218@.dbforums.com"]news:3059366.1057-
> 005218@.dbforums.com[/url]"]news:3059366.1057005218@.d-"]news-
> :3059366.1057005218@.d-[/url]
> > > bforums.com[/url]...
> > > > Hi there,
> > > > What we basically want is seperating the development
> and
> > > production
> > > > servers. I need to transfer all the data to the new server
> from
> > > the
> > > > existing server.
> > > > My question is that what's a good way to transfer schema,
> tables
> > > and
> > > > data from one SQL Server to another. I have tried export
> option
> > > in the
> > > > enterprise manager, but I guess it doesn't transfer things
> like
> > > pk,
> > > > relationships etc or does it?
> > > > Once I have all data on the other server, I can set up
> a
> > > Trans
> > > > Replication, so that I get same data on both servers.
> > > > Thanks in advance.
> > > > Regards,
> > > > Ricky Singh
> > > > --
> > > Posted via
> > http://dbforums.com/http://dbforums.com"]http://dbfor-
> ums.com/http://dbforums.com[/url]
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.com|||Certainly.
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3062421.1057073733@.dbforums.com...
> Originally posted by Quentin Ran
> > Ricky,
> >
> > I never tried that. I believe you can backup to a mapped LAN
> > drive. If you
> > have this option, you can then transfer the file over to the WAN site.
> >
> > Quentin
> >
> > Thanks a lot Quentin, I ll try that. Another question I have is that
> > if I make a copy of the database file, Can I use that copy for restore
> > at another SQL Server location.
> > Thanks again!!
> >
> > "Ricky_Singh" wrote in message
> > news:3059937.1057009483@.dbforums.com"]news:3059937.1057009483@.d-
> > bforums.com[/url]...
> > > Originally posted by Quentin Ran
> > > > IMO backing up and then restore on the new machine is a
> > easy,
> > > > convenient
> > > > way.
> > > >
> > > > Quentin
> > > >
> > > > Thanks for the replies. I m a newbie on SQL Server. Can I
> > back-up
> > > > using Tools-Backup database on enterprise manager and then
> > save the
> > > > file over WAN' I was trying doing that but cudn't see the
> > mapped
> > > > network drive over the WAN!!
> > > >
> > > >
> > > > "Ricky_Singh" wrote in message
> > > > news:3059366.1057005218@.dbforums.com"]news:3059366.1057-
> > 005218@.dbforums.com[/url]"]news:3059366.1057005218@.d-"]news-
> > :3059366.1057005218@.d-[/url]
> > > > bforums.com[/url]...
> > > > > Hi there,
> > > > > What we basically want is seperating the development
> > and
> > > > production
> > > > > servers. I need to transfer all the data to the new server
> > from
> > > > the
> > > > > existing server.
> > > > > My question is that what's a good way to transfer schema,
> > tables
> > > > and
> > > > > data from one SQL Server to another. I have tried export
> > option
> > > > in the
> > > > > enterprise manager, but I guess it doesn't transfer things
> > like
> > > > pk,
> > > > > relationships etc or does it?
> > > > > Once I have all data on the other server, I can set up
> > a
> > > > Trans
> > > > > Replication, so that I get same data on both servers.
> > > > > Thanks in advance.
> > > > > Regards,
> > > > > Ricky Singh
> > > > > --
> > > > Posted via
> > > http://dbforums.com/http://dbforums.com"]http://dbfor-
> > ums.com/http://dbforums.com[/url]
> > > --
> > Posted via
> http://dbforums.com/http://dbforums.com
>
> --
> Posted via http://dbforums.com|||Originally posted by Quentin Ran
> Certainly.
> The problem I m facing is that when I try to back-up the server, I am
> unable to see the mapped network drive. I see a dialog box with the
> server name and the drives on that server, but no mapped drives.
> Thanks for the help.
> "Ricky_Singh" wrote in message
> news:3062421.1057073733@.dbforums.com"]news:3062421.1057073733@.d-
> bforums.com[/url]...
> > Originally posted by Quentin Ran
> > > Ricky,
> > >
> > > I never tried that. I believe you can backup to a mapped
> LAN
> > > drive. If you
> > > have this option, you can then transfer the file over to the
> WAN site.
> > >
> > > Quentin
> > >
> > > Thanks a lot Quentin, I ll try that. Another question I have
> is that
> > > if I make a copy of the database file, Can I use that copy for
> restore
> > > at another SQL Server location.
> > > Thanks again!!
> > >
> > > "Ricky_Singh" wrote in message
> > > news:3059937.1057009483@.dbforums.com"]news:3059937.1057-
> 009483@.dbforums.com[/url]"]news:3059937.1057009483@.d-"]news-
> :3059937.1057009483@.d-[/url]
> > > bforums.com[/url]...
> > > > Originally posted by Quentin Ran
> > > > > IMO backing up and then restore on the new machine is
> a
> > > easy,
> > > > > convenient
> > > > > way.
> > > > >
> > > > > Quentin
> > > > >
> > > > > Thanks for the replies. I m a newbie on SQL Server. Can
> I
> > > back-up
> > > > > using Tools-Backup database on enterprise manager and
> then
> > > save the
> > > > > file over WAN' I was trying doing that but cudn't see
> the
> > > mapped
> > > > > network drive over the WAN!!
> > > > >
> > > > >
> > > > > "Ricky_Singh" wrote in message
> > > > > news:3059366.1057005218@.dbforums.com"]news:3059366.-
> 1057005218@.dbforums.com[/url]"]news:3059366.1057-"]news:305-
> 9366.1057-[/url]
> > > 005218@.dbforums.com[/url]"]news:3059366.1057005218@.-
> d-news:3059366.1057005218@.d-"]news-
> > > :3059366.1057005218@.d-[/url]
> > > > > bforums.com[/url]...
> > > > > > Hi there,
> > > > > > What we basically want is seperating the
> development
> > > and
> > > > > production
> > > > > > servers. I need to transfer all the data to the new
> server
> > > from
> > > > > the
> > > > > > existing server.
> > > > > > My question is that what's a good way to transfer
> schema,
> > > tables
> > > > > and
> > > > > > data from one SQL Server to another. I have tried
> export
> > > option
> > > > > in the
> > > > > > enterprise manager, but I guess it doesn't transfer
> things
> > > like
> > > > > pk,
> > > > > > relationships etc or does it?
> > > > > > Once I have all data on the other server, I can set
> up
> > > a
> > > > > Trans
> > > > > > Replication, so that I get same data on both
> servers.
> > > > > > Thanks in advance.
> > > > > > Regards,
> > > > > > Ricky Singh
> > > > > > --
> > > > > Posted via
> > > > http://dbforums.com/http://dbforums.com"]http://d-
> bforums.com/http://dbforums.com[/url]"]http://dbfor-/"]http-
> ://dbfor-[/url]
> > > ums.com/http://dbforums.com[/url]"]http://dbforums.-
> com[/url][/url]
> > > > --
> > > Posted via
> > http://dbforums.com/http://dbforums.com"]http://dbfor-
> ums.com/http://dbforums.com[/url]
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.comsql

Data Transformation Services (DTS)(Bulk Insert)

Hi All,

I'm using DTS package, a tool to transfer data from a txt file to database(Bulk Insert).

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.It seems that the Bulk Insert task supports only OLE DB connections for the destination database. But I want to use sql server authentication as OLEDB connection requires windows authentication.

So can the bulk insert be done using SQLServer authentication ? if yes then please help me.

I have given the code snippet below.

Code Sample:

Dim oPackage As New DTS.Package2()
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask
Try
oConnection = oPackage.Connections.New("SQLOLEDB")
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSBulkInsertTask")
oCustomTask = oTask.CustomTask
With oConnection
oConnection.Catalog = "pubs"
oConnection.DataSource = "(local)"
oConnection.ID = 1
oConnection.UseTrustedConnection = True
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"
End With
oPackage.Connections.Add(oConnection)
oConnection = Nothing
With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = "c:\dts\authors.txt"
.ConnectionID = 1
.DestinationTableName = "pubs..authors"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
oStep.TaskName = oCustomTask.Name
With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With
oPackage.Execute()
Catch ex As Exception
MsgBox("Error: " & CStr(Err.Number) & vbCrLf_
& Err.Description, vbExclamation, oPackage.Name)
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try

This is really a SSIS forum not DTS. There is a newsgroup for DTS, with a web/forum style interface.

Corrected Snippet

oConnection.UseTrustedConnection = False ' Must be false to use SQL Security
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"

Data Transfer Wizard - Not Copying properly

Here is the scenerio

I am trying to copy a database from a live sql server to a local server.

I wanted to copy the tables, triggers, procedures, functions, users

So I have "Copy tranfer of objects " in the DTS wizard.

But the process fails informing that the users are not there in the local server and fails the process.

Now what is my expectation is that why the user logins every thing is not copied to the local sever.

Help would be more appreciated. Pretty urgent.

The quick, easy and reliable way is to backup the live one and restore on the local server it moves everything and gives you the option to change the name of the database. In the backup and restore wizard choose restore from device option, known issues orphaned permissions just delete that restore and create a new one. The other valid option is to dettach and attach but the former is the best I know. Hope this helps.|||

I tried your solution.

This are the steps i follow to do the backup.

1. I select the database from the live server.

2. I wanted to give a file name and need to specify a location.

3. I am not able to specify a location of my local drive instead i am able to specify a location of the live server harddisk which is of no

use to me as i am not able to restore because i dont have access to the live server system.

Solution is pretty urgent.

|||

I think DTS not is properly executed when a field is having a datatype of image in a table.

As the "image" field is holding a binary value it is not properly performed.

How to Replicate the use.

1. Create a database and a table with a field "projectimage" and set it datatype as image.

2. Now try doing DTS and create a new database using DTS

3. I hope the error would be replicated.

::Error::
Transfer Status: Transferring Data: (Table '[dbo].[<tablename]')
Data truncation occurred in table [dbo].[tablename], column 1.

any solutions would be more appericated

|||Now to the correct way to use the backup and restore wizard, is because it is a backup in a different server you put the .bak in the backup subfolder in programs , Microsoft SQL Server, Backup you put the file there and then let Windows create the file path for you when you choose the restore from device option in the wizard that is the most important part because if you don't SQL Server gets confused. So try these instructions and post again. I am sorry I did not add this part in my first post, Hope this helps.|||

Hi caddre, I am looking for more help from you.

In the backup option of live server it shows the files and folder of the live server and not of local disk nor any shared directory. -This is the problem

Similarly in

the backup option of local server it shows the files and folder of the local server and not of the live server.

So I dont find any common place to restore the database.

Please consider that i dont have any access to live server.

|||

I have figured out the solution in msdn

http://support.microsoft.com/kb/257425

|||I am glad you got it resolved but you did not undersatnd my instruction because if you email me your .bak I can restore it in less than five minutes. You can put your .bak in a CD post it to me and I can still restore it in five minutes it is the most efficient way to move SQL Server databases.|||

I Found that was the actually problem but unable to resolve it.

The problem seems to be occur in sql server 7.0 and not in sql server 2000.

I am using sql server 2000 and win xp but still not able solve it.

I need a step by step details or screen sheets would be more helpful.

|||

I have restored SQL Server 7.0 in SQL Server 2000 and 2000 is very good it will ask you if you want to keep TDS(tabular data stream) 70 that is 7.0 compatibility and the .bak was emailed to me on the company network.

Here are the steps right click on the SQL Server 7.0 database go to all tasks and then backup, then use the backup and restore wizard to do a full backup, when you have completed the backup use any zip software like winzip and zip it. Then take the .bak file when you go into SQL Server 2000 in xp go to programs Microsoft SQL Server then there are two subfolders one is the Data folder where the MDF,LDF and IDF are stored the other one is the backup put your .bak there, then go to the backup and restore wizard back in Enterprise manager and click to create a restore it will ask for the location of the .bak file. This is the very important part when you choose restore from device it will want to know the file part click on the ellipse Windows will create the file part for you don't try to create it because if you do SQL Server will get confused, after Windows create the file part you are almost there, it will ask if you want to change the name of the database. There are many threads here where I have helped others and the problem is always the file path let Windows create it but also verify it is correct. Post again if you still have question.

|||

1Here are the steps right click on the SQL Server 7.0 database go to all tasks and then backup, then use the backup and restore wizard todo a full backup,

when you have completed the backup use any zip software like winzip and zip it. - This is the problem.

In the Select Backup Database (4th step in the wizard)

I need to give a file location and click the ellipse button to see the files and folder of live server .

I am not able to see the files and folder of local disk. - this is where i dont really understand

I hope now u have got my problem clearly.

|||

You need to take the .bak file physically to your local SQL Server Backup subfolder you cannot try to do the restore on your local drive from the live server, I know you cannot do that with the SQL Server backup and restore wizard.

Take the .bak put it in your local SQL Server backup folder in programs. I will look for the other thread so you may understand, because I think you are trying to restore on the local one from the live server and that is not correct.

|||

You need to take the .bak file physically to your local SQL Server Backup subfolder you cannot try to do the restore on your local drive from the live server, I know you cannot do that with the SQL Server backup and restore wizard - This is the problem.

I dont have the access to copy the .bak file from live server to local disk.

Is there is any work around.

|||

See if you can register the live server on your local SQL Server right click at the top of Enterprise Manager you will see register server if you can register it then it becomes local to you and you can just copy the .bak into this physical location. If you cannot then you have to create a blank database in your local XP and use DTS to move the data into your local machine. Hope this helps.

C:\Program Files\Microsoft SQL Server\MSSQL$SQLSERVER03\BACKUP\

|||

you can just copy the .bak into this physical location - this is the problem.

1. I am able to register the live server in my local server and able to create the .bak file but unable to copy the .bak into this physical location of local

disk. 2.I have tried using DTS, Then only I faced the problem of the image field

The first option is ruled out because iam not able to access it

ie i am using DTS and finding problem with image field which gives me the following error.

::Error::
Transfer Status: Transferring Data: (Table '[dbo].[tablename')
Data truncation occurred in table [dbo].[tablename], column 1

This is a problem in sql server 7.0 and getting solved using sp4 but still iam facing the same problem with sql 2000 and win xp

which i dont know why?

I hope now things clear and leaving for End of Day and hope to see u in the morning.

Data Transfer via Email

Hi,
I have a client that needs to transfer data changes from remote SQL
sites to their HQ central DB. I would prefer to use replication but
unfortunately this is not an option as they only want to use email.
We did a home rolled system about 10 years ago using Access with
custom flags for edits and additions in the DB and the changes were
written to ascii files. I don't want to go down this path again!!
Could anyone give suggestions as to the best direction
- Low bandwidth
- Simple to implement and maintain
- Robust
- Latest 'fashion'
The data changes are primarily in one direction - client->HQ and of
low volume say 5 edits per day. If the way to go is an XML file is
there a mechanism for field based selection, or does it have to be the
entire row?
Appreciate any input,
Thanks,
casey
Publish that table in a transactional replication publication and create a
local subscription and make sure you transaction retention is 1 day.
Then issue a call to sp_browsereplcmds and you will get a list of procedure
calls with parameters which you can email to the other SQL Server. If the
procs are on the remote server you will be able to run these procedures
there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"casy" <mailkerry@.gmail.com> wrote in message
news:1176105883.833718.305870@.q75g2000hsh.googlegr oups.com...
> Hi,
> I have a client that needs to transfer data changes from remote SQL
> sites to their HQ central DB. I would prefer to use replication but
> unfortunately this is not an option as they only want to use email.
> We did a home rolled system about 10 years ago using Access with
> custom flags for edits and additions in the DB and the changes were
> written to ascii files. I don't want to go down this path again!!
> Could anyone give suggestions as to the best direction
> - Low bandwidth
> - Simple to implement and maintain
> - Robust
> - Latest 'fashion'
> The data changes are primarily in one direction - client->HQ and of
> low volume say 5 edits per day. If the way to go is an XML file is
> there a mechanism for field based selection, or does it have to be the
> entire row?
> Appreciate any input,
> Thanks,
> casey
>
|||Wow,
Thanks Hilary, just what I was looking for, will explore this option!
Casey
On Apr 9, 10:26 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Publish that table in a transactional replication publication and create a
> local subscription and make sure you transaction retention is 1 day.
> Then issue a call to sp_browsereplcmds and you will get a list of procedure
> calls with parameters which you can email to the other SQL Server. If the
> procs are on the remote server you will be able to run these procedures
> there.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "casy" <mailke...@.gmail.com> wrote in message
> news:1176105883.833718.305870@.q75g2000hsh.googlegr oups.com...
>
>
>
>
>
> - Show quoted text -
|||The mouse is still warm...
New to tranactional replication so it will take a little time for me
to get up to speed. Having created a dummy set up as you suggested and
looked at the stored procedures my question is (before I invest some
serious time) ..
The main data source is -from- the client and installing full SQL
server is probably not an option on these numerous remote sites.
Is it possible/feasable to use Xpress on the client and set it up as a
transactional subscriber - maybe connect initially- and use the method
you outlined previously?
Pardon my ignorance if I am talking nonsense!
Casey
On Apr 10, 7:19 am, "casy" <mailke...@.gmail.com> wrote:
> Wow,
> Thanks Hilary, just what I was looking for, will explore this option!
> Casey
> On Apr 9, 10:26 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -