Move database file: file

Showing posts with label file. Show all posts

Friday, March 30, 2012

moving clustered indexes

Hi,
I have quite a few clustered indexes that I need to move theminto a new file
created in a new filegroup. How can I move them without losing any data?
regards,Hi,
Data are contained in the leaf pages of the clustered index, moving the
clustered index moves the data
to the new file group. So you cannot move the clustered index alone.
How to move the index to a new file group
---
create clustered index idx_clus on Table (Column) with drop existing
on new_file_group
Thanks
Hari
MCDBA
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:#$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>|||Hari is correct in the how-to... Be aware that this process may take a long
time... It will write to the transaction log, so be backing up the log
during the process to keep the log from growing huge... And users will be
locked out of the tables during the process.
Backup everything both before and after, just to be safe..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"aoxpsql" <anonymous@.discussion.com> wrote in message
news:%23$PdXIGdEHA.556@.tk2msftngp13.phx.gbl...
> Hi,
> I have quite a few clustered indexes that I need to move theminto a new
file
> created in a new filegroup. How can I move them without losing any data?
> regards,
>

moving and renaming a file

I have a DTS package that I currently execute manually. This DTS package
reads a text file and imports it into a table.
I would like to have it run automaticly.
For that to work correctly, it would need to check to see if the txt file is
there (the name is always teh same), if it is not there, then to quit. If
it is there, to read and import it as normal, then once completed, for it to
rename teh file and move it to another location on teh computer.
How do I do that?I'd use ActiveXScipt and the FileSystemObject. Here is some sample code:
Function Main()
dim fso, source, dest, oldFile
set fso = CreateObject("Scripting.FileSystemObject")
source = "\\myBox\FAAIVR\BENEFIT_GROUP_INFO.txt"
oldFile = replace(source, ".txt", "_" & replace(date - 7, "/","_") & ".txt")
if (fso.FileExists(source)) then
dest = replace(source, ".txt", "_" & replace(date, "/","_") & ".txt")
fso.MoveFile source, dest
end if
if (fso.FileExists(oldFile)) then
fso.DeleteFile (oldFile)
"Johnfli" wrote:
> I have a DTS package that I currently execute manually. This DTS package
> reads a text file and imports it into a table.
> I would like to have it run automaticly.
> For that to work correctly, it would need to check to see if the txt file is
> there (the name is always teh same), if it is not there, then to quit. If
> it is there, to read and import it as normal, then once completed, for it to
> rename teh file and move it to another location on teh computer.
> How do I do that?
>
>

Wednesday, March 28, 2012

moving a SQl 2005 log file

I do you move the log file to a different drive and leave the database
in its current location with SQL 2005?
Thanks for your help,
MarkMark,
See "alter database ... modify file ..." in BOL.
AMB
"Mark" wrote:
> I do you move the log file to a different drive and leave the database
> in its current location with SQL 2005?
> Thanks for your help,
> Mark
>|||In addition to the ALTER DATABASE topic, the topic Moving User Databases
http://msdn2.microsoft.com/en-us/library/ms345483.aspx provides step-by-step
instructions for moving files.
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:78B38875-979A-4973-A97F-7C7669A7F1A2@.microsoft.com...
> Mark,
> See "alter database ... modify file ..." in BOL.
>
> AMB
> "Mark" wrote:
>> I do you move the log file to a different drive and leave the database
>> in its current location with SQL 2005?
>> Thanks for your help,
>> Mark|||Gail Erickson [MS]" ,
I have to get use to look more closely into the BOL. The SQL Server
Documentation Team is doing a great job.
Thanks,
AMB
"Gail Erickson [MS]" wrote:
> In addition to the ALTER DATABASE topic, the topic Moving User Databases
> http://msdn2.microsoft.com/en-us/library/ms345483.aspx provides step-by-step
> instructions for moving files.
> Regards,
> Gail
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:78B38875-979A-4973-A97F-7C7669A7F1A2@.microsoft.com...
> > Mark,
> >
> > See "alter database ... modify file ..." in BOL.
> >
> >
> > AMB
> >
> > "Mark" wrote:
> >
> >> I do you move the log file to a different drive and leave the database
> >> in its current location with SQL 2005?
> >>
> >> Thanks for your help,
> >> Mark
> >>
>
>|||> I have to get use to look more closely into the BOL. The SQL Server
> Documentation Team is doing a great job.
Thanks!
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:32E9A595-9FA3-42E1-892C-951FF36FF095@.microsoft.com...
> Gail Erickson [MS]" ,
> I have to get use to look more closely into the BOL. The SQL Server
> Documentation Team is doing a great job.
> Thanks,
> AMB
>
> "Gail Erickson [MS]" wrote:
>> In addition to the ALTER DATABASE topic, the topic Moving User Databases
>> http://msdn2.microsoft.com/en-us/library/ms345483.aspx provides
>> step-by-step
>> instructions for moving files.
>> Regards,
>> Gail
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:78B38875-979A-4973-A97F-7C7669A7F1A2@.microsoft.com...
>> > Mark,
>> >
>> > See "alter database ... modify file ..." in BOL.
>> >
>> >
>> > AMB
>> >
>> > "Mark" wrote:
>> >
>> >> I do you move the log file to a different drive and leave the database
>> >> in its current location with SQL 2005?
>> >>
>> >> Thanks for your help,
>> >> Mark
>> >>
>>|||Hello,
You could use ALTER DATABASE...MODIFYFILE. THis will change the LDF path in
the system table. Once you restart the SQL Server
automatically this will come into effect.
You can also use SP_DETACH_DB and SP_ATTACH_DB
Thanks
Hari
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:78B38875-979A-4973-A97F-7C7669A7F1A2@.microsoft.com...
> Mark,
> See "alter database ... modify file ..." in BOL.
>
> AMB
> "Mark" wrote:
>> I do you move the log file to a different drive and leave the database
>> in its current location with SQL 2005?
>> Thanks for your help,
>> Mark

