copying server objects

I want to copy a database from one server to another. I'm happy about
how to do this but also want to copy a number of DTS packages, jobs and
alerts that relate to this database. Is there any way that I can copy
them or will I need to create them again on the new server.

Many Thanks

Laurence BreezeYou can copy DTS packages by opening up the current package and choosing
"Package/Save As..." from the menu bar. Be careful to make sure your
database references within the DTS package are still appropriate on the
new server.

Both jobs and alerts can be scripted and that script executed on the new
server - right click the jobs or alerts and choose "All Tasks/Generate
SQL Script...". More than one job or alert can be selected at a time if
you desire.

Good luck,
Tony Sebion

"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:433D4E58.2050802@.open.ac.uk:

> I want to copy a database from one server to another. I'm happy about
> how to do this but also want to copy a number of DTS packages, jobs and
> alerts that relate to this database. Is there any way that I can copy
> them or will I need to create them again on the new server.
> Many Thanks
> Laurence Breeze|||Hi

You may want to read
http://support.microsoft.com/defaul...b;en-us;Q314546

John

"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:433D4E58.2050802@.open.ac.uk...
>I want to copy a database from one server to another. I'm happy about how
>to do this but also want to copy a number of DTS packages, jobs and alerts
>that relate to this database. Is there any way that I can copy them or
>will I need to create them again on the new server.
> Many Thanks
> Laurence Breezesql

Copying my SQL Database to another server

Hi guys,

I have created a new SQL database (named “DB”) on my personal pc at home.

I want to copy “DB” to my job’s computer, which works on a local net with SQL server.

What should I do?

Thanks a lot,

Aldo.

you need a connection between the machines first of. If you have VPN to your work's LAN you can use it or create a backup of the database or copy the database files in the sql server directories. If you take the offline/copy approach simply bring the file(s) via removable media into work and restore/attach the database file(s). If you can get a valid remote connection then you could use maintenance plans/SSIS to copy over the database between sql servers assuming you have valid login credentiuals to the remote "work" server.|||

Hi Derek, Which / where is the file I should copy in order to get an offline copy?

I've tried copying my ".mdf" file from this path: "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\DB.mdf" but getting something strange...

Thanks in advance,

Aldo.

|||sorry, you need to detach the database first and then reattach it.|||Where can I see how to "detach" and "reatach" a dababase?|||

Hi ajliks,

I would suggest , first you take a back up of your Database (Db) from your personal pc . Copy the back up file to a CD or some portable storage device. Copy it to your Job's computer. And the restore the database from there on to your SQL Server directly. This would be the easiest way to do it.

Thanks & Regards

Mathew

|||

Hi Mathew, thanks for anwering. I want to know how to make the SQL database copy. I've tried copying the mdf file but getting some strange stuff. (I want to copy the table and views configuration).

Thanks,

Aldo.

|||

1. Open your SQL Server Enterprise Manager

2. Expand and Point to your database.

3. Right Click on your database (Db), and navigate to "All Task " -- "Backup Database" -- "Add" -- "File Name"

4. Give a proper file name and note the location where the backup file is being created.

5. Copy This file to your Job Computer

6. For Restoring on your Job computer check the following steps

7. Open Enterprise Manager on your job computer.

8. Point to Database Node and Right Click on "Restore Database"

9 . Type your Database Name "Db" on the drop down box "Restore Database as"

10 Navigate through "From device" -- "Select devices" -- "Add" - "File Name"

11 Select the backup file and click ok.

You would get ur database created on your machine

Does this makes sense to you now

|||Thanks a lot man!

Copying jobs to a new server.

We are moving SQL Server (2000) to a bigger dedicated server.
Is there a faster way to move all the jobs than creating a script for each
one and then running that script in query analyzer?
TIA
Mike
There is a "Transfer Jobs" Task in DTS That will do that for you.
Greg Jackson
PDX, Oregon
|||Hi,
There are 2 more options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"Michael Beck" <Mikeb46NoSpam@.pacbell.net> wrote in message
news:e9ySexfaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> We are moving SQL Server (2000) to a bigger dedicated server.
> Is there a faster way to move all the jobs than creating a script for each
> one and then running that script in query analyzer?
> TIA
> Mike
>

Copying jobs to a new server.

We are moving SQL Server (2000) to a bigger dedicated server.
Is there a faster way to move all the jobs than creating a script for each
one and then running that script in query analyzer?
TIA
MikeThere is a "Transfer Jobs" Task in DTS That will do that for you.
Greg Jackson
PDX, Oregon|||Hi,
There are 2 more options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"Michael Beck" <Mikeb46NoSpam@.pacbell.net> wrote in message
news:e9ySexfaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> We are moving SQL Server (2000) to a bigger dedicated server.
> Is there a faster way to move all the jobs than creating a script for each
> one and then running that script in query analyzer?
> TIA
> Mike
>

Copying jobs to a new server.

We are moving SQL Server (2000) to a bigger dedicated server.
Is there a faster way to move all the jobs than creating a script for each
one and then running that script in query analyzer?
TIA
MikeThere is a "Transfer Jobs" Task in DTS That will do that for you.
Greg Jackson
PDX, Oregon|||Hi,
There are 2 more options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"Michael Beck" <Mikeb46NoSpam@.pacbell.net> wrote in message
news:e9ySexfaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> We are moving SQL Server (2000) to a bigger dedicated server.
> Is there a faster way to move all the jobs than creating a script for each
> one and then running that script in query analyzer?
> TIA
> Mike
>

