Moving Data Files To A New Disk, Same Server

Jul 23, 2005

Hello, I have been having a bit of trouble finding help on the safest
way to move data files to a different disk on the same server. Most
help is about moving data files to a different sqlserver. I just want
to move the files to a different drive on the same server. Any help
would be appreciated.

Thanks,

David

View 6 Replies


ADVERTISEMENT

Changing The Data Files To Another Disk Partition

May 26, 2004

Hi,

I have a sql server 7 running on a machine with two disk partitions (D: and E:).

The data files xxx.mdf and xxx.ldf are stored in D:, which has very few space available. I want to copy these files to E: but I get an error saying that it is not possible to change the source file of a database. Is it possible to do it or do i have to create another data file in E: and keep the old one in D:?

Thanks in advance,

browser

View 3 Replies View Related

Drive Filling Up / Moving Data Files?

Sep 29, 2015

I have a database [CarlosDB] that currently has it's .MDF on E: and I need to move the x2 .NDF data files off C: to E:data using a single T-SQL statement:

Code:

database_id file_id db_name disk_path status size read_only
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
7 1 CarlosDB E:dataCarlosDB.mdf ONLINE 384 0
7 2 CarlosDB_log L:logsCarlosDB_log.ldf ONLINE 128 0
7 3 CarlosDB_2 C:sqlCarlosDB_2.ndf ONLINE 128 0
7 4 CarlosDB_3 C:sqlCarlosDB_3.ndf ONLINE 128 0

(4 row(s) affected)

Looking at the file configuration above, what would be the most logical way as a DBA / SQL Server 2014 Std to move the NDF files to live w/ the MDF file using:

Code:

EXEC master.dbo.xp_cmdshell 'copy c:sqlCarlosDB_2.ndf e:dataCarlosDB_2/ndf'...

but cleanly using a single T-SQL statement? properly formatting a single T-SQL query to use the xp_cmdshell system stored procedure.

View 5 Replies View Related

SQL 2012 :: Moving DB Data / Log Files Within AlwaysOn Causing (Recovery Pending)

Jan 9, 2015

I'm trying to move a log file of a database that is part of an availability group. I have been following steps from the article: [URL]

At first this worked fine for me in a test environment. When I tried it in a production environment the database on the secondary went into "Recovery Pending" state and I can't get it out.

I checked to ensure that the dB is looking in the right place for the log file, and it is. It just doesn't seem to actually use the new file. If I start and stop SQL service, the dB comes back up and is fine.

Here are the steps I'm going through and what is happening at each step:

--------------------------------------
:Connect DEVSQL --This is currently PRIMARY
USE[master]
GO
ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'DEVSQL' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))

[Code] ....

All is good so far. Both the Primary and the Secondard have had their logical files changed, which has not taken affect yet because there has been no failover.

--Make SQL10 the PRIMARY
:Connect SQL10
ALTER AVAILABILITY GROUP [DP-AG-DEV] FAILOVER;
GO

SQL10 is now the Primary for this AG. And, as expected, the database [AG-Test] is in "Recovery Pending" because it is now looking for the log file in the new location. I need to move the file to the new location.

:Connect DEVSQL
--Enable XP_CMDSHELL
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1

[code].....

This is where the script is failing, returning the error:

Msg 1468, Level 16, State 5, Line 5

The operation cannot be performed on database "AG-Test" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

Msg 5069, Level 16, State 1, Line 5

ALTER DATABASE statement failed.

I can not get the dB to recognize the log file at it's new location.

If I restart the SQL Service, it comes back fine, which seems to indicate to me that it is not a permission problem and confirms that the file is in the right place.

How do I force SQL to look for the log file again without restarting the service?

View 2 Replies View Related

DB Design :: Can Use Another Server / Disk To Store Files Using FileTable Concept?

May 11, 2015

I tried samples of FileTable in SQL Server 2012 to store files in database. I have following questions.

1. When I right click on FileTable, and say Explore File Table Directory, I was taken to a directory. But if I search the same directory in my server/machine, the directory is not available.

2. Can I use some external server/disk as a directory for FileTable?

3. How easy is to use FileTables using .Net web applications?

View 2 Replies View Related

Moving LogFile From One Disk To Another

Apr 11, 2002