Moving a log file while still "attached"

Is there a way to move a log file to another drive while it is still
attached ?
Reason I am asking... I have run into cases where after detaching a
database, I was unable to re-attach it.No, you cant move the primary log file while your DB is up and running. You
can however create a second log file while the DB is still attached (and you
may be running out of space in the primary log file)
Would I create a second log - no unless absolutley necessary.
Next time you try moving the log file via detach and attach - make sure you
run dbcc checkdb before detaching - when no problems are reported - copy the
original log file to its new location - dont move it. This way in case attac
h
shouldnt work (e.g the log file got damaged on its way to the new volume) yo
u
can reattach with minimal further loss of time - to your original log file.
Hope this helped.
"Rob" wrote:

> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>|||No, you cant move the primary log file while your DB is up and running. You
can however create a second log file while the DB is still attached (and you
may be running out of space in the primary log file)
Would I create a second log - no unless absolutley necessary.
Next time you try moving the log file via detach and attach - make sure you
run dbcc checkdb before detaching - when no problems are reported - copy the
original log file to its new location - dont move it. This way in case attac
h
shouldnt work (e.g the log file got damaged on its way to the new volume) yo
u
can reattach with minimal further loss of time - to your original log file.
Hope this helped.
"Rob" wrote:

> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>|||Rob wrote:
> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>
Instead of using attach, you can use RESTORE WITH MOVE. This is a much
safer way to do it since your source db will still be up and running and
operational. This means that if you backup for some reason doesnt work,
you can just make a new backup.
Regards
Steen Schlter Persson
DBA|||> Reason I am asking... I have run into cases where after detaching a database, I was unable
to
> re-attach it.
That should not happen. If you detach a database, then you should be able to
attach it, assuming you
*really* detached it first and you have all the files. If you fulfill those
criteria and still
cannot attach it, you have a bug in the product.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <rwchome@.comcast.net> wrote in message news:77-dncoBVcN_VhPZnZ2dnUVZ_qqdnZ2d@.comcast.c
om...
> Is there a way to move a log file to another drive while it is still attac
hed ?
> Reason I am asking... I have run into cases where after detaching a databa
se, I was unable to
> re-attach it.
>
>|||Rob wrote:
> Is there a way to move a log file to another drive while it is still
> attached ?
> Reason I am asking... I have run into cases where after detaching a
> database, I was unable to re-attach it.
>
>
Instead of using attach, you can use RESTORE WITH MOVE. This is a much
safer way to do it since your source db will still be up and running and
operational. This means that if you backup for some reason doesnt work,
you can just make a new backup.
Regards
Steen Schlter Persson
DBA|||> Reason I am asking... I have run into cases where after detaching a database, I was unable
to
> re-attach it.
That should not happen. If you detach a database, then you should be able to
attach it, assuming you
*really* detached it first and you have all the files. If you fulfill those
criteria and still
cannot attach it, you have a bug in the product.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <rwchome@.comcast.net> wrote in message news:77-dncoBVcN_VhPZnZ2dnUVZ_qqdnZ2d@.comcast.c
om...
> Is there a way to move a log file to another drive while it is still attac
hed ?
> Reason I am asking... I have run into cases where after detaching a databa
se, I was unable to
> re-attach it.
>
>