Copying instead of backing up SQL 2005 db

I just found out that our backup software (MS DPM) is conflicting with the
SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
Is there a way to copy a database? I'd like to add this to maintenance jobs
as a t-sql script, or sql server agent job.
Thanks in advance...
The BACKUP command has an option named COPY_ONLY that allows to perform
database backups without affecting the normal sequence of full, differential
and transaction log backups.
See more details on COPY_ONLY on BACKUP on BOL.
Hope this helps,
Ben Nevarez
"Artunc" wrote:

> I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
> Is there a way to copy a database? I'd like to add this to maintenance jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||My question would be why are you trying to back it up twice then? How
exactly is it conflicting?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace
> jobs.
> Is there a way to copy a database? I'd like to add this to maintenance
> jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||No other reson than having a second set of backup for importand dbs.
This is the error I am getting in DPM when backup maintenance job and DPM
backup job run for the same databases:
"DPM tried to do a SQL log backup, either as part of a backup job or a
recovery to latest point in time job. The SQL log backup job has detected a
discontinuity in the SQL log chain for SQL Server 2005 database database
SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
an express full backup runs. (ID 30140 Details: Internal error code:
0x80990D11)"
"Andrew J. Kelly" wrote:

> My question would be why are you trying to back it up twice then? How
> exactly is it conflicting?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>

Copying instead of backing up SQL 2005 db

I just found out that our backup software (MS DPM) is conflicting with the
SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
Is there a way to copy a database? I'd like to add this to maintenance jobs
as a t-sql script, or sql server agent job.
Thanks in advance...The BACKUP command has an option named COPY_ONLY that allows to perform
database backups without affecting the normal sequence of full, differential
and transaction log backups.
See more details on COPY_ONLY on BACKUP on BOL.
Hope this helps,
Ben Nevarez
"Artunc" wrote:
> I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
> Is there a way to copy a database? I'd like to add this to maintenance jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...|||My question would be why are you trying to back it up twice then? How
exactly is it conflicting?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace
> jobs.
> Is there a way to copy a database? I'd like to add this to maintenance
> jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...|||No other reson than having a second set of backup for importand dbs.
This is the error I am getting in DPM when backup maintenance job and DPM
backup job run for the same databases:
"DPM tried to do a SQL log backup, either as part of a backup job or a
recovery to latest point in time job. The SQL log backup job has detected a
discontinuity in the SQL log chain for SQL Server 2005 database database
SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
an express full backup runs. (ID 30140 Details: Internal error code:
0x80990D11)"
"Andrew J. Kelly" wrote:
> My question would be why are you trying to back it up twice then? How
> exactly is it conflicting?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
> >I just found out that our backup software (MS DPM) is conflicting with the
> > SQL Maintenance jobs. We can't use back up database task in maintenace
> > jobs.
> >
> > Is there a way to copy a database? I'd like to add this to maintenance
> > jobs
> > as a t-sql script, or sql server agent job.
> >
> > Thanks in advance...
>|||You should decide which backup method is more important to you. Let this do both database and log
backups. Let the other do only database backup, which will not disrupt the log backup chain (for the
other backup method).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:B5F12DC7-47DE-41E4-B8CD-2F9158EEC288@.microsoft.com...
> No other reson than having a second set of backup for importand dbs.
> This is the error I am getting in DPM when backup maintenance job and DPM
> backup job run for the same databases:
> "DPM tried to do a SQL log backup, either as part of a backup job or a
> recovery to latest point in time job. The SQL log backup job has detected a
> discontinuity in the SQL log chain for SQL Server 2005 database database
> SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
> an express full backup runs. (ID 30140 Details: Internal error code:
> 0x80990D11)"
>
> "Andrew J. Kelly" wrote:
>> My question would be why are you trying to back it up twice then? How
>> exactly is it conflicting?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
>> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>> >I just found out that our backup software (MS DPM) is conflicting with the
>> > SQL Maintenance jobs. We can't use back up database task in maintenace
>> > jobs.
>> >
>> > Is there a way to copy a database? I'd like to add this to maintenance
>> > jobs
>> > as a t-sql script, or sql server agent job.
>> >
>> > Thanks in advance...
>>

Copying a DTS Package and Jobs

Hi everybody,

Just want to ask how can I copy a DTS Package? Something like "Save As"...saving a DTS Package to another package name in the same server, or copying a DTS package to another server.

What I am doing right now, is I create the whole package again just to have another copy of it.

Same question with a Job.

Thank you so much. :)Why not try scripting it then just execute?|||for jobs u can right click on a job and use "generate sql script" to script it and execute it with QA.

for DTS it is a bit tricky. u can save-as an open DTS package as "structured storage file" (location drop-down). copy that disk file to another machine. and then right click on Data-Transformation-Services to open that saved package. need to save it again in SQL server of that machine.

Copying a DTS Package and Jobs

Hi everybody,

Just want to ask how can I copy a DTS Package? Something like "Save As"...saving a DTS Package to another package name in the same server, or copying a DTS package to another server.

What I am doing right now, is I create the whole package again just to have another copy of it.

Same question with a Job.

Thank you so much.Hey,

If you are using Enterprise manager, double click the package( or right click on the package, there will be a "Design package" option), it will open DTS Package designer, there you will find a menu under the main menu, click on Package and there will be a Save as... command there|||Hi Galexyus,

Thanks for taking the time to answer my query.
I found the "Save As" command already. What am I doing?
It's just in front of me, right before my very eyes. =)

Many thanks to you.
God Bless.