Copy one db with jobs to another hard drive

Hi all,
I'm replaced our current hard drive with another and I need to know the
best way to transfer all the sql data with jobs.
Going from:
NT4 Server running SQL Server 7
to:
W2K Server running SQL Server 7
I need to transfer all sql data including jobs to the new hard drive.
Can I just copy all the dbs (Master, User defined, etc) to the new hard
drive and be set?
What's the best way to handle this.
Thank you,
BillWilliam,
Go with Backup and Restore procedure.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"William Oliveri" <wuji@.bigvalley.net> wrote in message
news:eV2PpPFkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm replaced our current hard drive with another and I need to know the
> best way to transfer all the sql data with jobs.
> Going from:
> NT4 Server running SQL Server 7
> to:
> W2K Server running SQL Server 7
> I need to transfer all sql data including jobs to the new hard drive.
> Can I just copy all the dbs (Master, User defined, etc) to the new hard
> drive and be set?
> What's the best way to handle this.
> Thank you,
> Bill
>|||> Can I just copy all the dbs (Master, User defined, etc) to the new hard
> drive and be set?
>
No there are two ways:
1)
detach/attach
take care that no user is connected (kill SPID´s or : alter database
set restricted_user with rollback immediate)
all files you have to move you find at sysfiles (master)
exec sp_detach_db DBName
copy or move the files
at the new Server:
exec sp_attach_db DBName, FileName1, FileName2...FileName16
e.g exec sp_attach_db 'MyDB',
'e:\SQLData\MyDB.mdf','e:\SQLLog\MyDB.ldf'
2)
Backup/Restore
Backup Database DBName To Disk='PathName'
then on the new machine:
Restore Database DBName
from Disk = 'PathName'
with move 'MyDB' TO 'g:\SQLData\MyDB.mdf',
move 'MyDb_log' TO 'g:\SQLLog\MyDB.ldf'
hope this was helpful
Peter

copy database from one server to another

Hi:

I have a MSSQL 2005 database in current server, since we are going to shift to another server, I need to copy the database to the new server for testing.

Can you please let me know what is the best way to do that? I have tried to use detach and attach, or "back up the database and then restore on the other server.", but seems not working for me. I want step by step instruction since I am pretty new for the DB migration.

Thanks.

Jt

Hello Jt,

Do you know why the detach/attach and backup/restore are not working properly? They are created to do these kind of actions.

Have a look at the following article to move a database with attach/detach:http://support.microsoft.com/kb/224071

or to do the restore:http://msdn2.microsoft.com/en-us/library/ms186390.aspx

|||

Have a look atthis post on Scott Gu's blog, detailing the use of the recently releasedDatabase Publishing Wizard. It explains how to transfer a database schema and/or it's data to another server. It basically outputs a script file containing all the commands, which you would then save and execute on the server you wish to transfer it to.

Hope that helps.

Copy current record (again)

Hi there,
What I have is an appointments diary, what I want is to be able to move
details from one record to another (when someone calls to move their
appointment from one day & time to another).
Tablename: AppDetails includes the following fields:
Appdate smalldatetime (PK)
Apptime varchar(!) 10 (PK) - this not one of my tables!
Appname varchar 100
Address1....Address3 all varchar 100
Postcode varchar 20
The table is filled with 24 times (9.00am, 9.30am...8.30pm) per date over
ten years!
The user sees one record to view and what I'd like is an sp to copy all the
values in the visible record (not Appdate or Apptime) into @.Variables that
could be called from a button and another to paste them onto the new record
(the user will select the new record).
Where I'm stuck is how to select the Current record.
Help please
Thanks
Paul> Where I'm stuck is how to select the Current record.
You select a row by it's *Key*. As far as SQL is concerned there is no
such thing as a "current record" - it is your client app's job to pass
the key back to SQL Server.
How does this question differ from the one you asked last w?
http://www.google.co.uk/groups?selm...r />
roups.com
You're likely to get more help if you follow the suggestion I made then
about posting full details.
David Portas
SQL Server MVP
--|||Sounds like an ideal question for Joe to reply ;)
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Paul in Harrow" <PaulinHarrow@.discussions.microsoft.com> wrote in message
news:7BF7D25A-2BBE-4942-8921-2A6E4FFF83E0@.microsoft.com...
> Hi there,
> What I have is an appointments diary, what I want is to be able to move
> details from one record to another (when someone calls to move their
> appointment from one day & time to another).
> Tablename: AppDetails includes the following fields:
> Appdate smalldatetime (PK)
> Apptime varchar(!) 10 (PK) - this not one of my tables!
> Appname varchar 100
> Address1....Address3 all varchar 100
> Postcode varchar 20
> The table is filled with 24 times (9.00am, 9.30am...8.30pm) per date over
> ten years!
> The user sees one record to view and what I'd like is an sp to copy all
> the
> values in the visible record (not Appdate or Apptime) into @.Variables that
> could be called from a button and another to paste them onto the new
> record
> (the user will select the new record).
> Where I'm stuck is how to select the Current record.
> Help please
> Thanks
> Paul
>|||"David Portas" wrote:

> You select a row by it's *Key*. As far as SQL is concerned there is no
> such thing as a "current record" - it is your client app's job to pass
> the key back to SQL Server.
> How does this question differ from the one you asked last w?
It's the same question but I've finally got some some time to work on it.
Thanks
Paul|||Then, just to re-iterate: SQL knows nothing about what row is "current"
within your application. I imagine the logical sequence will be:
1. User chooses an appointment time.
2. User selects "copy". Application saves data for the row or just the
key values for the row.
3. User chooses a new appointment time.
4. User selects "paste". Application updates the table based on the key
values of the new row (@.ad2 and @.at2 in my original example) and the
data saved in Step 2.
Step 4 is the only SQL data modification operation.
David Portas
SQL Server MVP
--|||"David Portas" wrote:
> Then, just to re-iterate: SQL knows nothing about what row is "current"
> within your application. I imagine the logical sequence will be:
> 1. User chooses an appointment time.
> 2. User selects "copy". Application saves data for the row or just the
> key values for the row.
> 3. User chooses a new appointment time.
> 4. User selects "paste". Application updates the table based on the key
> values of the new row (@.ad2 and @.at2 in my original example) and the
> data saved in Step 2.
Spot on
I've been trying to avoid doing any of this with the front end app as it's
sooo slow.

> Step 4 is the only SQL data modification operation.
Many thanks for your help
Paul

Copy current record