Moving a log file while still "attached"

Moving a log file

Hi,

I am trying to move a log file in SQL2005.

I have tried unattaching a DB, then reattaching it and giving it a path to a new log file but it refuses to mount the database and simply reconnects to the old log file.

if i delete the old log file then it recreats it in the old path.

Any one know what i am doing worng.

There are several ways to handle this.

When you attach the database, the server looks in the db file to determine where the transaction log file is located. It will then display the log file and location -you can change the log file location at this step BEFORE you confirm the ATTACH.

You can also use T-SQL, DETACH and ATTACH, taking care to specify where to find the log file. See Books Online for syntax specifics.

You could do a BACKUP and RESTORE, using the WITH MOVE options for RESTORE. Again, check Books Online for syntax specifics.

|||

Check Create database with ATTACH_REBUILD_LOG option...

Check BOL for more details.

http://msdn2.microsoft.com/en-us/library/ms176061.aspx

|||

Follow as Arnie suggested tomove the file before using SP_ATTACH_DB, you could use SP_ATTACH_SINGLE_FILE_DB in this case that will recreate fresh log file.

The ATTACH_REBUILD_LOG clause enables attaching a database without requiring all of the log files. For example, when detaching a database from a production server for use as a read-only database on a reporting server, the read-only environment will not require all of the log files used in production. ATTACH_REBUILD_LOG lets you copy the database to the reporting server without having to copy over all of the production log files.

|||

Thanks guys

that worked, I dettached the DB moved the Log file then re-attached it using the create command and the ATTACH_REBUILD_LOG and specifed teh location of the log file and it worked.

Moving a log file

Hi,
I know that I can detach a database, move the log file(s) and then
re-attach, but what is the
syntax to do it with Alter Database (or is it even possible)? I have
searched high and low and only found examples
that use sp_detach_db
Thanks,
DanFor 2000, no syntax using ALTER DATABASE except for tempdb.
For 2005, you can use ALTER DATABASE. See below 2005 BOL URL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:%2328kfSc%23FHA.600@.tk2msftngp13.phx.gbl...
> Hi,
> I know that I can detach a database, move the log file(s) and then re-attach, but what is the
> syntax to do it with Alter Database (or is it even possible)? I have searched high and low and
> only found examples
> that use sp_detach_db
> Thanks,
> Dan
>
>|||Thanks Tibor,
I know I had used it for tempdb and thought maybe I could use it for
'regular' db's.
Dan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ODXUvfc%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> For 2000, no syntax using ALTER DATABASE except for tempdb.
> For 2005, you can use ALTER DATABASE. See below 2005 BOL URL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:%2328kfSc%23FHA.600@.tk2msftngp13.phx.gbl...
>> Hi,
>> I know that I can detach a database, move the log file(s) and then
>> re-attach, but what is the
>> syntax to do it with Alter Database (or is it even possible)? I have
>> searched high and low and only found examples
>> that use sp_detach_db
>> Thanks,
>> Dan
>>
>|||Hi Dan
You can only use it for tempdb because tempdb is rebuilt every time you
restart your SQL Server. And in fact, when you use it for tempdb, it doesn't
take effect until you stop and restart.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:%23rjulyc%23FHA.2816@.tk2msftngp13.phx.gbl...
> Thanks Tibor,
> I know I had used it for tempdb and thought maybe I could use it for
> 'regular' db's.
>
> Dan
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ODXUvfc%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
>> For 2000, no syntax using ALTER DATABASE except for tempdb.
>> For 2005, you can use ALTER DATABASE. See below 2005 BOL URL:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/89f01b10-5fae-4ed8-b0fb-a4b9f540fd28.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
>> news:%2328kfSc%23FHA.600@.tk2msftngp13.phx.gbl...
>> Hi,
>> I know that I can detach a database, move the log file(s) and then
>> re-attach, but what is the
>> syntax to do it with Alter Database (or is it even possible)? I have
>> searched high and low and only found examples
>> that use sp_detach_db
>> Thanks,
>> Dan
>>
>>
>
>