I am trying to move a logfile from one disk to another. I've detached the database, moved the logfile to another disk and reattached the database. I used the sp_detach and sp_attach_db to specify the paths of the *.MDF and *.LDF. I've tried it through Enterprise Manager and with boths ways I get an error message saying: "Error 9003: The LSN (201:48:2) passed to log scan in database 'DatabaseName' is invalid". Does anyone know what this means and how I can get around this. I've even tried to attach the database with the logfile in the original path and I still get that error. Need Help Please!

Thanks

View 2 Replies View Related

Moving RootDirectory From Disk C To D

Nov 8, 2007

Hello,

I need move my SQL Server 2005 installation (Database Engine, Analysis Services) from one disk to another.

C:Program FilesMicrosoft SQL ServerMSSQL.1C:Program FilesMicrosoft SQL ServerMSSQL.2
to

D:SQL2005MSSQL.1D:SQL2005MSSQL.2
May you help me how do this?


--
Regards,
anxcomp

View 2 Replies View Related

Moving DB Files To Another Drive On The Server.

Sep 27, 2005

Howdy y'all! :)

I have been instructed to move a large database we have on one of our servers off the current drive (local RAID-5 driveset in the server) to a EMC "drive" (logical drive, off-server).

I know one option is to back up the database, delete the database, re-create the db using the new drive for data/log files, then restore the database.

However, I was wondering if it would be better to just detach the DB, move the data/log files, then reattach to them?

Is it half-doz of one, and 6 of the other?

How should I go about this dastardly deed?

Off to poke around in BOL, but thought I would post first in case it's an incredibly easy answer for y'all

Thanks!

View 3 Replies View Related

Moving A Database To Another Physical Disk

Jun 7, 2006

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?

View 1 Replies View Related

Moving Transaction Log File To Another Disk

Apr 5, 2004

Hi all,
I have to give support to this new client of ours and the system out there was a real mess.The log files for all four databases were ranging from 5 to 9 GB's, no maintanance no nothing was done since the setup of the system. I have truncated the logs after taking proper backups since it all started after they compalined a DTS was not working, which was due to no disk space available ;).
Now I have to shift the log files to a seperate disk.
I know it can be done by detaching the database and attaching it back, I was wondering if we can do it without bringing the system offline?
Anycomments welcome.
Thankyou.
regards,
harshal.

View 1 Replies View Related

Moving Indexes To Their Own Disk Drive

Apr 22, 2004

I am trying to find out if it is possible to move indexes to a separate filegroup/disk drive during database restore. I am trying this to see if it improves performance. Also if I cannot move the indexes during restore, how would I move them afterwards to a different filegroup/disk drive? Thanks in advance for all the help.

View 7 Replies View Related

Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)

May 7, 2007

Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.



In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.

Linda



Here is the old VBScript Code:

Public Sub Main()

Option Explicit

Function Main()

Dim MovementDataDir

Dim MovementArchiveDataDir

Dim MovementDataFile

Dim MovementArchiveDataFile

Dim FileNameRoot

Dim FileNameExtension, DecimalLocation

Dim CurMonth, CurDay

Dim FileApplicationDate

Dim fso ' File System Object

Dim folder

Dim FileCollection

Dim MovementFile

'======================================================================

'Create text strings of today's date to be appended to the archived file.

FileApplicationDate = Now

CurMonth = Month(FileApplicationDate)

CurDay = Day(FileApplicationDate)

If Len(CurMonth) = 1 Then

CurMonth = "0" & CurMonth

End If

If Len(CurDay) = 1 Then

CurDay = "0" & CurDay

End If

FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)

'=====================================================================

' Set the movement data directory from the global variable.

MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value

MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value

fso = CreateObject("Scripting.FileSystemObject")

folder = fso.GetFolder(MovementDataDir)

FileCollection = folder.Files

' Loop through all files in the data directory.

For Each MovementFile In FileCollection

' Get the full path name of the current data file.

MovementDataFile = MovementDataDir & "" & MovementFile.Name

' Get the full path name of the archive data file.

MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name

DecimalLocation = InStr(1, MovementArchiveDataFile, ".")

FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)

FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)

MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension

If (fso.FileExists(MovementDataFile)) Then

