Transact SQL :: Moving MDF And NDF Files After Database Is Detached
Aug 19, 2015
I am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
View 7 Replies
ADVERTISEMENT
Nov 11, 2007
Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
View 3 Replies
View Related
Oct 10, 2007
I have detached the database via sp_detach_db in a job, (I was trying to use a job to detach the database, then make copies of the mdf file, then attach the databases as different copies, somehow the job failed due to file locked) I tried to delete the physical files, but they were still locked. I tried several times, two of them I was able to delete the mdf files, but not the ldf file. The other one both mdf file and ldf file can not be deleted. I was not able to see the database via Management studio.
Can anyone help?
Thanks
View 3 Replies
View Related
Oct 22, 2007
Hi
I have a database consisting of multiple files on a remote server.
Both servers have the same user (bob) which is in the administrator group.
The directory that the mdf/ndf files exist on is shared as "nas" with full rights given to the user bob.
They are both in the same workgroup.
SQL server is running as this user (not ideal I know but server and services will be locked down later)
The script I used to create the database uses the following line to enable remote database files:
-- Enable the use of network drive for DB files
DBCC TRACEON(1807)
GO
I used SSMS to detatch the database and then modified my create script by removing database sizes and adding "FOR ATTACH" at the end. (I have also tried using sp_attach_db but no use)
So the script looks like this:
CREATE DATABASE [dbname] ON PRIMARY
( NAME = N'DBName', FILENAME = N'\123.45.67.89asDBName_FG.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 10GB ),
-- Filegroups for the Alerts Table and Indexes.
FILEGROUP [FG_DBFile2]
( NAME = N'DBFile2_FG', FILENAME = N'\123.45.67.89asDBFile2_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB ),
FILEGROUP [FG_AlertsIndexes]
( NAME = N'DBFile3_FG', FILENAME = N'\123.45.67.89asDBFile3_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB )
LOG ON
( NAME = N'DBName_log', FILENAME = N'\123.45.67.89asDBName_log.ldf' ,MAXSIZE = 13GB , FILEGROWTH = 0%)
FOR ATTACH
GO
When I run the script I get the following error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5120, Level 16, State 101, Line 5
Unable to open the physical file "\123.45.67.89asDBName_FG.mdf". Operating system error 5: "5(error not found)".
I know the permissions are ok as I can map a drive without being prompted.
SQL server is running as the user bob which is the same user I log onto the server as.
I am running Windows 2003 Server R2 Standard Edition on server with SQL Server 2005 SP2.
The NAS box is running SQL Server R2 Storage Edition.
One thing I noticed was that the mdf/ndf files on the NAS box are locked as if SQL is still using them, i.e. I cannot rename them, move them, alter any properties etc. I just get "Access Denied".
I've tried rebooting both servers but still no luck. I've also tried stopping SQL server then renaming the files, but again "Access Denied".
I know there has been a problem in the past detaching and attaching db files on a NAS, but the Hotfix is included in SP2, which I am using.
I hope I have included enough info but if I've missed anything, it can be provided.
(Event Log basically replicates what the error message says.)
Any help or ideas would be greatly appreciated.
Thanks
Steve
View 2 Replies
View Related
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
Sep 19, 2002
Is this possible? If so, how is it done?
View 1 Replies
View Related
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
Mar 5, 2008
I feel that I have done something stupid here.I used SSMSE to detach my database so that I could create a copy - no problem here.But then in SSMSE I right-clicked Databases, chose attach and expected to see my original database. But there are no databases shown in the attach window!I have tried to find the original database in SSMSE but nothing I do allows me to see it. However, Windows Explorer shows me that the files are still where they were when I detached them.Please help - this is driving me crazy.ThanksChris
View 2 Replies
View Related
Nov 1, 2006
Hello,
I currently have an application that uses a Access/JET database for a desktop application. The application is single-user and outside of a few corrupt MDBs occasionally, the setup is working fine.
I am thinking about switching out from using the Access database to using a SQL Server Express detached database. I'm not sure if this is a "proper" usage of a detached database, but I've verified that I can access and query a detached one...
Is this something that is recommended? Are there any performance issues associated with it (I believe the detached would be faster than the JET database.) What kind of maintainence would be needed for the SQL Server file? (I currently have a "Repair and Compact" option for the Access file...anything along those lines needed?) Is there anything else I should be aware of?
Thank in advance!
View 4 Replies
View Related
Nov 10, 2015
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 Replies
View Related
Oct 1, 2007
Dear All,
i've detached my database. how to attache thesame again?
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 10 Replies
View Related
Nov 11, 2007
Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
View 5 Replies
View Related
Nov 29, 2007
I ran into a problem today where I had to pull a backup file from an older tape. The file was originally is a backup that was stored in a device like :
EXEC sp_addumpdevice
'disk', --type
'c2000_BackupDevice', --logical name
'\UNCPathackupFile.bak' --physical location
I put the file from the tape on a File Server and then tried to create a new backup device pointing to the file. That's easy enough, but when I try to restore from it I get an device error saying it's offline.
How can I get that device attached to my server and then extract the backup files stored inside it?
Please advise b/c this is an interesting problem I hadn't anticipated and it argues that I should change my backup strategy .
Thanks for any advice/experience you can provide,
alex8675
View 2 Replies
View Related
Nov 12, 2015
I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.
View 21 Replies
View Related
May 6, 2015
I have a requirement to
a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
View 9 Replies
View Related
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
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
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
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
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
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
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
Sep 20, 2002
How do you load existing DTS Stored Structured files on to a new SQL 2000 Server?
View 1 Replies
View Related
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
May 20, 2008
after moving the resource database files with this command:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)
(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions??????????????????????
Also.. I checked and the primary file is NOT READ-ONLY.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
View 1 Replies
View Related
Feb 11, 2014
Trying to find out if this is the best way to move log files in databases that are in an availability group.
remove the DB from the AG
Run alter database commands like you would normally to take offline ,move file,bring online ,etc
drop the db from secondary node
then rejoin the DB to the AG
Is that the only option for moving them when its in an avail group? cant find any other info on moving files in mirrors or HA groups
View 2 Replies
View Related
Nov 15, 2007
I have a number of XLS reports in template form. I want to move these to a new location on the File Server and after they have been populated move them to another location on the File Server.
I have seen some proposed solutions but I haven€™t found any that work. This should not be difficult and I envisage using a File System Task and a Foreach Loop Container. However passing the multiple file names to the File System Task errors repeatedly.
Any help would be greatly appreciated!!
Paul Boynton
View 13 Replies
View Related
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
Jul 23, 2005
Hello, I have been having a bit of trouble finding help on the safestway to move data files to a different disk on the same server. Mosthelp is about moving data files to a different sqlserver. I just wantto move the files to a different drive on the same server. Any helpwould be appreciated.Thanks,David
View 6 Replies
View Related
Oct 23, 2015
Have a SQL2008R2 instance on a VM where the single .mdf for the tempDb database is located on a high contention disk. Â I've managed to get another 60GB disk and thought it would be a good time to move the .mdf and also increase it's size and number of files.Â
The server has 12 cores and after a bit of reading I've decided that it would be best just to have four files for this database as the 1 file per core (-1) seems to be disputed. Â
-- Move the existing file to the new disk and rename it.
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:SQLData empdb0.mdf');
-- Change the size to 1GB
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE= 1048576KB, FILEGROWTH=5%);
-- Add three new files, all with the same size & growth
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'E:SQLData empdb1.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'E:SQLData empdb2.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'E:SQLData empdb3.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
-- Now restart the instance.
Â
Also, what are peoples thoughts on percentage growth for tempDb? Â I've read that it's not recommend and yet it seems to be the norm.
View 4 Replies
View Related
Apr 9, 2008
Hey,
What I am trying to achieve currently with SSIS is to view the content in one folder for example 'New' and if there is files in this then move it to 'Archived'.
Any nice liinks or tutorial or general advice you guys could give me ?
View 13 Replies
View Related
Aug 4, 2015
I have a documented API and an XSD for that API data.
I want to create an SQL DB from that XSD file and validate against it. Â There used to be tools and tutorials for this in Visual Studio 2010 but 2013 is proving to be difficult.
What is the best way to move from the XSD into an SQL DB? Â All manual coding?
View 6 Replies
View Related
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
