Copy jobs

How best to copy jobs from test instance to production?Well, you can script them and then run scripts on the production...
MC
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:D4148CA3-E474-4800-937F-2AD2D32CBDA6@.microsoft.com...
> How best to copy jobs from test instance to production?|||Is this SQL Server 2000? or SQL Server 2005? However, you may use object
transfer using DTS or "Transfer Job tasks" from toolbox of SSIS. (You may
also script out all the jobs and execute the script on the new box)
Few things to keep in mind:
Verify that you have appropriate owners for each job.
Verify that approriate databases have been selected for those jobs
Verify that notification properties are set to appropriate users (as it may
differ from test to production environment)
Hope this helps.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fdefault.aspx
"Gerry M" wrote:
> How best to copy jobs from test instance to production?|||"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:D4148CA3-E474-4800-937F-2AD2D32CBDA6@.microsoft.com...
> How best to copy jobs from test instance to production?
SQL agent jobs? DTS Jobs? SSIS Jobs?
2000, or 2005?
Try a right-click on the job name and look for a script option. If the job
calls a procedure, then make sure that is installed first.
Other than that, you should have documented and scripted putting the job in
place on dev, or QA and then you wouldn't have had the problem to begin
with.

Copy Database Wizard : where is it?

I need to copy an existing database within my SQL server 2005 to a new database within the same SQL Server instance. I've read a lot about the Copy Database Wizard but can't see this option within any menu of SQL Server 2005 Management Studio. As far as I know this option should be shown after right clicking at a database and selecting of 'Tasks'. It shows other options as Detach, Back-up, Restore but not he Copy function. I also don't see any wizard option when selecting 'Management'. I've already updated to SQL Server 2005 SP2 but that didn't help.

I've installed MS SQL Server 2005 from my MSDN-Professional subscription. After installation, while SQL Server Management Studio is shown without an 'Express' addition, it has named the SQL Server instance as Servername\SQLExpress which I find confusing. The full name of SQL Server Management Studio is shown with the version number 9.0.3042 which ensures that it is SQL Server 2005 SP2 version and not the Express edition.

Do I need to install something additionaly to display the Copy Database Wizard? Very frustrating that this option is not shown and there is no documentation how to toget it. Is this maybe a known bug?

regards

Oscar

Copy Database should be the last item on the Tasks menu.

On my machine with developer edition:

SELECT @.@.VERSION;

Returns: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6000: )

SELECT SERVERPROPERTY('Edition ');

Returns: Developer Edition

|||

In my case:

SELECT @.@.VERSION;

Returns : Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

SELECT SERVERPROPERTY('Edition ');

Returns : Express Edition with Advanced Services

So it looks like I've installed the Express edition. This is hard to believe since I used my Official MSDN Subscription to install SQL Server 2005. Important to inform, I even can't update the Manager Studio with the SQL Server Studio Express SP2 since it reports that the Express edition has not been installed.

Besides, As I've noticed, The SQL Server 2005 Express users have this Copy Database option and in my case it doesn't show this menu option.

Where did you got your Developer edition from?

|||

I have an MSDN subscription I can't remember the new name...it replaced universal.

You can download the trial edition from:

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

As I already own an MSDN subscription why should I download a trial edition?

Can anyone reply whether the Express edition lacks the Copy Database option like my case?

|||

Oscar99 wrote:

Can anyone reply whether the Express edition lacks the Copy Database option like my case?

Correct, the Express edition does not have the Copy Database Wizard. If you have an MSDN subscription you can download SQL Server Developer Edition. If you don't, you can buy it for $50.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

i don't think so

In my case:

SELECT @.@.VERSION;

Returns: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6000: )

SELECT SERVERPROPERTY('Edition ');

Returns: Developer Edition

but i ca'nt see this option

|||

Thanks Paul,

Finally everything clear now.

Meanwhile I've reinstalled my MSDN july 2006 version and now the option for CDW shows up! As to MS I don't understand why MS removes such basic and simple functions within the Express edition.

regards,

Oscar

|||I upgraded a SQLExpress instance to Standard and the copy command did not come with it. It was enough of a reason for me to uninstall Express and reinstall Standard and restore the pertinent dbs. What a hassle. I'd call that a bug.

Copy Database Wizard (MSSQL 2000)

Trying to copy a mssql 7 db from a NT 4.0 server to a mssql 2000 default instance on a Windows 2003 server using the Copy Database Wizard. The Wizard indicates that the database is OK to copy but that the "Files on the source have the same name as on the destination or there is not enough free disk space on the destination." Both of these statements are false. The db doesn't exsist on the destination and there is no shortage of disk space. Any help?

pse post the full error with error number and description.... BTW it is always better to use Backup/restore to transfer database ...

Madhu

Copy Database from SQL 2000 to SQL 2005

I'm trying to use "Copy Database" to copy a database from a SQL2000 instance
to a SQL2005 instance; everthing looks ok until I execute the operation and
get an error message on the action "Create Package" of "One or more
arguments are invalid (Copy Database Wizard)"
I've gone over it and over it and cannot see where the problem might be ...
TIA for any suggestions ..
LI would use SSIS for this instead of DTS
"Liz" <liz@.tiredofspam.com> wrote in message
news:e82zA%2346HHA.1164@.TK2MSFTNGP02.phx.gbl...
> I'm trying to use "Copy Database" to copy a database from a SQL2000
> instance to a SQL2005 instance; everthing looks ok until I execute the
> operation and get an error message on the action "Create Package" of "One
> or more arguments are invalid (Copy Database Wizard)"
> I've gone over it and over it and cannot see where the problem might be
> ...
> TIA for any suggestions ..
> L
>

Copy Database (SQL Management Object method)

I tried to copy a db instance from one SQL 2005 installation to another. The transfer/copy wizard in management studio showed nothing - a blank - in the "Destination database files" section. That struck me as bad, but the wizard would not let me add any files. Then when I tried to execute the transfer/copy, I got this error message:

Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->The PRIMARY filegroup must have at least one file.

I suppose I would not have had that error message, if the wizard allowed me to add a filegroup. What is causing this?

Are you copying or moving? Do you have to use SMO? If not, just make a backup of the database and restore it to the other server. If you are moving the database, detach the files and reattach them on the other server. Hope this helps.
Tim|||Run SP_HELPFILEGROUP on source server to see what are the contents displayed, as explained you can either take help of backup/restore of detach/attach method.

Copy Data....

Greetings,
Here's my problem. I have 2 databases, both on different instance installs,
with two identical tables. I want to copy the "Region" column data from one
table to the "Region" column of the other table. I thought this would be a
simple copy and paste situation, but apparently not. Any ideas?
Thanks
Ken S.You could either us DTS (export/Import) with a query that
select's only the column your interested in.
Or you could BCP with a select statment like so...
bcp "select column_name FROM db.dbo.table(nolock)"
queryout \\server\d$\ouput.txt -N -T -SServername -
eerror.err -b10000
>--Original Message--
>Greetings,
>Here's my problem. I have 2 databases, both on different
instance installs,
>with two identical tables. I want to copy the "Region"
column data from one
>table to the "Region" column of the other table. I
thought this would be a
>simple copy and paste situation, but apparently not. Any
ideas?
>Thanks
>Ken S.
>
>.
>|||OK great...let me try the DTS option first...
<anonymous@.discussions.microsoft.com> wrote in message
news:020401c3c4fe$62585800$a001280a@.phx.gbl...
> You could either us DTS (export/Import) with a query that
> select's only the column your interested in.
> Or you could BCP with a select statment like so...
> bcp "select column_name FROM db.dbo.table(nolock)"
> queryout \\server\d$\ouput.txt -N -T -SServername -
> eerror.err -b10000
> >--Original Message--
> >Greetings,
> >
> >Here's my problem. I have 2 databases, both on different
> instance installs,
> >with two identical tables. I want to copy the "Region"
> column data from one
> >table to the "Region" column of the other table. I
> thought this would be a
> >simple copy and paste situation, but apparently not. Any
> ideas?
> >
> >Thanks
> >
> >Ken S.
> >
> >
> >.
> >

Copy and move the database to another server

I have a SQL Server database(NWind) available in a stand alone server. I have
a CD writer in this stand alone server. I need to create an instance of this
NWind database into another server.
How can I copy the database from my first server into my second server.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
|||If I use Detach database does the database in the first server be still
available once I move it to another server.
"Shadow" wrote:

> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/?id=314546
>

Copy and move the database to another server

I have a SQL Server database(NWind) available in a stand alone server. I hav
e
a CD writer in this stand alone server. I need to create an instance of this
NWind database into another server.
How can I copy the database from my first server into my second server.HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546|||If I use Detach database does the database in the first server be still
available once I move it to another server.
"Shadow" wrote:

> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/?id=314546
>

Copy and move the database to another server

I have a SQL Server database(NWind) available in a stand alone server. I have
a CD writer in this stand alone server. I need to create an instance of this
NWind database into another server.
How can I copy the database from my first server into my second server.HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546|||If I use Detach database does the database in the first server be still
available once I move it to another server.
"Shadow" wrote:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/?id=314546
>

Copy a database with copy the .mdf file and attaching it with a new name?

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

Or is the datebase name part of the .mdf file?

Regards
Markus

Hi,

I had no problems doing just that and the documentation only mentions to detach the database before taking the database file (or files) to copy them for later attaching. So I guess you should not just stop sql server and copy the files, but detach db, copy db files, reattach db.

--
SvenC

|||

hi Markus,

Markus Sch. wrote:

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

please be carefull not to overwrite existing database files... but the "scenario" is viable...

Or is the datebase name part of the .mdf file?

Regards
Markus

only User Instances use the Database Name ( and path) to dynamically build database names..

regards

|||

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

|||

hi,

rf wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

I said, to be carefull ..

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

yes, this works as well, but, again, you have to be carefull with the actual physical database file as well.. you have to "move" them according to your needs not overwriting existing database files...

regards

|||

rf571786 wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

Hello rf,

if you detach a database with Management Studio Express, copy the .mdf and .ldf file, and then try to attach the copied files with Databases - Attach, then in the Attach Dialog the Attach As and Current File Path textboxes still have the old name.

The values are not changed to the new .mdf filename.

You have to type in the new database name in the Attach As textbox and the new file names in the Current File Path textbox. Then it works fine.

Or use a Sql Script. The InfoItems.mdf and Infotems_log.ldf files where copied to InfoItems_Test.mdf and Infotems_Test_Log.ldf and then attached with this Sql Script.

But the logical filename in Database Properties - Files - LogicalName still is InfoItems? It should be InfoItems_Test because the Name Clause was specified in the Sql statement?

Regards Markus

USE [master]
GO
CREATE DATABASE [InfoItems_Test] ON
(NAME = InfoItems_Test,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test.mdf' ),
(NAME = InfoItems_Test_Log,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test_log.ldf' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'InfoItems_Test'
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [InfoItems_Test].dbo.sp_changedbowner @.loginame=N'....\....', @.map=false
GO

Copy a database with copy the .mdf file and attaching it with a new name?

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

Or is the datebase name part of the .mdf file?

Regards
Markus

Hi,

I had no problems doing just that and the documentation only mentions to detach the database before taking the database file (or files) to copy them for later attaching. So I guess you should not just stop sql server and copy the files, but detach db, copy db files, reattach db.

--
SvenC

|||

hi Markus,

Markus Sch. wrote:

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

please be carefull not to overwrite existing database files... but the "scenario" is viable...

Or is the datebase name part of the .mdf file?

Regards
Markus

only User Instances use the Database Name ( and path) to dynamically build database names..

regards

|||

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

|||

hi,

rf wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

I said, to be carefull ..

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

yes, this works as well, but, again, you have to be carefull with the actual physical database file as well.. you have to "move" them according to your needs not overwriting existing database files...

regards

|||

rf571786 wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

Hello rf,

if you detach a database with Management Studio Express, copy the .mdf and .ldf file, and then try to attach the copied files with Databases - Attach, then in the Attach Dialog the Attach As and Current File Path textboxes still have the old name.

The values are not changed to the new .mdf filename.

You have to type in the new database name in the Attach As textbox and the new file names in the Current File Path textbox. Then it works fine.

Or use a Sql Script. The InfoItems.mdf and Infotems_log.ldf files where copied to InfoItems_Test.mdf and Infotems_Test_Log.ldf and then attached with this Sql Script.

But the logical filename in Database Properties - Files - LogicalName still is InfoItems? It should be InfoItems_Test because the Name Clause was specified in the Sql statement?

Regards Markus

USE [master]
GO
CREATE DATABASE [InfoItems_Test] ON
(NAME = InfoItems_Test,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test.mdf' ),
(NAME = InfoItems_Test_Log,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test_log.ldf' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'InfoItems_Test'
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [InfoItems_Test].dbo.sp_changedbowner @.loginame=N'....\....', @.map=false
GO

Copy a database with copy the .mdf file and attaching it with a new name?

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

Or is the datebase name part of the .mdf file?

Regards
Markus

Hi,

I had no problems doing just that and the documentation only mentions to detach the database before taking the database file (or files) to copy them for later attaching. So I guess you should not just stop sql server and copy the files, but detach db, copy db files, reattach db.

--
SvenC

|||

hi Markus,

Markus Sch. wrote:

Hello,

if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.

please be carefull not to overwrite existing database files... but the "scenario" is viable...

Or is the datebase name part of the .mdf file?

Regards
Markus

only User Instances use the Database Name ( and path) to dynamically build database names..

regards

|||

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

|||

hi,

rf wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

I said, to be carefull ..

There is a much easiere way that I finally figured out:

- right click on the database you want to copy

- select "Tasks" and subsequently select "Backup" and do a backup.

- right click on "Databases"

- select "Restore database"

- Enter the name of a new database (your copy target) and select the database to be copied as source database

- click "OK" (SQL Server creates a new database from the backup which means that you actually copied the original database)

If you only wanted the database design and not the content, well ... Delete from <tablename> ...

yes, this works as well, but, again, you have to be carefull with the actual physical database file as well.. you have to "move" them according to your needs not overwriting existing database files...

regards

|||

rf571786 wrote:

I tried this - and it did not really work. I could attach the copied database after renaming it and the original database, but then it would still point to the first physical database, i.e. the original .mdf-file ... Later the database wasn't recognized anymore ... etc.

Hello rf,

if you detach a database with Management Studio Express, copy the .mdf and .ldf file, and then try to attach the copied files with Databases - Attach, then in the Attach Dialog the Attach As and Current File Path textboxes still have the old name.

The values are not changed to the new .mdf filename.

You have to type in the new database name in the Attach As textbox and the new file names in the Current File Path textbox. Then it works fine.

Or use a Sql Script. The InfoItems.mdf and Infotems_log.ldf files where copied to InfoItems_Test.mdf and Infotems_Test_Log.ldf and then attached with this Sql Script.

But the logical filename in Database Properties - Files - LogicalName still is InfoItems? It should be InfoItems_Test because the Name Clause was specified in the Sql statement?

Regards Markus

USE [master]
GO
CREATE DATABASE [InfoItems_Test] ON
(NAME = InfoItems_Test,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test.mdf' ),
(NAME = InfoItems_Test_Log,
FILENAME = N'E:\InfoItems_Dev\Development\....\Data\DB\InfoItems_Test_log.ldf' )
FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'InfoItems_Test'
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [InfoItems_Test].dbo.sp_changedbowner @.loginame=N'....\....', @.map=false
GO

converting sql_variant column type to int

Hi,
I've got a table which has got a sql_variant column (sql server instance is 2000). the current data in that column are all numbers so can be converted to int.
I want to change the column type from sql_variant to int for that column. I have tried

alter table mytable alter column mycolumn int

and it complains
Disallowed implicit conversion from data type sql_variant to data type int, table 'mydatabase.dbo.mytable ', column 'mycolumn '. Use the CONVERT function to run this query.

I can do this task using EM but what that does is creating a new table temp table with int column type transferring data , dropping the original table and renaming the temp table to initial name.

therefore I need T-SQL command
Thanks

Really, I think I would suggest the same thing that you describe the EM doing -- drop the constraints on the original table, create the new table, copy in the data and then drop the old table. Can we get second and third opinions here?|||problem: this is template for a table in production server, which can not be dropped and also there are many constraints that points to this table. so using EM is not an option.

|||I would start by scripting out the table from the EM and massaging the generated script. Again, I'm not sure that you are going to be able to get out of creating a new version of the table and I would like additional opinions on this.|||

See if this helps.

create table dbo.t1 (

c1 sql_variant null

)

go

insert into dbo.t1 values(1)

go

alter table dbo.t1

add c2 int

go

update dbo.t1

set c2 = cast(c1 as int)

go

alter table dbo.t1

drop column c1

go

exec sp_rename 'dbo.t1.c2', 'c1', 'COLUMN'

go

select *

from dbo.t1

go

drop table dbo.t1

go

If there are indexes or constraints referencing column [c1], you have to drop them first and recreating them after renaming new column.

AMB

|||good solution, but column order is important for me. I think I can got to syscolumn table and change the column order there but this won't work on 2005|||

Kolf,

> but column order is important for me

Sorry about it, that is the beauty about relational dbs, the position of the column is not important at all. If that is the case, there is not other option than creating a new table, move all data, drop constraints refrencing the table, drop old table, recreate constraints and indexes. That is what" Enterprise Manager" or "Management Studio" does.

AMB