Re: DTS
From: Hari (hari_prasad_k_at_hotmail.com)
Date: 03/07/04
- Next message: Preet Kanwaljit Singh Shergill: "Query optimizing"
- Previous message: WJH: "Re: Login fails. Login failed for user 'LAPTOP\ASPNET'"
- In reply to: Noorali Issani: "Re: DTS"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 7 Mar 2004 17:33:14 +0530
Hi,
1. Using "Client network utility" create a alias in the local server for the
Remote server (Provide IP address and Port number of remote server)
eg: You create a alias name as "REMOTESRV01"
2. Inside the batch program using osql execute the below statement
OSQL -Usa -Ppassword -SREMOTESRV01 -Q"BACKUP database <dbname > to
disk='c:\backup\dbname.bak' with init" -o c:\log\backup.log
a. Replace the Password with actual password
b. Replace dbname with actual database name
c. Replace c:\backup with actual backup folder
d. Replace c:\log\backup.log with your requirement
3. Inside batch file copy this c:\backup\dbname.bak to local machine
copy \\remoteserver\share\dbname.bak c:\backup\dbname.bak
4. Load the database to Local server using OSQL
OSQL -Usa -Ppassword -SLOCALSRV -Q"RESTORE database <dbname > from
disk='c:\backup\dbname.bak' " -o c:\log\restore.log
So your batch file will be some thing like,
OSQL -Usa -Ppassword -SREMOTESRV01 -Q"BACKUP database <dbname > to
disk='c:\backup\dbname.bak' with init" -o c:\log\backup.log
copy \\remoteserver\share\dbname.bak c:\backup\dbname.bak
OSQL -Usa -Ppassword -SLOCALSRV -Q"RESTORE database <dbname > from
disk='c:\backup\dbname.bak' " -o c:\log\restore.log
Schedule this batch file using SQL Agent -- Jobs
Thanks
Hari
MCDBA
"Noorali Issani" <naissani@softhome.net> wrote in message
news:OS6saUzAEHA.744@TK2MSFTNGP10.phx.gbl...
> Hari, if u don't mind could you tell me how can I take the backup of
remote
> server from local server through OSQL and how I make the job that to
restore
> that remoted backup file into locally through osql. I want to make it
> automate
>
> thanks in advance
>
> Noor
>
>
> "Hari" <hari_prasad_k@hotmail.com> wrote in message
> news:#ICbPdqAEHA.2808@TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Where is your Remote database Located, Is that database assesible using
> SQL
> > server tools (Enterprise manager , Query Analyzer ..). If yes then you
can
> > automate the steps which I mentioned using SQL Agent -- Jobs.
> >
> > How to do..
> >
> > 1. From Local server execute the BACKUP database of your remote server
> using
> > OSQL
> > 2. Step 1 will backup the database backup file to local server
> > 3. Run a Restore database command using OSQL command in local SQL
server.
> >
> > Above steps can be automated using SQL Agent -- Jobs.
> >
> > DTS will not be a ideal solution if the data size is huge.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Noorali Issani" <naissani@softhome.net> wrote in message
> > news:ecZXQOpAEHA.3400@tk2msftngp13.phx.gbl...
> > > Hari you are very right but I want the remote database locally which
> > should
> > > fully automate, through DTS I can fulfill my task, like I will make
one
> > job
> > > which will execute that package and that package will copy the remote
> > > database locally... That's my point of view.. if you have any idea
then
> > > please let me know regarding this..
> > >
> > > Thanks
> > > Noor
> > >
> > > "Mario Splivalo" <majk@fly.srk.fer.hr> wrote in message
> > > news:slrnc4gcjk.91j.majk@fly.srk.fer.hr...
> > > > On 2004-03-05, Hari <hari_prasad_k@hotmail.com> wrote:
> > > > > Hi,
> > > > >
> > > > > If you need to transfer the entire database from remote to local,
> the
> > > best
> > > > > suggested way is not to use DTS, rather use the below methods,
> > > > >
> > > > > Method - 1
> > > > >
> > > > > a. Detach the database (SP_DETACH_DB dbname)
> > > > > b. Copy the MDF and LDF to LOcal server
> > > > > c. Attach the database (SP_ATTCHDB
> > > > > dbname,'mdffilephysicalname','ldffilephysicalname'
> > > >
> > > > This won't work if the database is published for replication. But,
you
> > > could
> > > > stop the SQL server, copy the files, and then start the SQL server
> back.
> > > > After that, attach the copies to the remote database.
> > > >
> > > > Mike
> > > > --
> > > > "I can do it quick. I can do it cheap. I can do it well. Pick any
> two."
> > > >
> > > > Mario Splivalo
> > > > msplival@jagor.srce.hr
> > >
> > >
> >
> >
>
>
- Next message: Preet Kanwaljit Singh Shergill: "Query optimizing"
- Previous message: WJH: "Re: Login fails. Login failed for user 'LAPTOP\ASPNET'"
- In reply to: Noorali Issani: "Re: DTS"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
