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

Database Backup

I am new to SQL Server, is it possible to back-up a database or even
individual tables to a server that does not have SQL Server installed? I
want this to be an automated process so that daily updates to the database
can be pickup by our back-up system that runs on a seperate server.
Thanks
AlexIf I understand your question, "tes". You can backup you SQL DB's to another
box that doesnt have SQL installed.
--
TIA,
ChrisR
"Alex" wrote:

> I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex|||You can not backup tables directly but any SQL Server backup can be sent to
a remote share or network location as long as the account SQL Server is
using has the permissions to access that share. Always use UNC notion and
not mapped drives.
Andrew J. Kelly SQL MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex|||Alex,
Use DTS or BCP (OUT) to copy the data out to a file.
HTH
Jerry
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex|||Hi,
You can backup the database using UNC names in the Backup database command.
Prerequsites to do a backup remotely
---
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
For table wise backup use:- DTS or BCP OUT.
Thanks
Hari
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex

Database Backup

I am new to SQL Server, is it possible to back-up a database or even
individual tables to a server that does not have SQL Server installed? I
want this to be an automated process so that daily updates to the database
can be pickup by our back-up system that runs on a seperate server.
Thanks
Alex
If I understand your question, "tes". You can backup you SQL DB's to another
box that doesnt have SQL installed.
TIA,
ChrisR
"Alex" wrote:

> I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex
|||You can not backup tables directly but any SQL Server backup can be sent to
a remote share or network location as long as the account SQL Server is
using has the permissions to access that share. Always use UNC notion and
not mapped drives.
Andrew J. Kelly SQL MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex
|||Alex,
Use DTS or BCP (OUT) to copy the data out to a file.
HTH
Jerry
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex
|||Hi,
You can backup the database using UNC names in the Backup database command.
Prerequsites to do a backup remotely
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
For table wise backup use:- DTS or BCP OUT.
Thanks
Hari
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex

Database Backup

I am new to SQL Server, is it possible to back-up a database or even
individual tables to a server that does not have SQL Server installed? I
want this to be an automated process so that daily updates to the database
can be pickup by our back-up system that runs on a seperate server.
Thanks
AlexIf I understand your question, "tes". You can backup you SQL DB's to another
box that doesnt have SQL installed.
--
TIA,
ChrisR
"Alex" wrote:
> I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex|||You can not backup tables directly but any SQL Server backup can be sent to
a remote share or network location as long as the account SQL Server is
using has the permissions to access that share. Always use UNC notion and
not mapped drives.
--
Andrew J. Kelly SQL MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex|||Alex,
Use DTS or BCP (OUT) to copy the data out to a file.
HTH
Jerry
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex|||Hi,
You can backup the database using UNC names in the Backup database command.
Prerequsites to do a backup remotely
---
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
For table wise backup use:- DTS or BCP OUT.
Thanks
Hari
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:3582FBB0-D1C7-42BE-902D-494B35328BCB@.microsoft.com...
>I am new to SQL Server, is it possible to back-up a database or even
> individual tables to a server that does not have SQL Server installed? I
> want this to be an automated process so that daily updates to the database
> can be pickup by our back-up system that runs on a seperate server.
> Thanks
> Alex