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 from C drive of sql server to another sever!

help!
I'm using a SQL server 2000 box and I'm a member of symins on it and I
have a remote drive mounted on the server that I can copy files to with
Explorer and the command line.
I've created a job that has the following line of SQL in it
xpcmdShell 'copy "C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\test.txt" "\\madupserver\madeupshare$\testzzzzz.txt"'
and whilst I can make this work in the command line, it tells that my access
is denied...
what can I do to run xpCmdShell with the same priviliges as I have when I'm
running the copy command via the UI of the server
Thanks in advance and regards
CharlesAthis could running as some other user
though i've never encountered this, you could check out who this is
running under using enterprize manager.|||"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:BCFB6F48-079E-4FFE-A966-22F3B4731A4A@.microsoft.com...
> help!
> I'm using a SQL server 2000 box and I'm a member of symins on it and I
> have a remote drive mounted on the server that I can copy files to with
> Explorer and the command line.
> I've created a job that has the following line of SQL in it
> xpcmdShell 'copy "C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\test.txt" "\\madupserver\madeupshare$\testzzzzz.txt"'
> and whilst I can make this work in the command line, it tells that my
> access
> is denied...
> what can I do to run xpCmdShell with the same priviliges as I have when
> I'm
> running the copy command via the UI of the server
> Thanks in advance and regards
> CharlesA
>
If the SQL Server login running the account is a member of the symins
group, then xp_cmdshell will use the Windows Security account associated
with the MSSQLServer service. If the SQL Server login is not a member of
symins, then xp_cmdshell will run in the context of the SQL Server Agent
Proxy account.
Given your scenario, you need to ensure that the Windows account has the
appropriate privileges on the share.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Rick,
I'm pretty sure we've had an email dialog before because I remember using
your magnificent book 'TY SQL server 2000 in 21 days' (From SAMS) which got
me into the right thinking mode about SQL server (after years of Access dev
work) I can heartily recommend this work to anyone wishing to understand how
to be a first-time DBA
I hope you're updating it for 2005!
Thanks for you helpful post,
Regards
CharlesAsqlsql

Copy Database Wizard Default Destination Folder

We have installed Microsoft SQL Server 2005 to the deafult folder. However, we have another larger drive for our database files. We have changed the Server Properties > Database Settings to have the correct "Database default locations" that we want. However, when we use the Copy Database Wizard to bring over the many user databases on our existing server, the wizard always uses the original installation folder as the Destination Folders.

Is this a bug, or is there some way to change the defaults for these folders so we don't have to on every one?

Thanks

I have the same problem but by mistake I've found a way to modify the destination folder. On the grid with the destination folder click the one with the path. A label will appear over the grid and then you could edit the path. Cheers and hope that this will help you.

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. When I drag and drop the file it tells me "insufficient amount of space" even though the hard drive has plenty of storage. Please help
Hi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. Wh
en I drag and drop the file it tells me "insufficient amount of space" even
though the hard drive has plenty of storage. Please helpHi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help

Copy Database

I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive. When I drag and drop the file it tells me "insufficient amount of space" even though the hard drive has plenty of storage. Please helpHi,
Can you please post your query to Windows 2000 public group.
Thanks
Hari
MCDBA
"Johnson" <anonymous@.discussions.microsoft.com> wrote in message
news:97CC8EA4-9B6E-422C-AB0A-A017FBE603BB@.microsoft.com...
> I am trying to copy a 125GB .mdf database file to a 250GB usb hard drive.
When I drag and drop the file it tells me "insufficient amount of space"
even though the hard drive has plenty of storage. Please help