How to include more databases in DB Maintenance Plan ?

Hi,
I have created a DB maintenance plan to backup a production database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I have checked the maintenance plan but I am not able to
work out how to include 2 more databases.
Is it possible to give me some advice ?
ThanksPeter
Right Click on MP and then Properties . Add more user databases. Is that
what you mean?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I have created a DB maintenance plan to backup a production database
> daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I have checked the maintenance plan but I am not able
> to work out how to include 2 more databases.
> Is it possible to give me some advice ?
> Thanks
>|||Yes.
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8m1RToIHHA.780@.TK2MSFTNGP03.phx.gbl...
> Peter
> Right Click on MP and then Properties . Add more user databases. Is that
> what you mean?
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan to backup a production database
>> daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I have checked the maintenance plan but I am not able
>> to work out how to include 2 more databases.
>> Is it possible to give me some advice ?
>> Thanks
>

How to include more databases in DB Maintenance Plan ?

Hi,
I have created a DB maintenance plan to backup a production database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I have checked the maintenance plan but I am not able to
work out how to include 2 more databases.
Is it possible to give me some advice ?
ThanksPeter
Right Click on MP and then Properties . Add more user databases. Is that
what you mean?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I have created a DB maintenance plan to backup a production database
> daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I have checked the maintenance plan but I am not able
> to work out how to include 2 more databases.
> Is it possible to give me some advice ?
> Thanks
>|||Yes.
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8m1RToIHHA.780@.TK2MSFTNGP03.phx.gbl...
> Peter
> Right Click on MP and then Properties . Add more user databases. Is that
> what you mean?
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:OZusmMoIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>

How to include more databases in DB Mainteance Plan for SQL Server 2005