fso.CopyFile(MovementDataFile, MovementArchiveDataFile)

' If the archive file was coppied, then delete the old copy.

If (fso.FileExists(MovementArchiveDataFile)) Then

fso.DeleteFile(MovementDataFile)

End If

End If

Next

fso = Nothing

folder = Nothing

FileCollection = Nothing

Main = DTSTaskExecResult_Success

End Function

View 6 Replies View Related

Moving Tempdb To Free Up Disk Space

Nov 29, 2000

does anyone know if tempdb can be physically moved to a different partition on a disk drive on SQL Server 7.0? Since it can't be backed up I'm hesitant to use the sp_detach/sp_attach procedure because I don't want to crash it. If nothing else is available, I can attempt moving it this way at the end of the day and then just reboot to get tempdb back up again if the server fails, but I'd really appreciate a suggestion from someone who has more know-how than I do about system table operations. Thanks again

View 1 Replies View Related

Setup And Upgrade :: Moving Log File To Different Disk

Sep 16, 2015

Do you have to move the entire content of the log folder to the new location?

View 4 Replies View Related

DB Engine :: Moving Database Backup Files From One Server To Another?

Sep 15, 2015

How to move the database backup files (.bak) from one server to another server using ‘XP_CMDSHELL’

View 2 Replies View Related

Files As BLOBS Or On Disk?

Dec 14, 2006

Hi

I wanted to ask a question in terms of design (Performance, scalability, maintenance, flexibility etc.) is it better to refernce a document on the actual file system (on a file server) i.e. from a database link to the actual file at a disk location
e.g. in the database have something like \fileserver2006 estewtext.txt or C: estewtext.txt

or is it better to store all the files in a database as blobs?

The Question i would like to ask is more in the context of desigining a document management system?

View 5 Replies View Related

Multiple Database Files On The Same Disk

May 16, 2007

Hi there



It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?



I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

View 10 Replies View Related

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.

View 2 Replies View Related

Do I Save Disk Space Storing Images In Db Vs. Loose Files?

Jan 16, 2007

Hello --
I'm building an app that will allow users to create their own photo galleries.  At this point, I'm planning on storing all photos as byte arrays in SQL server image fields.
Besides the organizational benefit, is there a space benefit to doing this?  That is, if I have 1MB of .jpg's, will those same images take up less than 1MB of file space within the database?
One of the reasons I ask is that most hosting plans out there seem to offer more "normal" disk space than is allocated for the database, so I'm trying to make a best plan to accommodate what will probably end up being the biggest disk space consumer in my app (the photos, that is).
Any other recommendations re: this scenario (hosting, best practices) are appreciated.
TIA,
Eric

View 8 Replies View Related

Is It Possible To Move My Sql 2000 Database (in C Disk) To Another Disk (Disk) ?

Dec 28, 2006

hello,all
          I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure)   from C Disk to D Disk(its space is very large) .
         is it possible to do it ? 
         if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal  report connectstring ) ?
        thanks in advanced!
 
 
 
      

View 1 Replies View Related

Moving .MDF Files

Mar 4, 2008

Hi,
I'm using SQL 2005 express edition and my database files are in the MSSQL.1MSSQLData folder. When I'm trying to copy them I get "Files already in Use" error. How do I stop the server from running (services or command line). Or is there a better why to export
them to a diffrent location.

Thanks

View 3 Replies View Related

Moving Log Files

Jul 23, 2005

If I use attach/detach to move my log file to a different drive, willit break any of my permissions for the sql logins. If so, how can I fixthis.This all stemmed from needing to setup log shipping to another serverfor redundancy. Step 1 says to create a share where the log filesreside. Well the sql server was installed by a previous employee andthe log files are in the same directory as the data files. I would feelmore comfortable (security wise) if I only shared out the directorywhere the log files lived.If anyone thinks that I am traveling down the wrong path or has anysuggestions, please let me know.Thanks

View 3 Replies View Related

Moving Files

Apr 1, 2008

How can i move files from one folder to other using script task

pls specify if its clear n can i ask this query in this forum

View 3 Replies View Related

Moving Mdf Files To A SAN

May 12, 2008



Hi, I'm fairly new to SQL, so I hope someone can help.