Hi There
Tablename "tblAppDetails"
Which includes the following fields
AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
varchar 100, AppAddress1 varchar 100
What I'd like are 2 SP's, the first to select the all the fields (other than
AppDate & AppTime) for the current record and the second to paste those
values onto another record in the same table (this is for moving someone fro
m
one appointment to another. The user sees one record to view and'll click a
button to copy, select a new record and click another button to paste) .
Is this something to do with Fetch?
Many thanks
PaulPaul in Harrow wrote:
> Hi There
> Tablename "tblAppDetails"
> Which includes the following fields
> AppDate smalldatetime, Apptime varchar 10 (both make the PK), AppName
> varchar 100, AppAddress1 varchar 100
> What I'd like are 2 SP's, the first to select the all the fields
> (other than AppDate & AppTime) for the current record and the second
> to paste those values onto another record in the same table (this is
> for moving someone from one appointment to another. The user sees one
> record to view and'll click a button to copy, select a new record and
> click another button to paste) .
> Is this something to do with Fetch?
> Many thanks
> Paul
The PK in the table seems a little iffy. What prevents two records from
having the same date and time?
With that PK, why not just change the date and time to make the change.
I assume by "current record" you mean a row of data that a user is
editing. I'm not sure what you mean by "another record" because of the
PK.
Could you explain in a little more detail what data you want to move,
where it's going and if any other tables are involved in the process.
David Gugick
Imceda Software
www.imceda.com|||You don't need cursors and multiple SPs. A single UPDATE will do it:
UPDATE tblAppDetails
SET appname =
(SELECT appname
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1),
(SELECT appaddress1
FROM tblAppDetails
WHERE appdate = @.ad1
AND apptime = @.at1)
WHERE appdate = @.ad2
AND apptime = @.at2
(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively.
Why are you using separate columns for date and time when SMALLDATETIME
stores both?
David Portas
SQL Server MVP
--|||Thanks both Davids
"David Portas" wrote:
"Why are you using separate columns for date and time when SMALLDATETIME
stores both?"
I know, but this is not my table and it's been pre-filled with 24 AppTimes
for each AppDate for the next five years!
"(@.ad1, @.at1) and (@.ad2, @.at2) specify the key values of the source and
target rows respectively."
Er, how? @.ad1 &@.at1 are from the current record but how does it know which
is the second record?|||In my query you need to specify the keys for both the source row and
the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
"second row"? You didn't specify how the target row for the change is
to be determined so I assumed you were supplying that information as
parameters in your SP. If you wanted something else then please give us
a full description of the problem: DDL, sample data INSERT statements
and show your required end result. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||David,
I'll do all this on Monday
Paul
"David Portas" wrote:

> In my query you need to specify the keys for both the source row and
> the target row (@.ad1, @.at1) and (@.ad2, @.at2). What do you mean by
> "second row"? You didn't specify how the target row for the change is
> to be determined so I assumed you were supplying that information as
> parameters in your SP. If you wanted something else then please give us
> a full description of the problem: DDL, sample data INSERT statements
> and show your required end result. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>

Converting Timestamp to varchar or concatenating it with a string

Hello,

I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.

--code within cursor

SELECT'

DECLARE @.Result INT

EXEC @.Result = DELETE_DOCUMENT

@.DocumentID = ' + STR(DocumentID) + ',

@.TimeStamp =' + CAST([Timestamp] as varchar) + ',

-- CHECK RESULT AND STATUS

-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL

FROMDocument

The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values

T

T?

T-

xnT

T!

T"

T#

T$

T%

T&

T'

T(

T)

T*

T+

T,

instead of

0x0000000013540F1C

0x0000000013540F1E

0x0000000013540F1F

0x0000000013786EDE

0x0000000013540F21

0x0000000013540F22

0x0000000013540F23

0x0000000013540F24

0x0000000013540F25

0x0000000013540F26

0x0000000013540F27

0x0000000013540F28

0x0000000013540F29

0x0000000013540F2A

0x0000000013540F2B

0x0000000013540F2C

which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.

Thanks

Sam

use tempdb

go

create table dbo.t1 (

c1 timestamp

)

go

insert into dbo.t1 default values

go

select

c1,

master.sys.fn_varbintohexstr(cast(c1 as varbinary(8)))

from

dbo.t1

go

drop table dbo.t1

go

AMB

|||

hi

Thanks for that I have been looking for that kind of function every where

Regards

Sam

Converting Text to number

Hello there
I have table with field which is a text field that provide numbers and text
I would like to drop all the records that on the current field has text and
leave only the records with the number on the field
For this i've tried to use the convertion process but it gave me an error
when the data is text
Is there a way to know if the data is text or number so i can deal with it?
any help would be useful
roy@.atidsm.co.il
' 050-7709399"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in
news:#Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl:

> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an
> error when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
Try ISNUMERIC(expression)|||Although ISNUMERIC will provide the desired results in many cases, it will
also consider some obscure numeric values to be numeric. You can use LIKE
to test for integer digits. The example shows some differences between
ISNUMERIC and LIKE.
CREATE TABLE MyTable
(
MyNumber varchar(10)
)
GO
INSERT INTO MyTable VALUES('')
INSERT INTO MyTable VALUES(' ')
INSERT INTO MyTable VALUES('1')
INSERT INTO MyTable VALUES(' 2')
INSERT INTO MyTable VALUES('3 ')
INSERT INTO MyTable VALUES('1D')
INSERT INTO MyTable VALUES('0D1')
INSERT INTO MyTable VALUES('0E1')
SELECT *
FROM MyTable
WHERE ISNUMERIC(MyNumber) = 0
SELECT *
FROM MyTable
WHERE MyNumber LIKE '%[^0-9]%' OR
DATALENGTH(MyNumber) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...
> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and
> leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an error
> when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
> any help would be useful
> --
>
> roy@.atidsm.co.il
> ' 050-7709399
>|||ISNUMERIC also interprets '.', '-' and '+' as numeric.
Dan Guzman wrote:
> Although ISNUMERIC will provide the desired results in many cases, it
> will also consider some obscure numeric values to be numeric. You
> can use LIKE to test for integer digits. The example shows some
> differences between ISNUMERIC and LIKE.
> CREATE TABLE MyTable
> (
> MyNumber varchar(10)
> )
> GO
> INSERT INTO MyTable VALUES('')
> INSERT INTO MyTable VALUES(' ')
> INSERT INTO MyTable VALUES('1')
> INSERT INTO MyTable VALUES(' 2')
> INSERT INTO MyTable VALUES('3 ')
> INSERT INTO MyTable VALUES('1D')
> INSERT INTO MyTable VALUES('0D1')
> INSERT INTO MyTable VALUES('0E1')
> SELECT *
> FROM MyTable
> WHERE ISNUMERIC(MyNumber) = 0
> SELECT *
> FROM MyTable
> WHERE MyNumber LIKE '%[^0-9]%' OR
> DATALENGTH(MyNumber) = 0
>
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...

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