Hi,
I have created a DB maintenance plan by using wizard to backup a production
database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I select the maintenance plan and press "Modify" but I am
not able to work out how to include 2 more databases. Besides, it appears
that both transaction log & database backup are not deleted since the plan
is executed, is there something wrong ?
Is it possible to give me some advice ?
ThanksPeter
You asked the question yesterday and I gave you the answer. Don't you
rememner that?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a
> production database daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I select the maintenance plan and press "Modify" but I
> am not able to work out how to include 2 more databases. Besides, it
> appears that both transaction log & database backup are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||> There is a request to include 2 more databases to be included in the maintenance plan. I select
> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
> databases.
You need to add the databases to the backup task inside the plan. Right-lock the backup task, select
"Edit..." and in the "Databases:" drop.down you select the databases you want to include.
> Besides, it appears that both transaction log & database backup are not deleted since the plan is
> executed, is there something wrong ?
Add a "Maintenance Cleanup Task" to the plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a production database daily.
> There is a request to include 2 more databases to be included in the maintenance plan. I select
> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
> databases. Besides, it appears that both transaction log & database backup are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||Dear Uri,
I forget to mention that it is SQL Server 2005 yesterday. In this way, your
advice is applicable for SQL Server 2000.
Thanks
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEfBDB1IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Peter
> You asked the question yesterday and I gave you the answer. Don't you
> rememner that?
>
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted since
>> the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||Dear Tibor and Uri,
Thank you for your advice.
I find that the reason why I am not able to add more databases in the task
is because I use SA in my workstation while Windows Authentication is used
at the server side. In this way, I change the connection from Windows
Authentication to SQL Authentication for both local and target servers and I
am able to do it on my workstation. I have changed the ownership of the
jobs to SA (Instead of Administrator).
I would like to seek your advice
1) Is it possible to change the ownership of the "Database Maintenance Plan"
from Administrator to SA ?
2) Is it necessary to add the "Cleanup Task" for the daily transaction log
maintenance plan so that old transaction log backup will be deleted ?
3) Which step should be performed first - Cleanup Task or Backup Task ?
Should the constraint be success or finish ?
4) Is it necessary to add another task to delete old reports in the "Weekly
Maintenance Plan" so that the logging will be deleted ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases.
> You need to add the databases to the backup task inside the plan.
> Right-lock the backup task, select "Edit..." and in the "Databases:"
> drop.down you select the databases you want to include.
>
>> Besides, it appears that both transaction log & database backup are not
>> deleted since the plan is executed, is there something wrong ?
> Add a "Maintenance Cleanup Task" to the plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but I
>> am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted since
>> the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||> 1) Is it possible to change the ownership of the "Database Maintenance Plan" from Administrator to
> SA ?
I would guess that you would change the owner of the job. To the best of my knowledge, a maint plan
doesn't have an owner, the job does. Not sure, though.
> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log maintenance plan so
> that old transaction log backup will be deleted ?
Yes, if you want the old backup files to be removed and if you don't remove them some other way.
> 3) Which step should be performed first - Cleanup Task or Backup Task ? Should the constraint be
> success or finish ?
This is really your decision. I prefer to do the backup first, and if it fails I don't remove old
backups.
> 4) Is it necessary to add another task to delete old reports in the "Weekly Maintenance Plan" so
> that the logging will be deleted ?
Yes, if you want the old report files to be removed and if you don't remove them some other way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Dear Tibor and Uri,
> Thank you for your advice.
> I find that the reason why I am not able to add more databases in the task is because I use SA in
> my workstation while Windows Authentication is used at the server side. In this way, I change the
> connection from Windows Authentication to SQL Authentication for both local and target servers and
> I am able to do it on my workstation. I have changed the ownership of the jobs to SA (Instead of
> Administrator).
> I would like to seek your advice
> 1) Is it possible to change the ownership of the "Database Maintenance Plan" from Administrator to
> SA ?
> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log maintenance plan so
> that old transaction log backup will be deleted ?
> 3) Which step should be performed first - Cleanup Task or Backup Task ? Should the constraint be
> success or finish ?
> 4) Is it necessary to add another task to delete old reports in the "Weekly Maintenance Plan" so
> that the logging will be deleted ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the maintenance plan. I select
>> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
>> databases.
>> You need to add the databases to the backup task inside the plan. Right-lock the backup task,
>> select "Edit..." and in the "Databases:" drop.down you select the databases you want to include.
>>
>> Besides, it appears that both transaction log & database backup are not deleted since the plan
>> is executed, is there something wrong ?
>> Add a "Maintenance Cleanup Task" to the plan.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a production database daily.
>> There is a request to include 2 more databases to be included in the maintenance plan. I select
>> the maintenance plan and press "Modify" but I am not able to work out how to include 2 more
>> databases. Besides, it appears that both transaction log & database backup are not deleted
>> since the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>|||Dear Tibor,
Thank you for your advice.
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejNfWICJHHA.3424@.TK2MSFTNGP02.phx.gbl...
>> 1) Is it possible to change the ownership of the "Database Maintenance
>> Plan" from Administrator to SA ?
> I would guess that you would change the owner of the job. To the best of
> my knowledge, a maint plan doesn't have an owner, the job does. Not sure,
> though.
>
>> 2) Is it necessary to add the "Cleanup Task" for the daily transaction
>> log maintenance plan so that old transaction log backup will be deleted ?
> Yes, if you want the old backup files to be removed and if you don't
> remove them some other way.
>
>> 3) Which step should be performed first - Cleanup Task or Backup Task ?
>> Should the constraint be success or finish ?
> This is really your decision. I prefer to do the backup first, and if it
> fails I don't remove old backups.
>
>> 4) Is it necessary to add another task to delete old reports in the
>> "Weekly Maintenance Plan" so that the logging will be deleted ?
> Yes, if you want the old report files to be removed and if you don't
> remove them some other way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
>> Dear Tibor and Uri,
>> Thank you for your advice.
>> I find that the reason why I am not able to add more databases in the
>> task is because I use SA in my workstation while Windows Authentication
>> is used at the server side. In this way, I change the connection from
>> Windows Authentication to SQL Authentication for both local and target
>> servers and I am able to do it on my workstation. I have changed the
>> ownership of the jobs to SA (Instead of Administrator).
>> I would like to seek your advice
>> 1) Is it possible to change the ownership of the "Database Maintenance
>> Plan" from Administrator to SA ?
>> 2) Is it necessary to add the "Cleanup Task" for the daily transaction
>> log maintenance plan so that old transaction log backup will be deleted ?
>> 3) Which step should be performed first - Cleanup Task or Backup Task ?
>> Should the constraint be success or finish ?
>> 4) Is it necessary to add another task to delete old reports in the
>> "Weekly Maintenance Plan" so that the logging will be deleted ?
>> Thanks
>> Peter
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but
>> I am not able to work out how to include 2 more databases.
>> You need to add the databases to the backup task inside the plan.
>> Right-lock the backup task, select "Edit..." and in the "Databases:"
>> drop.down you select the databases you want to include.
>>
>> Besides, it appears that both transaction log & database backup are not
>> deleted since the plan is executed, is there something wrong ?
>> Add a "Maintenance Cleanup Task" to the plan.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have created a DB maintenance plan by using wizard to backup a
>> production database daily.
>> There is a request to include 2 more databases to be included in the
>> maintenance plan. I select the maintenance plan and press "Modify" but
>> I am not able to work out how to include 2 more databases. Besides, it
>> appears that both transaction log & database backup are not deleted
>> since the plan is executed, is there something wrong ?
>> Is it possible to give me some advice ?
>> Thanks
>>
>>
>sql