I'm trying to move our SQl data files to a new drive on a SAN. The MDF files are currently located in the E;mssql directory.

I want to keep the same drive letter, so I am attempting to do the following

1: stop SQL
2: xcopy data and ntfs permissions from E: to G:
3: rename e: to h:
4: rename g: to e:
5: reboot Server

when I do this, I get the following errors and SQl fails to start

udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device e:datamssqlDATAmodel.mdf.

FCB:pen failed: Could not open device e:datamssqlDATAmodel.mdf for virtual device number (VDN) 1.

Device activation error. The physical file name 'e:datamssqlDATAmodel.mdf' may be incorrect.

is anyone able to tell me what I'm doing wrong?

thanks!

View 7 Replies View Related

Moving Files

May 11, 2007

Hi,



I need to move backup files from the production server to another server. This would be regularly scheduled file move only for security reasons. The target server does not have SQL Server installed. Which is the best way to do this?



Thanks

View 1 Replies View Related

Database (model) Cannot Be Opened Due To Inaccessible Files Or Insufficient Memory Or Disk Space

Aug 4, 2015

How to fix this SQL error event logg 17204 and 17207 ?

View 8 Replies View Related

Moving Errorlog Files

Sep 17, 2001

I need to move the errorlog files from the d: drive to the e: drive on my NT servers. Does anyone know a way to accomplish this without having to re-install?
Thanks
tcb

View 2 Replies View Related

Moving Databases Files.

Feb 7, 2005

I like to move my database from a directory to another (on different drives foir exemple).
Is there an easy way to do this kind of job ?

View 3 Replies View Related

Moving Database Files

Aug 28, 2007

Sorry for the ultra-beginner question, but I've just recently started playing with SQL 2005 Express Edition for a task I've been assigned to at work.

I'm building an ASP.NET 2.0 web site that needs to connect to a SQL database to pull information. I installed SQL Express 2005 and installed the SQL Server Management Studio and I've managed to build a small database with one table.

I noticed that by default, the mdf and ldf files are located in C:Program FilesMS SQL ServerMSSQLDATA (or something along those lines). The website I'm working on is temporarily stored in C:WebSite. I need to move the database files over to C:WebSiteApp_Data so I can access them easily with ASP.NET and VS 2005 Express.

I can copy and paste the mdf and ldf files, but then I can't figure out how to point the SQL Server Management Studio to the new location.

I am obviously just a complete newbie at all of this. It's pretty sad that I can't figure out how to simply move the database from one directory to another on the same server... it makes me worry about when I'll need to move it to the new webserver!

Could anyone point me in the right direction at least?

Thanks in advance!

View 2 Replies View Related

SQL Server, Disk Arrays And Disk IO

May 7, 2004

Hi all,

Ok here goes,

I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.

So my options as I understand it are:

Get Enterprise edition

or

Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.


I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.

Does anyone know how much performance increase I will get from using two arrays as opposed to one?


Any other advice on this scenario would be greatly appreciated.


Thanks

View 4 Replies View Related

Moving Data From One Sql Server To Another

Jul 5, 2002

Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:

1what should be the ideal spec of a sql server to allow for windows 2000 in future. And how do I go about moving data from one to the other. I am new to sql and this is my first assignment.

Thanks

Eve

View 1 Replies View Related

Moving Data From One Sql Server To Another

Jul 5, 2002

Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:

1. What should be the ideal spec of a sql server to allow for windows 2000 in future.

2. Is there any kind of monitoring agent available to see how heavily loaded the server is and what load each application/database is creating.

3. And how do I go about moving data from one to the other.

4. What do I do at the clients end to ensure they are able to access their database.

I am new to sql and this is my first assignment.



Thanks

Eve

View 1 Replies View Related

Moving Data To New Server?

Oct 28, 2013

I have a server that will be going off-line in a couple of weeks, and in the meantime, I need to backup everything and move it to a new location. This involves a lot of data and databases.

What I have is 33 databases in MS SQL Server 2005. The target machine is MS SQL Server 2008. Each of these databases has a myriad of stored procedures and compiled functions that will need to be moved as well. Essentially the task is to completely replicate the entire MS SQL environment.

Is there a shortcut to making this happen or am I relegated to backing up each database individually and then restoring them individually?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved