Moving System Database Files

Sep 19, 2002

Is this possible? If so, how is it done?

View 1 Replies


ADVERTISEMENT

Moving System Database

Jan 26, 2005

What is the correct order:

Should it be:

Master
Model
MSDB
Tempdb

I understand that the model should be before the MSDB database but as for master and tempdb I don't know the order.

Thanks

Lystra

View 10 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

Moving Database Server From WIN2k To WIN2003Advanced 64 Bit Including System DB's

Dec 12, 2007

Hi

Today I have task to move all UDB's including matser,msdb form Windows 2000 server to Windows 2003 Advacned Server with 64bit(8 Hours Down time)


Present Plan

1.Install same Version of SQL Server in Destination Server
2.Take all Databases Backup
2.Move DTS,JOB and Scripts with same location
3.Stop both servers SQL Server's
4.Deattch all Databses and Move to Destination Server
5.Place all Databases(Same Loaction i.e Soruce Server master DB is C:<folders>.. Place in Destination Server C:<folder>..)
6.Start the Databse Servers(Destination)
7.Port the Apllication and Test.

Note:for our project we are using windows users,so that no problem with Orphan users.

Please some body Advice me, or suggest me
..it is ok

View 6 Replies View Related

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

SQL Security :: Server Won't Start After Mistyped System Database Names When Moving Them To New Location

Apr 24, 2015

I'm using SQL Server 2012 and was attempting to move the msdb, model, and tempdb databases to a new location and accidentally gave their log files an mdf extension instead of ldf when providing the new pathfilename.  After the server wouldn't start I checked my script and noticed my error.  I have good backups of my system databases, so I was hoping to start the MSSQLSERVER service in single-user mode (using the -m startup parameter) and then just restore master using sqlcmd. 

Unfortunately the service was starting but I couldn't connect via sqlcmd using any of the three protocols (it said the server was not found or not accessible each time).  I also tried using the dedicated Admin connection but I got the same error.  Then I went into the Templates folder and copied the master, msdb, model, and tempdb templates into the DATA folder and tried to restart SQL Server but still no luck (now the MSSQLSERVER service won't start at all).  Is there an easy way to fix this mess without having to reinstall from the setup application?

View 8 Replies View Related

DB Engine :: System Database Files Location

May 16, 2015

I have C,D,E drives on server. Data files will be on D and Log on E. My question is what is best practice for data and log files for system databases during sql server installation selection? Should they be on C drive along with SQL Server installation or D & E? If they should not be on C then what is the reason and what is benefit to move them on other drives.

View 9 Replies View Related

Moving Data From Old To New System

May 21, 2008

I've been given the task of moving data from an old system to a new one. That's easy enough apart from some of the data fields do not match in the least:

Example: In the old system (tableOld) there is a field that contains text data such as 'Laboratory','Field','Market','Demo','Development', but this one field could contain one or more of these. In the new system (tableNew) there are boolean fields for each of the above.

What I need is a way to check for these and then make sure the correct field(s) gets ticked (1 entered into the field) in the new system.

Any suggestions, just trying to get my head around SSIS.

Thanks for any help provided.

View 2 Replies View Related

SQL 2012 :: System Databases Moving

Jan 11, 2014

whenever we are moving system databases from one drive to another, do we need to move the path physically? or it automatically moves.I want to try the rebuild the sql server. So I want to corrupt the master database so, I deleted the master mdf file and restore it back but instead of database corruption it is giving the following error message. How can I corrupt the master database and can practice the rebuild the server.

And one more thing when I try to use the repair option I couldn't able to get all the things back to normal. The database engine service, replication is not working but SSAS, SSRS things are showing successfully repaired.

2014-01-10 21:34:26.44 Server Error: 26055, Severity: 16, State: 1.
2014-01-10 21:34:26.44 Server The SQL Server failed to initialize VIA support library [QLVipl.dll]. This normally indicates the VIA support library does not exist or is corrupted. Please repair or disable the VIA network protocol. Error: 0x7e.
2014-01-10 21:34:26.44 Server Error: 17182, Severity: 16, State: 1.
2014-01-10 21:34:26.44 Server TDSSNIClient initialization failed with error 0x7e, status code 0x60. Reason: Unable to initialize the VIA listener. The specified module could not be found.

[code]....

View 9 Replies View Related

Moving System Databases SQL 2005

Mar 16, 2007