how to import the database data from backup file ?

Hello,All:

I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager.

Now I want to import the backup data(generated by production server),I don't know how to do it.

Is it possible to do it ? anyone can give me a soluation ?

thanks in advanced!

->Open Enterprise manager of SQL Server 2000

-> Select Your Server

-> right click on your database

-> All Tasks -> Restore Database

-> Select from Device

-> click the button Select Devices

-> in the new window select Disk option

-> click add button

-> select your backup file in file name option

-> ok

->ok

->ok

How to import data into development database from productions database

I have two different setup, one for development and other for production.
Two different SQL server 2000 installed on two different Windows 2000
server. I want to import all data of selected tables from production server
to development server. Databases name on both seerver are different. Before
I import, I want to remove all information from selected tables from
development database. Can anyone suggest me TSQL code?
Thanks.You might want to script the tables out en masse and build them that way,
rather than "import" anything (since you're not really importing any data).
See http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Would it be easer to backup and restore production database on the
development server under different name?
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Or you can do something like this, with is not the best solution:
DECLARE @.name SYSNAME
DECLARE rs CURSOR
READ_ONLY
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--
OPEN rs
--
FETCH NEXT FROM rs INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- find your tables you need
IF @.name = 'Table1'
BEGIN
PRINT @.name
--
DELETE
FROM OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
--
INSERT INTO OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
SELECT * FROM TABLE1
--
END
END
FETCH NEXT FROM rs INTO @.name
END
--
CLOSE rs
DEALLOCATE rs
GO
But before that you have to add linked server and linked server login
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Thanks Aaron and Sergey.
Backup restore works, but sometime I just want to few tables and not all
tables from production data. what I would like to do is store all tables
name in array and for each array element first delete all records from table
(name in array) of development server and copy all records from production
server. Any idea? or any better solution?
Thanks.
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||DTS?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks Aaron and Sergey.
> Backup restore works, but sometime I just want to few tables and not all
> tables from production data. what I would like to do is store all tables
> name in array and for each array element first delete all records from
table
> (name in array) of development server and copy all records from production
> server. Any idea? or any better solution?
> Thanks.
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > I have two different setup, one for development and other for
production.
> > Two different SQL server 2000 installed on two different Windows 2000
> > server. I want to import all data of selected tables from production
> server
> > to development server. Databases name on both seerver are different.
> Before
> > I import, I want to remove all information from selected tables from
> > development database. Can anyone suggest me TSQL code?
> >
> > Thanks.
> >
> >
> >
>|||Yes, I think that would be the best option. I have not used DTS before and
SQL server is new to me, do you have any suggestion where to start?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> DTS?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > Thanks Aaron and Sergey.
> >
> > Backup restore works, but sometime I just want to few tables and not all
> > tables from production data. what I would like to do is store all tables
> > name in array and for each array element first delete all records from
> table
> > (name in array) of development server and copy all records from
production
> > server. Any idea? or any better solution?
> >
> > Thanks.
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > I have two different setup, one for development and other for
> production.
> > > Two different SQL server 2000 installed on two different Windows 2000
> > > server. I want to import all data of selected tables from production
> > server
> > > to development server. Databases name on both seerver are different.
> > Before
> > > I import, I want to remove all information from selected tables from
> > > development database. Can anyone suggest me TSQL code?
> > >
> > > Thanks.
> > >
> > >
> > >
> >
> >
>|||www.sqldts.com
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OfQ3oCvqEHA.3244@.tk2msftngp13.phx.gbl...
> Yes, I think that would be the best option. I have not used DTS before and
> SQL server is new to me, do you have any suggestion where to start?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> > DTS?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > > Thanks Aaron and Sergey.
> > >
> > > Backup restore works, but sometime I just want to few tables and not
all
> > > tables from production data. what I would like to do is store all
tables
> > > name in array and for each array element first delete all records from
> > table
> > > (name in array) of development server and copy all records from
> production
> > > server. Any idea? or any better solution?
> > >
> > > Thanks.
> > >
> > >
> > > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > > I have two different setup, one for development and other for
> > production.
> > > > Two different SQL server 2000 installed on two different Windows
2000
> > > > server. I want to import all data of selected tables from production
> > > server
> > > > to development server. Databases name on both seerver are different.
> > > Before
> > > > I import, I want to remove all information from selected tables from
> > > > development database. Can anyone suggest me TSQL code?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to import data into development database from productions database

I have two different setup, one for development and other for production.
Two different SQL server 2000 installed on two different Windows 2000
server. I want to import all data of selected tables from production server
to development server. Databases name on both seerver are different. Before
I import, I want to remove all information from selected tables from
development database. Can anyone suggest me TSQL code?
Thanks.
You might want to script the tables out en masse and build them that way,
rather than "import" anything (since you're not really importing any data).
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>
|||Thanks Aaron and Sergey.
Backup restore works, but sometime I just want to few tables and not all
tables from production data. what I would like to do is store all tables
name in array and for each array element first delete all records from table
(name in array) of development server and copy all records from production
server. Any idea? or any better solution?
Thanks.
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>
|||DTS?
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks Aaron and Sergey.
> Backup restore works, but sometime I just want to few tables and not all
> tables from production data. what I would like to do is store all tables
> name in array and for each array element first delete all records from
table[vbcol=seagreen]
> (name in array) of development server and copy all records from production
> server. Any idea? or any better solution?
> Thanks.
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
production.
> server
> Before
>
|||Yes, I think that would be the best option. I have not used DTS before and
SQL server is new to me, do you have any suggestion where to start?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> DTS?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> table
production
> production.
>
|||www.sqldts.com
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OfQ3oCvqEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Yes, I think that would be the best option. I have not used DTS before and
> SQL server is new to me, do you have any suggestion where to start?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
all[vbcol=seagreen]
tables[vbcol=seagreen]
> production
2000
>

How to import data into development database from productions data

Would it be easer to backup and restore production database on the
development server under different name?
"Sunny" wrote:

> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>
Or you can do something like this, with is not the best solution:
DECLARE @.name SYSNAME
DECLARE rs CURSOR
READ_ONLY
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN rs
FETCH NEXT FROM rs INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- find your tables you need
IF @.name = 'Table1'
BEGIN
PRINT @.name
DELETE
FROM OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
INSERT INTO OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
SELECT * FROM TABLE1
END
END
FETCH NEXT FROM rs INTO @.name
END
CLOSE rs
DEALLOCATE rs
GO
But before that you have to add linked server and linked server login
"Sunny" wrote:

> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>