I have a Windows 2003 server with SQL Server 2005 installed. The
server is on small drive and we would like to upgrade to much larger
harddrives. I've been hearing of problems using Ghost to get an image
and placing the image onto the new drive. I think this is more of a
Windows 2003 problem, but this server is for nothing but the SQL
Server databases. Does anyone have a clear method of moving this
server to the larger drives?
TIA.
We are planning to install more disk space and need to temporarily move the data and log files and move them back after the disk space has been added.
Is Detach/Attach the best way to handle this?
Being a very novice SQL Server administrator, I need to ask the experts a question.
How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?
Thanks,
Rick
We installed MS SQL Server to our drive D drive E was the CDROM Drive.We wanted to move the CDROM drive to drive D and the hard drive to driveE. We change that around and corrected the registry entries -repointing everything to drive E instead of D. SQL Server however willnot start it says it cannot find the databases. Where is thisinformation stored. How can I go about changing it?~Todd
View 2 Replies View RelatedI have a SQL server with multiple instances on it and would like to move one of them to a drive with more storage.
I have SQL 2010 on a server with 2 partitions.
The database is located on the C: drive (original build) but the drive isn't partitioned to handle a db of the size that this one will grow to. I would like to move the full DB instance to another partition.
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!
I am new to sql server world as I am a controls engineer who is being asked to manage an asset management system at our facility. We have a system that uses SQL 2005 Express. My database is on drive C, but it is only 10gig and it is running out of space. I have a 210 GIG hard drive as drive D. What is the easiest way to move the database from C to D?
View 18 Replies View RelatedHi
I have a database(CEB) and my CEB.mdf is on D Drive and CEB.LDF is on
G DRIVE ...NOw I want to move the CEB.LDF on to the different drive ..
can any one suggest me the way and will I have any effect on the database.
It is kind of urgent.
Thanks
RAGHU
Hello All,
Can anyone be so kind as to turn me on to a script to move a database from spilt drives C: and D: to just drive D:. (we have one of those Dell's that comes with C/D partitions so we split the .dta files with a limit on the primary file, but the damn C: drive still ran low on disk space and now we can't install Win 2003 SP2 on it!)
thanks in advance
Bill
I currently have about 4 databases on our SAN located in one of the drives. These databases are going to expand massively and I want o seperate 1 onto seperate drives located on the SAN. I figured using SQL Server Management Studio I could complete this with an easy "Detach / Attach" operation. When I go to attach the files back into SQL, it doesn't read any other drive other than the current drive all of the databases are located on.
Is there a way to do this?
I currently have about 4 databases on our SAN located in one of the drives. These databases are going to expand massively and I want o seperate 1 onto seperate drives located on the SAN. I figured using SQL Server Management Studio I could complete this with an easy "Detach / Attach" operation. When I go to attach the files back into SQL, it doesn't read any other drive other than the current drive all of the databases are located on.
Is there a way to do this?
Hello all -
I have TFS installed on one machine, and the SQL Server database on another. I made the unfortunate mistake of installing SQL Server to the OS drive (C : ), so the TFS database is writing to this drive. How can I switch this to write to another drive (e.g. E:)?
thanks
Hi,
I'm trying to move the transaction logs of my databases to a different drive (for fault tolerance). I can create a second transaction log file for each database via Enterprise Manager but I have 2 questions:
1) If two transaction log files exist for a database which one does it use ?
2) How do I force SQL to use the new transaction log file ? (so I can delete old)
Thanks,
Tim
Hey guys i want to relocate my database datafile and transaction logs from C: drive to D:
From what i have in mind , correct me if i am wrong: First I will create the same folder on D drive as they are on C drive then copy the datafile from C to D , then come back and change the paths on the database files to point on D.
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 RelatedHello all ...
is there a standard procedure or document that explains how to:
Expand tempdb onto a faster drive ... making it larger
then ...
Remove the small portion of tempdb from my c: drive to reduce contention?
Thanks!
Doug
I want to move all indexes into new separate drive for this I need to create new .ndf file in particular drive.
Mirroring has configured for the DB but the principal and mirror servers do not have same drives.
I want to move indexes to new drive and the derive is not present in mirror server.
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.
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
Hi
I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.
Can I not use a \ path and only use mapped drives?
Thanks
below works...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])
below doesn't work...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])
I am trying to move a log file from one drive to another.
What I have done is add another file to my file group. So now my log has a file on the 'e' drive and one on the 'f' drive. I now want to remove the file on the 'e' drive. I have emptied the file on the 'e' drive. When doing the command:
ALTER DATABASE Uniprodruntime
REMOVE FILE m_rk_runtime_log
I get the following error message..
Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
I have also gone into enterprise manager and tried to delete the file and it does nothing.
Has anyone run into this?
How do you compare SAN drive vs local drive on a 32 bit server?
Is it good idea to move my DB files to a SAN instead of local?
Canada DBA
Server: SQL 2008 R2
1: TempDB keeps getting filled. Restart of the server has not fixed it. I shrink it, but the space gets filled again. Now I can't even shrink it anymore
2: TempDB is at the wrong location. Its current location is this :C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
How do I change its location?
C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
Correct location of TempDB should be: TempDB(T:) But its not there
I am using sql server 2000 developer editionMy table defintion is as followin: CREATE TABLE query_table (id IDENTITY (1, 1) NOT NULL ,qtext char (4000) )When i try to insert data with length more than 256 characterit only inserts only the first 256 characters.Please, let me know why this is happening.
View 2 Replies View RelatedI have a situation where I need to migrate data from an older platform to a newer one. The data from the old system(s) will be available on DAT tapes. All database construction on the new system will be identical to the old one in size and schema, except for one table (call it "ARCHIVE").
If the ARCHIVE table on the old system is 210MB, and the ARCHIVE table on the new system has the same attributes but has been expanded to 380MB in size, can I simply restore the dump for the old table into the new ARCHIVE?
Empirically it works (I have done it with apparent success two times) but I seem to recall that backups are done by pages, and I'm concerned that there may be conditions not being met by simply doing the restore the way I'm planning to do it.
Also, are there any tests or checks built into SQL which I can use to check table integrity on the target ARCHIVE table after the restore?
Any help is greatly appreciated.
Best rgds,
Kevin
Is it possible to make a text-field or something like that, which can contain more than 255 characters ?
View 1 Replies View RelatedHi,
i want to be alerted when any of my databases is more the xxxGB.
i know one method to do that and it is with the alerts in the SQL agent Performance condition ,but with this alert i needs to created alert monitor for every DB.
do you know a better way to achieve that ?
THX
I have two int fields in my database, CEOAnnualBonus and CEOBonus, and I want to return the value of whichever one has the larger value as CEOBonusCombined. I thought using COALESCE would do the trick like below but there are many cases where either CEOAnnualBonus or CEOBonus have a zero value instead of NULL and it doesn't work.
SELECT
COALESCE(CEOAnnualBonus, CEOBonus)
AS CEOBonusCombined
FROM tbenchmarktemp
WHERE Ticker='F'
Thanks for any help
I have written code to combine, delete redundant data in my system. The table structure remains the same, except I changed some INTs to TINYINTs.
When I do sp_spaceused, it tells me that number of rows is smaller(which is correct), but the datasize, and index_size is significantly larger AFTER the deletions.
I tried using shrink, but that doesn't seem to change anything.
When I right-click the database, and choose PROPERTIES, it also confirms that the database got significantly larger.
I am confused about how deleting data and changing to TINYINTs could make my database bigger. What would cause this?
Greetings all and thanks for reading this post.
Here is my situation... I have 2 fairly large databases. Full backups are 83gb & 63gb. I am in the process of moving these database to a new data center. I've taken full backups of these databases and shipped them to the new center. I have been taking transaction log backups (larger db every 24 hrs smaller db every 15 min ... from log shipping).
I want to restore these databases in the new data center. I've gone ahead and restored the dbs in the new location.
Question final cutover.. can I just apply the transaction logs to the databases on final cut-over or do I have to restore the database backup first then apply the transaction logs?
Is there an other way to do this that I'm missing?
Thanks.
Kurt
I have replication setup between our main site and a remote one, and have recently noticed that the database at the remote site's .MDF file is about 3 times as large as the main site's. This doesn't seem to make sense since essentially all of our data is replicated between the two servers. Can anyone suggest why this might be happening and what is safe to do to shrink the remote file?
TIA
Ron L
Hello.
I created SDF database files for "SQL compact" with a size larger than 128
MB (which is default for creation). Now when I try to open these files with
VS2005 I get the error "The size of the databasfile exceeds the configured
maximum...Required Max Databse size (in MB; 0 if unknown)" (translated from
german). The real problem is that I can not change the connectionstring in
VS, cause all field which show the connectionstring are readonly (greyed
out). I know I have to set the option "Max Database Size = 512" or so in the
connectionstring to get the things runnning, but don't know any way to do
that in VS2005.
My attempt to access the SDF files when copied to the device via active sync
results in the same error message.
This seems for me to be a design flaw, cause I can not add the optional
parameter to the connection string (even not in the details form, where only
"DataSource" and "Password" fields are displayed.
- Does anybody know a solution in VS2005?
- Does anybody have a workaround for me?
- Does anybody know where the connectionstrings of VS2005 are stored, so
that I may "hack" the connection string?
Thanks so far.