Moving a log file

Monday, March 26, 2012

moving a db from 2005 to 2000

I have a vendor who uses sql server 2005 but we are currently using 2000 sp4
.
He has sent me a mdf, a ldf file and a bak file. I would like to get this
on my ss2k server. What is the best way to do this?
I've tried restoring the bak file but it tells me that there is a
compatibility problem.
I've tried to create a new db, detach and reattach the mdf and ldf file that
the vendor sent and I get the following error:
Error 602: Could not find row in sysindexes for database ID XX, object ID
X, index ID X. Run DBCC Checktable on sysindexes.
Does this mean that I need to run DBCC checktable on sysindexes on the
master db?
I ran this on my ss2k machine. Should the vendor run the dbcc
checktable(sysindexes) on his 2005 machine first and then ship me the mdf an
d
ldf?
Got to get this somehow, all suggestions are greatly appreciated.
EdieCould you have the vendor restore the database as a different name on their
2005 server (so as not to disrupt their real copy), change the compatibility
to 80, then back *that* up, and try restoring that on 2000? I haven't tried
that, it's just an outside suggestion.
You could follow the same steps and try it yourself, if you have a 2005
instance available (or could set one up).
"Edie Richardson" <EdieRichardson@.discussions.microsoft.com> wrote in
message news:E9B97EAB-4F28-4F88-BBAD-D268B4538AE5@.microsoft.com...
>I have a vendor who uses sql server 2005 but we are currently using 2000
>sp4.
> He has sent me a mdf, a ldf file and a bak file. I would like to get
> this
> on my ss2k server. What is the best way to do this?
> I've tried restoring the bak file but it tells me that there is a
> compatibility problem.
> I've tried to create a new db, detach and reattach the mdf and ldf file
> that
> the vendor sent and I get the following error:
> Error 602: Could not find row in sysindexes for database ID XX, object ID
> X, index ID X. Run DBCC Checktable on sysindexes.
> Does this mean that I need to run DBCC checktable on sysindexes on the
> master db?
> I ran this on my ss2k machine. Should the vendor run the dbcc
> checktable(sysindexes) on his 2005 machine first and then ship me the mdf
> and
> ldf?
> Got to get this somehow, all suggestions are greatly appreciated.
> Edie|||You cannot get a 2005 database into 2000 at the binary level (using either r
estore or attach).
Script and BCP/DTS/BULK INSERT etc is the way to go here. Compatibility leve
l of the database does
not change this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Edie Richardson" <EdieRichardson@.discussions.microsoft.com> wrote in messag
e
news:E9B97EAB-4F28-4F88-BBAD-D268B4538AE5@.microsoft.com...
>I have a vendor who uses sql server 2005 but we are currently using 2000 sp
4.
> He has sent me a mdf, a ldf file and a bak file. I would like to get thi
s
> on my ss2k server. What is the best way to do this?
> I've tried restoring the bak file but it tells me that there is a
> compatibility problem.
> I've tried to create a new db, detach and reattach the mdf and ldf file th
at
> the vendor sent and I get the following error:
> Error 602: Could not find row in sysindexes for database ID XX, object ID
> X, index ID X. Run DBCC Checktable on sysindexes.
> Does this mean that I need to run DBCC checktable on sysindexes on the
> master db?
> I ran this on my ss2k machine. Should the vendor run the dbcc
> checktable(sysindexes) on his 2005 machine first and then ship me the mdf
and
> ldf?
> Got to get this somehow, all suggestions are greatly appreciated.
> Edie|||> Script and BCP/DTS/BULK INSERT etc is the way to go here. Compatibility
> level of the database does not change this.
Ugh, hadn't tried it...

moving a database to another physical disk

I want to move 2 databases to another physical disk. They are both single file databases. It looks like a fairly straigtforward attach and detach procedure, but I have a couple of questions:

The log files (.ldf) currently reside on a separate physical disk from the data files, if I attach and reattach the data files will the logs remain where they are by default or do I have to re specify there location?

Is there any advantage to running the update statistics portion of the sp_detach_db?with the re-attach procedure you can specify the location of the log file as well. See the article in BOL and this is probably the quickest method. However the same thing can be done with backup and restore using the with move and replace arguments.

Moving a database to a server

I have a SQL Server 2005 Express database on my local machince called OpenAssess.mdf. The server we host with has a file extension of .mdb. How can I go about getting my database to the server? I tried changing the extension to mdb on the local machine but then it tried opening the database in MS Access and didn't work. I just need to connect to the database in my web pages. Here is my connection string and then the error which is visible at the botton of openassessment.org.

*********************************************connection string*************************************************

OPEN_Conn = "Provider=SQLOLEDB;Data Source=connectionToHostServer;Network Library=DBMSSOCN;Initial Catalog=OpenAssess.mdb;User ID=myuserid;Password=mypassword"

*****************************************************error*********************************************************

Microsoft OLE DB Provider for SQL Servererror '80004005'

Cannot open database requested in login 'OpenAssess.mdb'. Login fails.

Hi,

Which edition of SQLServer does your host support?

If SqlExpress, just upload your mdf file onto the app_data folder on your server and make the connection string look like:

"data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"

Generally, the host will give you the instance name and you may assign the data source attribute with that.

If SqlServer, you will need to have the .mdf database files attach to server manually since other editons of SQLServer does not support attaching database files automatically at runtime. Also,you will need to modify your connection string and make it look like:

"data source=ServerName\InstanceName;Initial Catelog=DatabaseName;User ID=myuserid;Password=mypassword"

Thanks.

sql

Moving a database to a new drive

I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
2005. I need to move the database and log file to a different drive from the
current location. It is currently on C: which is the system partion. I need
to move the log file and data file to H and I respectively.
The database has one filegroup, Primary. It has one datafile in the primary
filegroup, and one log file. I need to move these files to drives H and I
respectively.
Will someone be able to point me in the right direction on how to move this
files?
Thanks in advance.
ODINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>|||See reply in sqlserver.tools
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>|||Thanks Jasper. You have provided the exact information that I am searching.
For some reason I couldn't find it on microsoft's Web site.
Thanks again,
OD
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:e7LOZPd0GHA.720@.TK2MSFTNGP02.phx.gbl...
> See reply in sqlserver.tools
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>>2005. I need to move the database and log file to a different drive from
>>the current location. It is currently on C: which is the system partion. I
>>need to move the log file and data file to H and I respectively.
>> The database has one filegroup, Primary. It has one datafile in the
>> primary filegroup, and one log file. I need to move these files to drives
>> H and I respectively.
>> Will someone be able to point me in the right direction on how to move
>> this files?
>> Thanks in advance.
>> OD
>|||You could take a look at KBArticle
http://msdn2.microsoft.com/en-us/library/ms187858.aspx
--
Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>

Moving a database to a new drive

I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
2005. I need to move the database and log file to a different drive from the
current location. It is currently on C: which is the system partion. I need
to move the log file and data file to H and I respectively.
The database has one filegroup, Primary. It has one datafile in the primary
filegroup, and one log file. I need to move these files to drives H and I
respectively.
Will someone be able to point me in the right direction on how to move this
files?
Thanks in advance.
ODINF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/defaul...b;EN-US;q224071
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>|||See reply in sqlserver.tools
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>|||Thanks Jasper. You have provided the exact information that I am searching.
For some reason I couldn't find it on microsoft's Web site.
Thanks again,
OD
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:e7LOZPd0GHA.720@.TK2MSFTNGP02.phx.gbl...
> See reply in sqlserver.tools
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>|||You could take a look at KBArticle
http://msdn2.microsoft.com/en-us/library/ms187858.aspx
Thanks
Sethu Srinivasan, Software Design Engineer, SQL Server Manageability
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"OD" <oludan@.hotmail.com> wrote in message
news:%238v5kKd0GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I have a CRM 3.0 application running on a Windows server 2003 R2 and SQL
>2005. I need to move the database and log file to a different drive from
>the current location. It is currently on C: which is the system partion. I
>need to move the log file and data file to H and I respectively.
> The database has one filegroup, Primary. It has one datafile in the
> primary filegroup, and one log file. I need to move these files to drives
> H and I respectively.
> Will someone be able to point me in the right direction on how to move
> this files?
> Thanks in advance.
> OD
>

