Move database file: files

Showing posts with label files. Show all posts

Friday, March 30, 2012

Moving C2 Audits

I'm not seeing or finding how to move the location of the C2 Audit files. I
used the pretty standard script to enable C2 Auditing:
EXEC sp_configure 'show advanced option', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'c2 audit mode', 1
RECONFIGURE WITH OVERRIDE
However, it would be nice if I could move them from the C:\ drive to a
folder already being backed up since we need these audit trails for at least
one year (Government thing). Any ideas? I'll keep looking, but I know we
get pretty quick responses in here also!
Thanks
AllenSorry , what is C2 Audit files ?
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>|||Hi,
By default SQL Server logs the C2 trace into data folder which you mentioned
during SQL Server installation.
The file can be copied to a different location by writing a batch file and
scheduled using SQL Server Agent every 1 hour.
Thanks
Hari
SQL Server MVP
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>|||C2 audits are predefined audits you can run to trace specific events on a
SQL Server. It is compliant with Government auditing policies and
procedures.
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
A Google search will produce more results for you.
In SQL 2005 it is a standard checkbox you can turn on by checking the
Properties of a Server, then clicking on Security. In SQL 2000, you have to
run the script I show below.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uX%23l0rStGHA.4748@.TK2MSFTNGP03.phx.gbl...
> Sorry , what is C2 Audit files ?
>
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
>|||Yeah, not really what I had in mind though. Just like I can change the
location of my data files and log files, I want my traces to go to another
location as well. This should be something we can run a script to configure
IMHO. I don't want to write batch files for each of my 70 SQL Servers ;-)
The default size of an audit file is 200 MB, and with C2 auditing, that will
be reached daily for some servers - no way around it. Furthermore, you
can't copy an active trace file, so I would have to continually check back
to see if the thing is 200 MB yet, then copy it to another location on the
same computer - awaiting pickup by our backup system. That is way too much
disk activity for me.
I would like to see the ability to write them directly to another location -
other than the original /data folder.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uY92JSUtGHA.4968@.TK2MSFTNGP03.phx.gbl...
> Hi,
> By default SQL Server logs the C2 trace into data folder which you
> mentioned during SQL Server installation.
> The file can be copied to a different location by writing a batch file and
> scheduled using SQL Server Agent every 1 hour.
> Thanks
> Hari
> SQL Server MVP
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
>|||Ok - I've been told that audits get written to the default data directory.
However, we have dedicated drives for log and data files (E: and F:, for
example). What I had to do to get the C2 audits (trace files) to write to
our data directory was:
1) launch Enterprise Manager
2) right-click and Properties
3) Database Settings tab
4) Change 'default data directory' to F:\<location>
5) Stop and restart the services
Good to go. The traces will begin writing to the drive you specified as
your default data directory.
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>sql

Moving ALL DBs to new server

I am building a new SQL server and would like to copy ALL DBs (user and
system) to it. The server will eventually have the same name, but the
DB files will be in different locations (paths). Are there any links I
should check out or MSFT KB articles? I tried searching but wasn't
able to get anything for my specific issues. (Also, upgrading Windows
2000 to Windows 2003, but still on SQL Server 2000 SP3.)
I found this one.
http://support.microsoft.com/default.aspx/kb/224071
"tommydogs@.sickoflosers.com" wrote:

> I am building a new SQL server and would like to copy ALL DBs (user and
> system) to it. The server will eventually have the same name, but the
> DB files will be in different locations (paths). Are there any links I
> should check out or MSFT KB articles? I tried searching but wasn't
> able to get anything for my specific issues. (Also, upgrading Windows
> 2000 to Windows 2003, but still on SQL Server 2000 SP3.)
>
|||In addition to the link posted by Jen, here are some more articles that you
may find helpful:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
Keith
<tommydogs@.sickoflosers.com> wrote in message
news:1107283271.072157.290890@.c13g2000cwb.googlegr oups.com...
> I am building a new SQL server and would like to copy ALL DBs (user and
> system) to it. The server will eventually have the same name, but the
> DB files will be in different locations (paths). Are there any links I
> should check out or MSFT KB articles? I tried searching but wasn't
> able to get anything for my specific issues. (Also, upgrading Windows
> 2000 to Windows 2003, but still on SQL Server 2000 SP3.)
>
|||Thanks for the help! One of the questions I have that doesn't seem to
be answered is this: I detach all user DBs from my current SQL server,
then copy over the master, model, msdb, & tempdb system DBs to the new
server. Can I then copy and reattach user DB files?
Keith Kratochvil wrote:
> In addition to the link posted by Jen, here are some more articles
that you
> may find helpful:
>
>
> INF: Disaster Recovery Articles for Microsoft SQL Server
> http://www.support.microsoft.com/?id=307775
> INF: Moving SQL Server databases to a new location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.support.microsoft.com/?id=314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/?id=221465
> PRB: User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
Incomplete
> http://www.support.microsoft.com/?id=274188
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases
When[vbcol=seagreen]
> Database Is Moved
> http://www.support.microsoft.com/?id=298897
>
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
>
> --
> Keith
>
> <tommydogs@.sickoflosers.com> wrote in message
> news:1107283271.072157.290890@.c13g2000cwb.googlegr oups.com...
and[vbcol=seagreen]
the[vbcol=seagreen]
links I[vbcol=seagreen]
Windows[vbcol=seagreen]
|||I've just recently had to move my database to other server. A mirror in fact.
The faster way to move and get them running was stopping sql services copy
the .ldf and .mdf files to the other server. Copy the folder \mssql\data to
the other server and start the services.
It worked.
But i think it wont work for you since you're gonna place db on different
paths.
Hope you dont have a bad time doing that

"tommydogs@.sickoflosers.com" wrote:

> Thanks for the help! One of the questions I have that doesn't seem to
> be answered is this: I detach all user DBs from my current SQL server,
> then copy over the master, model, msdb, & tempdb system DBs to the new
> server. Can I then copy and reattach user DB files?
>
> Keith Kratochvil wrote:
> that you
> Incomplete
> When
> and
> the
> links I
> Windows
>

Moving ALL DBs to new server

I am building a new SQL server and would like to copy ALL DBs (user and
system) to it. The server will eventually have the same name, but the
DB files will be in different locations (paths). Are there any links I
should check out or MSFT KB articles? I tried searching but wasn't
able to get anything for my specific issues. (Also, upgrading Windows
2000 to Windows 2003, but still on SQL Server 2000 SP3.)I found this one.
http://support.microsoft.com/default.aspx/kb/224071
"tommydogs@.sickoflosers.com" wrote:

> I am building a new SQL server and would like to copy ALL DBs (user and
> system) to it. The server will eventually have the same name, but the
> DB files will be in different locations (paths). Are there any links I
> should check out or MSFT KB articles? I tried searching but wasn't
> able to get anything for my specific issues. (Also, upgrading Windows
> 2000 to Windows 2003, but still on SQL Server 2000 SP3.)
>|||In addition to the link posted by Jen, here are some more articles that you
may find helpful:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
Keith
<tommydogs@.sickoflosers.com> wrote in message
news:1107283271.072157.290890@.c13g2000cwb.googlegroups.com...
> I am building a new SQL server and would like to copy ALL DBs (user and
> system) to it. The server will eventually have the same name, but the
> DB files will be in different locations (paths). Are there any links I
> should check out or MSFT KB articles? I tried searching but wasn't
> able to get anything for my specific issues. (Also, upgrading Windows
> 2000 to Windows 2003, but still on SQL Server 2000 SP3.)
>|||Thanks for the help! One of the questions I have that doesn't seem to
be answered is this: I detach all user DBs from my current SQL server,
then copy over the master, model, msdb, & tempdb system DBs to the new
server. Can I then copy and reattach user DB files?
Keith Kratochvil wrote:
> In addition to the link posted by Jen, here are some more articles
that you
> may find helpful:
>
>
> INF: Disaster Recovery Articles for Microsoft SQL Server
> http://www.support.microsoft.com/?id=307775
> INF: Moving SQL Server databases to a new location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.support.microsoft.com/?id=314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/?id=221465
> PRB: User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
Incomplete
> http://www.support.microsoft.com/?id=274188
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases
When[vbcol=seagreen]
> Database Is Moved
> http://www.support.microsoft.com/?id=298897
>
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
>
> --
> Keith
>
> <tommydogs@.sickoflosers.com> wrote in message
> news:1107283271.072157.290890@.c13g2000cwb.googlegroups.com...
and[vbcol=seagreen]
the[vbcol=seagreen]
links I[vbcol=seagreen]
Windows[vbcol=seagreen]|||I've just recently had to move my database to other server. A mirror in fact
.
The faster way to move and get them running was stopping sql services copy
the .ldf and .mdf files to the other server. Copy the folder \mssql\data to
the other server and start the services.
It worked.
But i think it wont work for you since you're gonna place db on different
paths.
Hope you dont have a bad time doing that

Wednesday, March 28, 2012

Moving a SQL Server 2000 database that is in Standby Mode

SQL Users/DBAs,

I'm trying to move data files around for a database that is in standby
mode. I can detach/attach the database fine using
SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
sequence is broken and I can't restore any more logs. Does anyone
know if there is a way to move around data files and keep the database
in standby mode?

Thanks,

JB"jbizzy" <jbizzaro@.yahoo.com> wrote in message
news:609b4de0.0404201342.29ceb03e@.posting.google.c om...
> SQL Users/DBAs,
> I'm trying to move data files around for a database that is in standby
> mode. I can detach/attach the database fine using
> SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
> sequence is broken and I can't restore any more logs. Does anyone
> know if there is a way to move around data files and keep the database
> in standby mode?
>
> Thanks,
>
> JB

It would be useful to know why you need to do this - if you want to maintain
two standby copies of a database, then why not simply restore the log
backups to both databases, instead of restoring to only one and then trying
to copy it?

Simon|||Our database is 350 GB...we're upgrading some of our arrays...rather
than re-sync the database up using restore, we would just like to move a
few of the datafiles..the restore takes many hours..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"jbizzy" <jbizzaro@.yahoo.com> wrote in message
news:609b4de0.0404201342.29ceb03e@.posting.google.c om...
> SQL Users/DBAs,
> I'm trying to move data files around for a database that is in standby
> mode. I can detach/attach the database fine using
> SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
> sequence is broken and I can't restore any more logs. Does anyone
> know if there is a way to move around data files and keep the database
> in standby mode?
>
> Thanks,
>
> JB

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Monday, March 26, 2012

Moving a Database

I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)

I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder of the source PC to the destination PC (Same folder).

I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?

If you are moving the db;s you need to'detach' them first, then copy the files to the new location, then'attach' them back. check out BOL on how to attach/detach DB's.|||

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='pramsetc',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

This seemed to run okay, but I cant now find my database anywhere in Object Explorer in Management Studio, however the database must of been created somwhere as when I try running the sql again I get:

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?FONT>

|||

Apologies correction:

Thanks

I have just tried doing this, I opened a sql command window on (local)SQLEXPRESS.master - SQLQuery1.sql and than this script:

EXEC

sp_attach_db @.dbname='blog',

@.filename1

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog.mdf',

@.filename2

='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\blog_log.ldf'

Msg 1801, Level 16, State 3, Line 1

Database 'blog' already exists.

Any pointers as to where I am going wrong?

|||Did you detach the DB before attaching it? If you did, refresh your databases list and you could probably see the new db in the list.|||