I am in the process of moving my system databases to another volume. I have accommplished the first section pertaining to master database.

I have reached step 3 in the Resource database move section down below.

It states to change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');

I changed the query to point ot the new location of the master, (E:MSSQLData)

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= E:MSSQLDatamssqlsystemresource.mdf');


I get the following error when I run the query:

Could not locate entry in sysdatabases for database mssqlsystemresource. No entry found with that name. Make sure that the name is entered correctly.

What am I doing wrong? My syntax must be incorrect. But I can't figure it. Anybody done this before.

These are the steps per msdn.
****************************************************************************************************************
To move the master database, follow these steps.
1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server 2005 Services node, right-click the SQL Server (MSSQLSERVER) service and choose Properties.
3. In the SQL Server (MSSQLSERVER) Properties dialog box, click the Advanced tab.
4. Edit the startup parameters values to point to the planned location for the master database data and log files and click Apply. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-dC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
If the planned relocation for the master data file is E:SQLData and the planned relocation for the log file is F:SQLLog, the parameter values would be changed as follows:
-dE:SQLDatamaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG;-lF:SQLLogmastlog.ldf
5. Stop the MSSQLSERVER service.
6. Physically move the files to the new location.
7. Restart the MSSQLSERVER service.
8. Verify the file change.
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
********************************************************************************************

To move the Resource database, follow these steps.

1. Stop the MSSQLSERVER service if it is started.
2. Start the service in minimal mode. To do this, at the command prompt, enter <SQLPath>innsqlservr -c -f -T3608 where <SQLPath> is the path for the instance of ssNoVersion. For example, C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL. This will start the instance of ssNoVersion for master-only recovery.
3. Run these queries. Change the FILENAME path to match the new location of the master database. Do not change the name of the database or the file names.

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
4. Make sure the Resource database is set to read-only by running this query:
ALTER DATABASE mssqlsystemresource SET READ_ONLY;
5. Physically move the files to the new location.
6. Restart the MSSQLSERVER service.

View 6 Replies View Related

2005 Instance Is Dead After Moving System DBs

May 19, 2008

I think one of my system database files is corrupted. I did an alter database... to move my Model and MSDB and now I can only start the instance with /f /t3608

I can connect to the instance but I can't do anything I get an error that one of the database files does not match it's primary file.. I don't know if it's MSDB or MODEL but I pretty much can't do anything.




I made a backup of model and msdb before I started.. how do I go about restoring them?

View 3 Replies View Related

Error Restarting MSSQL After Moving System DB's

Dec 8, 2007

Hello,

I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005

I had moved the master db under the minimal configuration. using the parameters /f and /T3608

What can I do to restart the SQL

Thanks

View 1 Replies View Related

Error Restarting MSSQL After Moving System DB's

Dec 8, 2007

Hello,

I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005

I had moved the master db under the minimal configuration. using the parameters /f and /T3608

What can I do to restart the SQL

Thanks

View 1 Replies View Related

Error Restarting MSSQL After Moving System DB's

Dec 8, 2007

Hello,

I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005

I had moved the master db under the minimal configuration. using the parameters /f and /T3608

What can I do to restart the SQL

Thanks

View 3 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

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

SQL Server 2005 Instance Is Dead After Moving System Databases!

May 20, 2008

Followed this article to move my model and msdb databes but I think I messed up. http://msdn.microsoft.com/en-us/library/ms345408.aspx
The instance will not start because it says one of the files does not match it's primary file (not sure if it's model or msdb)

I can bring the instance up in single user mode (NET START mssqlserver /f /t3608) but when I try to do a query to re-do my alter statements, it says i can't attach because only one seesion is allowed. in 2000 i used to be able to start query analyzer without starting enterprise manager..

When i try and start with a minimal config (sqlservr -c -m) it won't start. I get an error saying that one of my files does not match it's primary file (either model or msbd don't know which) any ideas????????

View 10 Replies View Related

Moving DTS Stored Structured Files

Sep 20, 2002

How do you load existing DTS Stored Structured files on to a new SQL 2000 Server?

View 1 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

Instance Will Not Come Up After Moving Resource Db Files

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

SQL 2012 :: Moving Log Files In HA Groups

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

SSIS Moving Multiple Files

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

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

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

TempDb - Moving / Adding Files And Resizing

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

New SSIS, Info About Working With Directories Moving Files Etc

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

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







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