moving a database from 2005 to 2000

Hi I have heard that you can not restore a 2000 SQL database from a 2005 file
but wondering if you can run scripts in sql 2000 created in 2005 to recreate
the database?
thanks
--
Paul G
Software engineer.Yes. When you generate the script set the "Script for Server Version"
option to SQL Server 2000.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Apr 2008 15:13:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I have heard that you can not restore a 2000 SQL database from a 2005 file
>but wondering if you can run scripts in sql 2000 created in 2005 to recreate
>the database?
>thanks|||ok found the script type selection option! thanks.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> Yes. When you generate the script set the "Script for Server Version"
> option to SQL Server 2000.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 25 Apr 2008 15:13:00 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I have heard that you can not restore a 2000 SQL database from a 2005 file
> >but wondering if you can run scripts in sql 2000 created in 2005 to recreate
> >the database?
> >thanks
>sql

Friday, March 23, 2012

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.
>

Moving .ldf to new drive, doesn't work gives "Cannot associate fil

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?
>
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.googlegr oups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> 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 T-log to another Disk?

Hello DBAs,
I think there are several different ways to move a
transaction log file to another disk. What would you do?
what method will you go with?
Currently DBs and T-logs are located in same disk, and I
learned that if they are located different disks would
increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
SunnyThis is a multi-part message in MIME format.
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Detach the database using sp_detach_db. (Don't bother updating the =statistics.) Move your log to the new drive, then re-attach, using =sp_attach_db.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"litnadsl" <lintadsl@.yahoo.com> wrote in message =news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
Hello DBAs,
I think there are several different ways to move a transaction log file to another disk. What would you do? what method will you go with?
Currently DBs and T-logs are located in same disk, and I learned that if they are located different disks would increase SQL server performance from SQL trainning class.
Please advice me on this, new DBA here.
Thanks in advance.
Sunny
--=_NextPart_000_0295_01C3512E.6C124CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Detach the database using =sp_detach_db. (Don't bother updating the statistics.) Move your log to the new =drive, then re-attach, using sp_attach_db.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"litnadsl" wrote in =message news:031f01c3514e$f6=3ce230$a301280a@.phx.gbl...Hello DBAs,I think there are several different ways to move a =transaction log file to another disk. What would you do? what method will you =go with?Currently DBs and T-logs are located in same disk, and I =learned that if they are located different disks would increase SQL server performance from SQL trainning class.Please advice me on this, =new DBA here.Thanks in advance.Sunny

--=_NextPart_000_0295_01C3512E.6C124CF0--|||If you have a RAID system then you do not need to store the log file to a
different disk.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Detach the database and then when you do sp_attachdb then specify different
locations.
This you can do with user databases.
For system databases, you need to follow different approach.
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||For user databases, follow Tom's description. For master, model, tempdb,
there're more steps involved including setting up trace flags and modifying
startup parameters. Please refer to the following MS KB articles:
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
Richard
"litnadsl" <lintadsl@.yahoo.com> wrote in message
news:031f01c3514e$f63ce230$a301280a@.phx.gbl...
> Hello DBAs,
> I think there are several different ways to move a
> transaction log file to another disk. What would you do?
> what method will you go with?
> Currently DBs and T-logs are located in same disk, and I
> learned that if they are located different disks would
> increase SQL server performance from SQL trainning class.
> Please advice me on this, new DBA here.
> Thanks in advance.
> Sunny|||Danut
I'm a little puzzled how you think a RAID system affects
the way a Transaction log is sequentially written to? (As
that is the main reason for putting it on a seperate disk)
Regards
John|||Well, I'm not expert but I read some articles on MSDN. Disk stripping offers
the best performance, adding the parity will make it falut tolerant so you
get both.
When you write to a RAID logical drive it will write it to more than one
physical drive in parallel and this is what makes it performant.
Well, let's look into MSDN:
"Both Microsoft Windows NT® and Microsoft Windows® 2000-based disk striping,
and striping with parity, can improve performance. Disk striping with parity
also protects against data loss in the event of media failure."
"Disk striping writes data in stripes across a volume (created from areas of
free space). For more information about volumes, see the Windows NT or
Windows 2000 documentation.
These areas are all the same size and are spread over an array of disks (up
to 32 disks). Striping writes files across all disks, so data is added to
all partitions in the set at the same rate.
Windows NT-based disk striping and Windows 2000 volume sets implement RAID
0. Disk striping provides the best performance of all Windows NT Server
disk-management strategies, but does not provide any fault-tolerance
protection.
Disk striping with parity is similar to disk striping. Disk striping with
parity adds a parity-information stripe to each disk partition in the
volume. This provides fault-tolerance protection equivalent to that of disk
mirroring, but requires much less space for the redundant data. Windows
NT-based disk striping with parity and Windows 2000 RAID-5 volumes implement
RAID 5. "
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0c6901c351f3$d490d470$a501280a@.phx.gbl...
> Danut
> I don't think you have got the idea here yet. RAID 5 uses
> an extra disk for parity checking. Say you have four disks
> in your RAID 5 array. You only have three disks to store
> data as you use one quarter of the space to hold parity
> data. Sql server uses this so that if one disk fails it
> can use the parity data to tell what data is missing. When
> the disk is replaced it can then rebuild the data.
> Performance wise RAID 5 is the worst of the raid systems,
> it turns one write into two reads and two writes to
> maintain the parity information.
> RAID 5 is not that bad if the database is mostly used for
> reads, with little write activity. For databases with high
> write activity, you are much better off using RAID 10 or
> RAID 01.
> Transaction logs are written too sequentially. If you keep
> your Transaction logs on seperate mirrored drives the
> heads are not moving all over the place they are writting
> where they already are. Putting transaction logs and
> database data files together on RAID 5 disks is just about
> the worst thing you can do performance wise. It might make
> administration easy, but it is a very bad idea.
> The only exception to that is if you are using OLAP with
> no update activity on the database.
> Regards
> John|||I know what is does, I've been a DBA for years, I'm just
telling you in simple terms
RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
WRITE A LOT
HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
SLOWS PERFORMANCE
You will not find anyone on here that disagees with any of
those statements.|||I will attest to what John is saying. Regardless of the Raid used it is
detrimental to performance to have the log file on the same array as the
data. The data is written randomly and the log is sequential and these two
just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
than a Raid 5 but in any case it should always be on it's own array.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> I know what is does, I've been a DBA for years, I'm just
> telling you in simple terms
> RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> WRITE A LOT
> HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> SLOWS PERFORMANCE
> You will not find anyone on here that disagees with any of
> those statements.|||Ok. I got it.
Thanks!
Danut
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uZdPL2fUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> I will attest to what John is saying. Regardless of the Raid used it is
> detrimental to performance to have the log file on the same array as the
> data. The data is written randomly and the log is sequential and these
two
> just don't mix well. A Raid 1, 1+0 or 0+1 are much better for log files
> than a Raid 5 but in any case it should always be on it's own array.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:0eb101c351fb$6df46440$a401280a@.phx.gbl...
> > I know what is does, I've been a DBA for years, I'm just
> > telling you in simple terms
> >
> > RAID 5 IS THE WORST PERFOMING RAID FOR DISK WRITES
> >
> > TRANSACTION LOGS PERFORM BADLY ON RAID 5 BECAUSE THEY
> > WRITE A LOT
> >
> > HAVING A TRANSACTION LOG ON THE SAME DISK AS YOUR DATABASE
> > SLOWS PERFORMANCE
> >
> > You will not find anyone on here that disagees with any of
> > those statements.
>

Friday, March 30, 2012

moving clustered indexes

moving and renaming a file

Wednesday, March 28, 2012

moving a SQl 2005 log file

moving a SQl 2005 log file

moving a SQl 2005 log file

Moving a log file while still "attached"

Moving a log file while still "attached"

Moving a log file

Moving a log file

Moving a log file

Moving a log file

Monday, March 26, 2012

moving a db from 2005 to 2000

moving a database to another physical disk

Moving a database to a server

Moving a database to a new drive

Moving a database to a new drive

moving a database from 2005 to 2000

Friday, March 23, 2012

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

Moving .ldf to new drive, doesn't work gives "Cannot associate fil

Wednesday, March 21, 2012

move T-log to another Disk?

Move database file

Blog Archive

About Me