No I didnt know I needed to do that when I copied them across. I will do that now, just as a matter of interest why does that make a difference, what does the detach do that affects the file being attached locally?

Thanks

|||I am puzzled as how it let you copy the files without getting an "Access denied" or "File in use" error. (2) On the server where you are restoring, refresh the databases on the enterprise maanger. Do you see the 'Blob' db? If you already have one, you need to give a different name while restoring.|||I will try detaching it first, thanks

Moving a database

I have a small customer with a 2GB database. They recently added a RAID 5
array and RAID10 array to their server. I want to move the data files to the
RAID 5 and the log files to the RAID 10. Is there a script/command I can use
to move it or is it easier to just backup the database and then use RESTORE
WITH MOVE?
Note that I have an entire w

end to accomplish it. =-)
Thanks,
DeanYou can either use Restore WITH MOVE or attach it. It's almost 6 or one and
a half dozen of the other either way.
Andrew J. Kelly SQL MVP
"Dean" <Dean@.discussions.microsoft.com> wrote in message
news:F441AF9C-BBFE-487F-B23F-0226F326C0CA@.microsoft.com...
>I have a small customer with a 2GB database. They recently added a RAID 5
> array and RAID10 array to their server. I want to move the data files to
> the
> RAID 5 and the log files to the RAID 10. Is there a script/command I can
> use
> to move it or is it easier to just backup the database and then use
> RESTORE
> WITH MOVE?
> Note that I have an entire w

end to accomplish it. =-)
> Thanks,
> Dean
>sql

Friday, March 23, 2012

moving a 80GB sql data from one drive to another

Hello:
We have a 80GB SQL database that has 6 files, including the transaction log.
The data files size range from 6GB to 53GB. We are trying to move it from
one drive to another. Tried two methods, but either does not work.
1. Detach the database, and copy the data files to the destination drive.
There was a Windows message saying that there was not enough resource (our
computer server has 2GB of Ram).
2. Create a new database in the destination drive. Then run SQL import
utility to import the objects and data to the new drive. It failed, but the
error message was not clear enough to point out what the problem is. It
seems most of the data was copied however, as the destination database size
is about 80GB.
Any idea?
Thanks,
QThe detach and attach should have worked fine. I don't know where the
resource error came from. Are you ruining anything else on the server at
that time? Did you try to copy the files one at a time?
Andrew J. Kelly SQL MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Hi,
The 2 best approaches are:-
1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
the datbase. This method is not at all resouce intencive. So possibility of
memory utilization is not at all possible. CHeck if other process is
utilizing the memory.
2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
restore the database (RESTORE DATABASE. This is actually a online operation
.
Thanks
Hari
SQL Server MVP
"Q" wrote:

> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction lo
g.
> The data files size range from 6GB to 53GB. We are trying to move it fro
m
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but t
he
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database siz
e
> is about 80GB.
> Any idea?
> Thanks,
> Q|||I tend to use ESEFILE to copy large files as the speed is much better than
xcopy/robocopy etc and you're less likely to hit system resource issues. You
can find it on an Exchange CD if you have one
http://support.microsoft.com/kb/248406/EN-US/
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Andrew:
Thanks for your reply. We were not running anything particular at that
time. Our server is mainly for SQL server. Perhaps I shall try to shut it
down before copying/moving the data file. I may have selected to copy all
the data files at the same time. That may have something to do with the
resource warning.
I will give it another try.
Thanks again!
Q
"Andrew J. Kelly" wrote:

> The detach and attach should have worked fine. I don't know where the
> resource error came from. Are you ruining anything else on the server at
> that time? Did you try to copy the files one at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>|||Thanks Hari!
Q
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> The 2 best approaches are:-
> 1. detach the database , copy the MDF, NDF, LDF to destination drive, Atta
ch
> the datbase. This method is not at all resouce intencive. So possibility o
f
> memory utilization is not at all possible. CHeck if other process is
> utilizing the memory.
> 2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination
,
> restore the database (RESTORE DATABASE. This is actually a online operati
on.
> Thanks
> Hari
> SQL Server MVP
>
> "Q" wrote:
>|||Hello Jasper:
Thanks for your idea. Not sure if we have the exchance CD. I was thinking
about using a third party software from Western Digital. When I bought a
disk from them, it came with a software for copying files.
Thanks again for your ideas!
Q
"Jasper Smith" wrote:

> I tend to use ESEFILE to copy large files as the speed is much better than
> xcopy/robocopy etc and you're less likely to hit system resource issues. Y
ou
> can find it on an Exchange CD if you have one
> http://support.microsoft.com/kb/248406/EN-US/
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>

moving a 80GB sql data from one drive to another

Hello:
We have a 80GB SQL database that has 6 files, including the transaction log.
The data files size range from 6GB to 53GB. We are trying to move it from
one drive to another. Tried two methods, but either does not work.
1. Detach the database, and copy the data files to the destination drive.
There was a Windows message saying that there was not enough resource (our
computer server has 2GB of Ram).
2. Create a new database in the destination drive. Then run SQL import
utility to import the objects and data to the new drive. It failed, but the
error message was not clear enough to point out what the problem is. It
seems most of the data was copied however, as the destination database size
is about 80GB.
Any idea?
Thanks,
QThe detach and attach should have worked fine. I don't know where the
resource error came from. Are you ruining anything else on the server at
that time? Did you try to copy the files one at a time?
--
Andrew J. Kelly SQL MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Hi,
The 2 best approaches are:-
1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
the datbase. This method is not at all resouce intencive. So possibility of
memory utilization is not at all possible. CHeck if other process is
utilizing the memory.
2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
restore the database (RESTORE DATABASE. This is actually a online operation.
Thanks
Hari
SQL Server MVP
"Q" wrote:
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||I tend to use ESEFILE to copy large files as the speed is much better than
xcopy/robocopy etc and you're less likely to hit system resource issues. You
can find it on an Exchange CD if you have one
http://support.microsoft.com/kb/248406/EN-US/
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q|||Andrew:
Thanks for your reply. We were not running anything particular at that
time. Our server is mainly for SQL server. Perhaps I shall try to shut it
down before copying/moving the data file. I may have selected to copy all
the data files at the same time. That may have something to do with the
resource warning.
I will give it another try.
Thanks again!
Q
"Andrew J. Kelly" wrote:
> The detach and attach should have worked fine. I don't know where the
> resource error came from. Are you ruining anything else on the server at
> that time? Did you try to copy the files one at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> > Hello:
> >
> > We have a 80GB SQL database that has 6 files, including the transaction
> > log.
> > The data files size range from 6GB to 53GB. We are trying to move it from
> > one drive to another. Tried two methods, but either does not work.
> >
> > 1. Detach the database, and copy the data files to the destination drive.
> > There was a Windows message saying that there was not enough resource (our
> > computer server has 2GB of Ram).
> > 2. Create a new database in the destination drive. Then run SQL import
> > utility to import the objects and data to the new drive. It failed, but
> > the
> > error message was not clear enough to point out what the problem is. It
> > seems most of the data was copied however, as the destination database
> > size
> > is about 80GB.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Q
>
>|||Thanks Hari!
Q
"Hari Prasad" wrote:
> Hi,
> The 2 best approaches are:-
> 1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
> the datbase. This method is not at all resouce intencive. So possibility of
> memory utilization is not at all possible. CHeck if other process is
> utilizing the memory.
> 2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
> restore the database (RESTORE DATABASE. This is actually a online operation.
> Thanks
> Hari
> SQL Server MVP
>
> "Q" wrote:
> > Hello:
> >
> > We have a 80GB SQL database that has 6 files, including the transaction log.
> > The data files size range from 6GB to 53GB. We are trying to move it from
> > one drive to another. Tried two methods, but either does not work.
> >
> > 1. Detach the database, and copy the data files to the destination drive.
> > There was a Windows message saying that there was not enough resource (our
> > computer server has 2GB of Ram).
> > 2. Create a new database in the destination drive. Then run SQL import
> > utility to import the objects and data to the new drive. It failed, but the
> > error message was not clear enough to point out what the problem is. It
> > seems most of the data was copied however, as the destination database size
> > is about 80GB.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Q|||Hello Jasper:
Thanks for your idea. Not sure if we have the exchance CD. I was thinking
about using a third party software from Western Digital. When I bought a
disk from them, it came with a software for copying files.
Thanks again for your ideas!
Q
"Jasper Smith" wrote:
> I tend to use ESEFILE to copy large files as the speed is much better than
> xcopy/robocopy etc and you're less likely to hit system resource issues. You
> can find it on an Exchange CD if you have one
> http://support.microsoft.com/kb/248406/EN-US/
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> > Hello:
> >
> > We have a 80GB SQL database that has 6 files, including the transaction
> > log.
> > The data files size range from 6GB to 53GB. We are trying to move it from
> > one drive to another. Tried two methods, but either does not work.
> >
> > 1. Detach the database, and copy the data files to the destination drive.
> > There was a Windows message saying that there was not enough resource (our
> > computer server has 2GB of Ram).
> > 2. Create a new database in the destination drive. Then run SQL import
> > utility to import the objects and data to the new drive. It failed, but
> > the
> > error message was not clear enough to point out what the problem is. It
> > seems most of the data was copied however, as the destination database
> > size
> > is about 80GB.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Q
>
>sql

moving a 80GB sql data from one drive to another

Hello:
We have a 80GB SQL database that has 6 files, including the transaction log.
The data files size range from 6GB to 53GB. We are trying to move it from
one drive to another. Tried two methods, but either does not work.
1. Detach the database, and copy the data files to the destination drive.
There was a Windows message saying that there was not enough resource (our
computer server has 2GB of Ram).
2. Create a new database in the destination drive. Then run SQL import
utility to import the objects and data to the new drive. It failed, but the
error message was not clear enough to point out what the problem is. It
seems most of the data was copied however, as the destination database size
is about 80GB.
Any idea?
Thanks,
Q
The detach and attach should have worked fine. I don't know where the
resource error came from. Are you ruining anything else on the server at
that time? Did you try to copy the files one at a time?
Andrew J. Kelly SQL MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||Hi,
The 2 best approaches are:-
1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
the datbase. This method is not at all resouce intencive. So possibility of
memory utilization is not at all possible. CHeck if other process is
utilizing the memory.
2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
restore the database (RESTORE DATABASE. This is actually a online operation.
Thanks
Hari
SQL Server MVP
"Q" wrote:

> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||I tend to use ESEFILE to copy large files as the speed is much better than
xcopy/robocopy etc and you're less likely to hit system resource issues. You
can find it on an Exchange CD if you have one
http://support.microsoft.com/kb/248406/EN-US/
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Q" <Q@.discussions.microsoft.com> wrote in message
news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
> Hello:
> We have a 80GB SQL database that has 6 files, including the transaction
> log.
> The data files size range from 6GB to 53GB. We are trying to move it from
> one drive to another. Tried two methods, but either does not work.
> 1. Detach the database, and copy the data files to the destination drive.
> There was a Windows message saying that there was not enough resource (our
> computer server has 2GB of Ram).
> 2. Create a new database in the destination drive. Then run SQL import
> utility to import the objects and data to the new drive. It failed, but
> the
> error message was not clear enough to point out what the problem is. It
> seems most of the data was copied however, as the destination database
> size
> is about 80GB.
> Any idea?
> Thanks,
> Q
|||Andrew:
Thanks for your reply. We were not running anything particular at that
time. Our server is mainly for SQL server. Perhaps I shall try to shut it
down before copying/moving the data file. I may have selected to copy all
the data files at the same time. That may have something to do with the
resource warning.
I will give it another try.
Thanks again!
Q
"Andrew J. Kelly" wrote:

> The detach and attach should have worked fine. I don't know where the
> resource error came from. Are you ruining anything else on the server at
> that time? Did you try to copy the files one at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>
|||Thanks Hari!
Q
"Hari Prasad" wrote:
[vbcol=seagreen]
> Hi,
> The 2 best approaches are:-
> 1. detach the database , copy the MDF, NDF, LDF to destination drive, Attach
> the datbase. This method is not at all resouce intencive. So possibility of
> memory utilization is not at all possible. CHeck if other process is
> utilizing the memory.
> 2. Backup the database (BACKUP DATABASE), Copy the BAK file to destination,
> restore the database (RESTORE DATABASE. This is actually a online operation.
> Thanks
> Hari
> SQL Server MVP
>
> "Q" wrote:
|||Hello Jasper:
Thanks for your idea. Not sure if we have the exchance CD. I was thinking
about using a third party software from Western Digital. When I bought a
disk from them, it came with a software for copying files.
Thanks again for your ideas!
Q
"Jasper Smith" wrote:

> I tend to use ESEFILE to copy large files as the speed is much better than
> xcopy/robocopy etc and you're less likely to hit system resource issues. You
> can find it on an Exchange CD if you have one
> http://support.microsoft.com/kb/248406/EN-US/
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:E9D6F236-F79A-45D9-A36A-1C8F8F2557AB@.microsoft.com...
>
>

Moving .ldf to new drive, doesn't work gives "Cannot associate files with different datab

I am trying to move a log file to a non-RAID drive. I have detached
the database and made a copy of the .ldf file. I have taken this copy
of the .ldf file and placed it where I want it to be. I would like to
keep the .mdf file where it is located on the RAID drive, but for some
reason I can only reattach if the .ldf file is in the same location as
the .mdf file.
I have then run:
USE MASTER
GO
EXEC sp_attach_db @.dbname = N'Corporate',
@.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
@.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
And it returns:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
Is this possible?The procedure you described should work fine. But I suspect your .ldf file is
not really from that database.
Linchi
"caperneus@.gmail.com" wrote:
> I am trying to move a log file to a non-RAID drive. I have detached
> the database and made a copy of the .ldf file. I have taken this copy
> of the .ldf file and placed it where I want it to be. I would like to
> keep the .mdf file where it is located on the RAID drive, but for some
> reason I can only reattach if the .ldf file is in the same location as
> the .mdf file.
> I have then run:
> USE MASTER
> GO
> EXEC sp_attach_db @.dbname = N'Corporate',
> @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> And it returns:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
> Is this possible?
>|||On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> The procedure you described should work fine. But I suspect your .ldf file is
> not really from that database.
> Linchi
> "capern...@.gmail.com" wrote:
> > I am trying to move a log file to a non-RAID drive. I have detached
> > the database and made a copy of the .ldf file. I have taken this copy
> > of the .ldf file and placed it where I want it to be. I would like to
> > keep the .mdf file where it is located on the RAID drive, but for some
> > reason I can only reattach if the .ldf file is in the same location as
> > the .mdf file.
> > I have then run:
> > USE MASTER
> > GO
> > EXEC sp_attach_db @.dbname = N'Corporate',
> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> > And it returns:
> > Server: Msg 5173, Level 16, State 2, Line 1
> > Cannot associate files with different databases.
> > Is this possible?
It is really from that database. I can attach it back with no problems
if the files are in the same directory.|||Instead of detached and attached try backup and restore ...
BACKUP DATABASE Corporate
TO DISK = 'E:\backup_corporate.bak'
WITH INIT
Drop the database, and delete the mdf and ldf file (or just back it up)
Then the Restore:
RESTORE DATABASE Corporate
FROM DISK = 'E:\backup_corporate.bak'
WITH RECOVERY,
MOVE '[datafilelogicalname]' TO 'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
MOVE '[loglogicalname]' TO 'E:\Web\Corporate\Corporate_Log.ldf'
Hope that works.
Lucas
<caperneus@.gmail.com> wrote in message
news:1181852163.020692.243720@.a26g2000pre.googlegroups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>> The procedure you described should work fine. But I suspect your .ldf
>> file is
>> not really from that database.
>> Linchi
>> "capern...@.gmail.com" wrote:
>> > I am trying to move a log file to a non-RAID drive. I have detached
>> > the database and made a copy of the .ldf file. I have taken this copy
>> > of the .ldf file and placed it where I want it to be. I would like to
>> > keep the .mdf file where it is located on the RAID drive, but for some
>> > reason I can only reattach if the .ldf file is in the same location as
>> > the .mdf file.
>> > I have then run:
>> > USE MASTER
>> > GO
>> > EXEC sp_attach_db @.dbname = N'Corporate',
>> > @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
>> > @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
>> > And it returns:
>> > Server: Msg 5173, Level 16, State 2, Line 1
>> > Cannot associate files with different databases.
>> > Is this possible?
> It is really from that database. I can attach it back with no problems
> if the files are in the same directory.
>

Wednesday, March 21, 2012

Move tempdb files to a new disk

I have done a new installation which is up and running.
However the disk on which the tempdb mdf and ldf files are
is giving a problem. Is there a way to move the files to a
different disk? I know we can't detach and attach it.
Alter database does not allow the primary data file to be
deleted.
I can create a new data file on the other disk and stop
the existing datafile from growing. This will effectively
move all tempdb activity to the new disk. But is there no
better way to move the mdf and ldf files to the new disk?
TIA,
JackINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...1&Product=sql2k
Scroll down to the bottom and find the section titled 'Moving Tempdb'
You are correct in that Detach/Attach doesn't work on TempDB. This KB
article does explain the proper way to move TempDB.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||You do this using ALTER DATABASE. See KB 224071 for more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack|||Hi,
How to Move TEMPDB from one location to another
---
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and
log files, and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the
mssqlserver service before it takes affect.
This steps are common for SQL Server 7.0 and 2000.
Thanks
Hari
MCDBA
"Jack" <jacka8@.excite.com> wrote in message
news:e4a401c40ba6$b1358d50$a301280a@.phx.gbl...
> I have done a new installation which is up and running.
> However the disk on which the tempdb mdf and ldf files are
> is giving a problem. Is there a way to move the files to a
> different disk? I know we can't detach and attach it.
> Alter database does not allow the primary data file to be
> deleted.
> I can create a new data file on the other disk and stop
> the existing datafile from growing. This will effectively
> move all tempdb activity to the new disk. But is there no
> better way to move the mdf and ldf files to the new disk?
> TIA,
> Jack

Move TempDB data/trans From C: to E:

I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
What is the best way to move the TempDB data and transaction log files from
the C: to E: drive?
Thank You,I'm very interested in this as well. We were planning on doing this first
thing Saturday morning.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
> I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
> What is the best way to move the TempDB data and transaction log files
> from
> the C: to E: drive?
> Thank You,|||Here ya go....BOL...just tried it and it worked great.
1. stop sql server
2. copy to your desired location
3. start sql server
4. run the alter statements below
5. restart sql server
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
> I'm very interested in this as well. We were planning on doing this first
> thing Saturday morning.
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
>> What is the best way to move the TempDB data and transaction log files
>> from
>> the C: to E: drive?
>> Thank You,
>|||Hi Tim
What exactly are you doing in step 2? You shouldn't have to move anything,
since the tempdb files are recreated upon system startup. To move tempdb you
just need to run ALTER DATABASE and then restart your SQL Server.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
> Here ya go....BOL...just tried it and it worked great.
> 1. stop sql server
> 2. copy to your desired location
> 3. start sql server
> 4. run the alter statements below
> 5. restart sql server
> USE master;
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
> GO
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>> I'm very interested in this as well. We were planning on doing this
>> first thing Saturday morning.
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced
>> Server.
>> What is the best way to move the TempDB data and transaction log files
>> from
>> the C: to E: drive?
>> Thank You,
>>
>|||I was doing the steps as outlined in BOL...and I quote from BOL
G. Moving tempdb to a new location
The following example moves tempdb from its current location on disk to
another disk location.
1.. Determine the logical file names of the tempdb database and their
current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
2.. Physically move these files to the new location E:\SQLData.
In this example the new location specified for the tempdb files is
E:\SQLData. If you move the files to different location, modify the example
to point to that location.
3.. Change the location of each file by using ALTER DATABASE.
4.. Stop and restart the instance of SQL Server.
5.. Verify the file change.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi Tim
> What exactly are you doing in step 2? You shouldn't have to move anything,
> since the tempdb files are recreated upon system startup. To move tempdb
> you just need to run ALTER DATABASE and then restart your SQL Server.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
>> Here ya go....BOL...just tried it and it worked great.
>> 1. stop sql server
>> 2. copy to your desired location
>> 3. start sql server
>> 4. run the alter statements below
>> 5. restart sql server
>> USE master;
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
>> GO
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>> I'm very interested in this as well. We were planning on doing this
>> first thing Saturday morning.
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced
>> Server.
>> What is the best way to move the TempDB data and transaction log files
>> from
>> the C: to E: drive?
>> Thank You,
>>
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0368_01C66B08.B9F88460
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Tim
Just FYI, you do not have to move any files to move tempdb, because =tempdb is rebuilt every time SQL Server starts.
Do you have the latest BOL? There was a refresh just a week ago.
G. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk =to another disk location. Because tempdb is re-created each time the =MSSQLSERVER service is started, you do not have to physically move the =data and log files. The files are created when the service is restarted =in step 3. Until the service is restarted, tempdb continues to function =in its existing location.
1.. Determine the logical file names of the tempdb database and their =current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id =3D DB_ID('tempdb');
GO
2.. Change the location of each file by using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D tempdev, FILENAME =3D 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D templog, FILENAME =3D 'E:\SQLData\templog.ldf');
GO
3.. Stop and restart the instance of SQL Server.
4.. Verify the file change.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id =3D DB_ID('tempdb');
5.. Delete the tempdb.mdf and templog.ldf files from their original =location.
-- HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message =news:%23TTfa5zaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>I was doing the steps as outlined in BOL...and I quote from BOL
> > G. Moving tempdb to a new location
> The following example moves tempdb from its current location on disk =to > another disk location.
> > 1.. Determine the logical file names of the tempdb database and their =
> current location on disk.
> > > > SELECT name, physical_name
> FROM sys.master_files
> WHERE database_id =3D DB_ID('tempdb');
> GO
> > 2.. Physically move these files to the new location E:\SQLData.
> > > > In this example the new location specified for the tempdb files =is > E:\SQLData. If you move the files to different location, modify the =example > to point to that location.
> > > > 3.. Change the location of each file by using ALTER DATABASE.
> > 4.. Stop and restart the instance of SQL Server.
> > > 5.. Verify the file change.
> > > > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message > news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>> Hi Tim
>> What exactly are you doing in step 2? You shouldn't have to move =anything, >> since the tempdb files are recreated upon system startup. To move =tempdb >> you just need to run ALTER DATABASE and then restart your SQL Server.
>> -- >> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message >> news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
>> Here ya go....BOL...just tried it and it worked great.
>> 1. stop sql server
>> 2. copy to your desired location
>> 3. start sql server
>> 4. run the alter statements below
>> 5. restart sql server
>> USE master;
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME =3D tempdev, FILENAME =3D ='E:\SQLData\tempdb.mdf');
>> GO
>> ALTER DATABASE tempdb
>> MODIFY FILE (NAME =3D templog, FILENAME =3D ='E:\SQLData\templog.ldf');
>> GO
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message =
>> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>> I'm very interested in this as well. We were planning on doing =this >> first thing Saturday morning.
>>
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message >> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>> I have a SQL Server 2000 database server on Windows 2000 Advanced >> Server.
>> What is the best way to move the TempDB data and transaction log =files >> from
>> the C: to E: drive?
>> Thank You,
>>
>>
>> > >
--=_NextPart_000_0368_01C66B08.B9F88460
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Hi Tim
Just FYI, you do not have to move any =files to move tempdb, because tempdb is rebuilt every time SQL Server =starts.
Do you have the latest BOL? There was a =refresh just a week ago.
G. Moving =tempdb to a new location
http://ddue.schemas.microsoft.com/authoring/2003/5">
The following example moves =tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER =service is started, you do not have to physically move the data and log =files. The files are created when the service is restarted in step 3. Until the =service is restarted, tempdb continues to function in its existing =location.
Determine the logical file names of =the tempdb database and their current location on disk.
SELECT name, =physical_name
FROM sys.master_files
WHERE database_id =3D DB_ID('tempdb');
GO
Change the location of each file by =using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D tempdev, FILENAME =3D 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =3D templog, FILENAME =3D 'E:\SQLData\templog.ldf');
GO
Stop and restart the instance of SQL =Server.
Verify the file change.
SELECT name, =physical_name
FROM sys.master_files
WHERE database_id =3D =DB_ID('tempdb');
Delete the tempdb.mdf and templog.ldf =files from their original location.
-- HTHKalen Delaney, SQL =Server MVPwww.solidqualitylearning.com
"Tim Greenwood" wrote in message news:%23TTfa5zaGHA.1196@.TK2MSFTNGP03.phx.gbl...>I was doing the =steps as outlined in BOL...and I quote from BOL> > G. Moving =tempdb to a new location> The following example moves tempdb from its current =location on disk to > another disk location.> => 1.. Determine the logical file names of the tempdb database and their => current location on disk.> > > > SELECT =name, physical_name> FROM sys.master_files> WHERE database_id ==3D DB_ID('tempdb');> GO> > 2.. Physically move =these files to the new location E:\SQLData.> > > > In this example the =new location specified for the tempdb files is > E:\SQLData. If you =move the files to different location, modify the example > to point to =that location.> > > > 3.. Change the =location of each file by using ALTER DATABASE.> > 4.. Stop and =restart the instance of SQL Server.> > > 5.. Verify =the file change.> > > > > "Kalen Delaney" wrote in message > news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...> Hi Tim>> What exactly are =you doing in step 2? You shouldn't have to move anything, > since the =tempdb files are recreated upon system startup. To move tempdb > you =just need to run ALTER DATABASE and then restart your SQL Server.>> -- > HTH> Kalen =Delaney, SQL Server MVP>www.solidqualitylearning.com>>> "Tim Greenwood" = wrote in message > news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...> Here ya go....BOL...just tried it and it =worked great.> 1. stop sql server> 2. copy to =your desired location> 3. start sql server> 4. =run the alter statements below> 5. restart sql server>> USE master;> GO> ALTER DATABASE tempdb> MODIFY FILE =(NAME =3D tempdev, FILENAME =3D 'E:\SQLData\tempdb.mdf');> =GO> ALTER DATABASE tempdb> MODIFY FILE (NAME =3D =templog, FILENAME =3D 'E:\SQLData\templog.ldf');> GO>> "Tim Greenwood" wrote in message > news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...> I'm very interested in this as =well. We were planning on doing this > first thing Saturday morning.>>>&=gt;>> "Joe K." wrote in message > news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...>> I have a SQL Server 2000 database server on Windows 2000 Advanced > =Server.>> What is the best way to move the TempDB data and transaction log files > from> the C: to E: drive?>> Thank You,>>>>&=gt;>> > >

--=_NextPart_000_0368_01C66B08.B9F88460--sql

Move TempDB data/trans From C: to E:

Monday, March 19, 2012

Move System and User database

Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
K
That is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K
|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:

> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>
|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James

>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database[vbcol=seagreen]
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scri...ptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.
>.
>

Move System and User database

Dear All,
Is it possible to move system(master, model, msdb and so on...) and user
database to another sql server by just copying all ldf and mdf files in data
directory while the sql on the source server is down?
Any problem using this method instead of backup and restore?
as I don't want to detach the database from source server.
Thanks for any advices
KThat is never a good idea. Check these out:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
> Dear All,
> Is it possible to move system(master, model, msdb and so on...) and user
> database to another sql server by just copying all ldf and mdf files in
> data
> directory while the sql on the source server is down?
> Any problem using this method instead of backup and restore?
> as I don't want to detach the database from source server.
> Thanks for any advices
> K|||Andrew, Thanks for your reply!
I've already read through these documents.
However, I wonder what will I miss if I move all data including SYSTEM
database to new server.
any problem with that?
besides, will the sa password be moved to the new server if system database
moved? if not where the sa password stored?
Thanks for any advice
"Andrew J. Kelly" wrote:

> That is never a good idea. Check these out:
> http://www.support.microsoft.com/?id=314546 Moving DB's between Server
s
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Database
s
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permissi
on
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
> for SQL Server
>
> --
> Andrew J. Kelly SQL MVP
>
> "Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
> news:D1EB32EB-A2F6-465D-978E-58F96DB228F9@.microsoft.com...
>
>|||There is no problem with moving everything over just don't do a file level
copy of the db's unless you use sp_detach and sp_attach. The sa password is
stored in the master db.
Andrew J. Kelly SQL MVP
"Kenneth" <Kenneth@.discussions.microsoft.com> wrote in message
news:C630D625-C889-49D4-B683-C4997CA05A61@.microsoft.com...[vbcol=seagreen]
> Andrew, Thanks for your reply!
> I've already read through these documents.
> However, I wonder what will I miss if I move all data including SYSTEM
> database to new server.
> any problem with that?
> besides, will the sa password be moved to the new server if system
> database
> moved? if not where the sa password stored?
> Thanks for any advice
> "Andrew J. Kelly" wrote:
>|||Hi Ken
well there are times when one just has to move their
entire SQL server install to a new box - such as a server
upgrade
the article below may shed some light on the principles
and procedures involved to accomplish what you are trying
to do
http://vyaskn.tripod.com/moving_sql_server.htm
cheers
James

>--Original Message--
>Andrew, Thanks for your reply!
>I've already read through these documents.
>However, I wonder what will I miss if I move all data
including SYSTEM
>database to new server.
>any problem with that?
>besides, will the sa password be moved to the new server
if system database
>moved? if not where the sa password stored?
>Thanks for any advice
>"Andrew J. Kelly" wrote:
>
DB's between Servers[vbcol=seagreen]
SQL Server Databases[vbcol=seagreen]
Using WITH MOVE in a[vbcol=seagreen]
Transfer Logins and[vbcol=seagreen]
Logins & SIDs after a[vbcol=seagreen]
Utility to map logins to[vbcol=seagreen]
Logon and/or Permission[vbcol=seagreen]
Resolve Permission[vbcol=seagreen]
http://www.sqlservercentral.com/scr...iptdetails.asp?
scriptid=599[vbcol=seagreen]
Recovery Articles[vbcol=seagreen]
message[vbcol=seagreen]
58F96DB228F9@.microsoft.com...[vbcol=seagreen]
so on...) and user[vbcol=seagreen]
ldf and mdf files in[vbcol=seagreen]
restore?[vbcol=seagreen]
server.[vbcol=seagreen]
>.
>

Monday, March 12, 2012

move SQL data files to same letter drive but different disk

Hi,
We need to move our sql data files to new disks. Current
data are on drive D, F. New disks are U,V. We want to
move data files on D to U and F to V. Then rename U to D
and V to F (after rename original D & F to X & Y).
The quesions are
if SQL server is shutdown before this, will SQL Server
see the same data file?
Will I need to do a dettach/attach even the data file
will be on same path at the end?
Thanks.
Haii believe if you do this in correct order you don't have any problem:
1. stop SQL service
2. move the files (.mdf and .ldf)
3. rename D:, F: to X:, Y:
4. rename U:, V: to D:, F:
5. start SQL service
don't know if a reboot after step 4 would help but why don't you try it?
"Hai Le" <hle@.coair.com> wrote in message
news:02d401c3dac8$70e9a2e0$a101280a@.phx.gbl...
> Hi,
> We need to move our sql data files to new disks. Current
> data are on drive D, F. New disks are U,V. We want to
> move data files on D to U and F to V. Then rename U to D
> and V to F (after rename original D & F to X & Y).
> The quesions are
> if SQL server is shutdown before this, will SQL Server
> see the same data file?
> Will I need to do a dettach/attach even the data file
> will be on same path at the end?
> Thanks.
> Hai

move SQL data files to same letter drive but different disk

quote:

> Hi,
> We need to move our sql data files to new disks. Current
> data are on drive D, F. New disks are U,V. We want to
> move data files on D to U and F to V. Then rename U to D
> and V to F (after rename original D & F to X & Y).
> The quesions are
> if SQL server is shutdown before this, will SQL Server
> see the same data file?
> Will I need to do a dettach/attach even the data file
> will be on same path at the end?
> Thanks.
> Hai

Move sql 2005 ee db to new server without disk access?

Is there any reasonably simple way to install the mdf and ldf files for a sq
l
server 2005 ee database on a server where one does not have access to the da
ta
folder of the sql server? IE using the ee management studio from a workstati
on?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:[vbcol=seagreen]
> Is there any reasonably simple way to install the mdf and ldf files for a
sql
> server 2005 ee database on a server where one does not have access to the
data
> folder of the sql server? IE using the ee management studio from a workstation?[/v
bcol]
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?[vbcol=seagreen]
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.

Move sql 2005 ee db to new server without disk access?

Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?
On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:[vbcol=seagreen]
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.

Move sql 2005 ee db to new server without disk access?

Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
>> Is there any reasonably simple way to install the mdf and ldf files for a sql
>> server 2005 ee database on a server where one does not have access to the
data
>> folder of the sql server? IE using the ee management studio from a
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.

Friday, March 30, 2012

Moving C2 Audits

Moving ALL DBs to new server

Moving ALL DBs to new server

Wednesday, March 28, 2012

Moving a SQL Server 2000 database that is in Standby Mode

Monday, March 26, 2012

Moving a Database

Moving a database

Friday, March 23, 2012

moving a 80GB sql data from one drive to another

moving a 80GB sql data from one drive to another

moving a 80GB sql data from one drive to another

Moving .ldf to new drive, doesn't work gives "Cannot associate files with different datab

Wednesday, March 21, 2012

Move tempdb files to a new disk

Move TempDB data/trans From C: to E:

Move TempDB data/trans From C: to E:

Monday, March 19, 2012

Move System and User database

Move System and User database

Monday, March 12, 2012

move SQL data files to same letter drive but different disk

move SQL data files to same letter drive but different disk

Move sql 2005 ee db to new server without disk access?

Move sql 2005 ee db to new server without disk access?

Move sql 2005 ee db to new server without disk access?

Move database file

Blog Archive

About Me