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


ADVERTISEMENT

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

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 SQL Server 2005 Databases

May 19, 2007

What is the easiest way to move SQL Server 2005 databases from one oldserver to a new server?

View 1 Replies View Related

Moving Sql Server 2005 DataBases From C: To D:

Dec 7, 2007



How do you move databases from C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
to d:MSSQL.1MSSQL?

I want to copy them, then detach/retach at the new location

Thanks

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

Moving And Restoring Connections W/ SQL 2005 Databases

Dec 27, 2007



I have a question regarding moving databases within the same server but to a different drive. Using the 'Alter Database' query transaction procedures within the SQL Server Management Studio, I'm able to take a user database offline and move it. However, when using the Alter transaction to bring the database back online, I get errors. There was also an attempt that seemed to work, but the app that uses the database through ODBC can't see the database that was moved and errors out.
How can databases be moved and connections re-established within the same server?
Thanks...

View 1 Replies View Related

Error Connecting Database Problem After Moving Databases 2000 To 2005

Nov 9, 2007

I have moved my databases to 2000 to instance of 2005 on the same server.
Now i set databases offline in 2000 as i did upgrade use backup & recovery method.

& my connection string
Password=test;Persist Security Info=True;User ID=test;Initial Catalog=databasename;Data Source=ipaddress

i was using above connection string to connect when using 2000 databases form applications in the production machine

I get the following error when i am running my applications now after moving to 2005


An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

IS it because i have two server on one server or
What do i need to do in order to fix this....tried several thing by searching nothing worked out..

Let me know
thanks


View 9 Replies View Related

2005 Migration Do You Have To Migrate The 2000 System Databases?

Sep 20, 2007

Hi all

I was just wondering on if I have to migrate the systems databases from 2000 ie. Master , Model & MSDB ??




Thanks

View 4 Replies View Related

Moving Ms SQL Databases

May 5, 2005

I've created a web application with Visual Studio 2003. Uploading my application to the server is easy enough. I use the Copy Project utility from the Project selection on the menu bar.
I'm using a remote server-- and hosting account that supports ASP.NET and ms SQL. But now I want to copy my ms SQL database and can't find any way to copy my DB to the server.
What do the best way to to this?
PaulTucson, AZ

View 1 Replies View Related

Moving SQL 7 Databases

Apr 11, 2002

What is the best way to move the databases to a new hard drive connected to the system? We added a new larger hard drive and I would like to move just the databases to the new hard drive.
Thanks

View 1 Replies View Related

Moving Databases

Aug 4, 2000

Hi,

Iam trying to move the database from one server to the other server.I know it can be done in the following 3 ways i.e

Copy .mdf and .ldf files to the other server and attach them.
or
restore the databses from the backups
or
use data transformation services.

Now let me know which is the fastest way of doing this.

and also what are advantages and disadvantages of above methods if possible.

Any suggestion would be appreciated.

Thanks.

View 9 Replies View Related

Moving SQL Databases

Dec 15, 1998

I move SQL databases quite often from one server to another. What is the most efficient way to do this? I normally perform a log dump then a
database dump and then copy the backup DAT file over to the new server and then perform a restore. This is OK except that the User accounts
permissions usually get messed up in the process and I have to redo them. It's OK for a few user accounts but some databases I work with have
many user accounts. The version of MS SQL I use is 6.5 (sp4). Thanks in advance. JLS

View 1 Replies View Related

Moving All Databases

Jan 11, 2008

hi i am trying to move my databases from one drive to another..sometimes could be into different network. i know how to do this through management studio. but how easy it could be doing it from query analyzer or may be using batch file. if you have experience please drop some words.

View 7 Replies View Related

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

Moving Data Between Databases

Jan 5, 2006

I have a web app that has been regulated to a disconnected PC. It's runing IIS and 2.0 with sql server express, but no connectivity. I have changes that are made to some of the data in the db (data, bot schema). There is one particular table that I cannot overwrite, and must extract the data.
What methods are available to do this swap of data between databases? I was thinking of doing something like this:
Track last date that remote db was updated. Upload updated database into data directory, loop through records for all affected tables, any date that was past logged date then update the record if it exists or insert new record, and then loop through the remote db and delete this records that dont exist in the updated db.
This seems intensive and slow - especially as the tables get bigger but I can't think of another solution that can be done by a user using sometime of web interface.

View 1 Replies View Related

Moving Databases From One SQL 7 Server To Another

Apr 19, 2001

I'd like to hear the "best practices" for moving databases from one server to another with minimal impact (minimum downtime) to users. The database is about 1GB, but growing at an average rate of 5MB/day.

Only 2 databases on that source server are to be moved. There are other databases on that server that shouldn't be affected as much as possible.

Any good suggestions?

I'm sorry, but I'm a complete newbie to SQL Server although I'm very familiar with Exchange/NT stuff. Got thrown this responsibility lately :(

View 3 Replies View Related

Moving 29 Databases To Another Server

Aug 23, 2001

We are going to be moving over about 29 databases from one server to another
(space issues). The new database will then be renamed to the old server name.
The database sizes range from 100mb to 29gig.
I was planning on doing a dump/restore which will take over 3 hours to dump and
12-15 hours to restore.
Our NTAdmin suggested shutting down the SQL Servers and just copying over the
data/log files to the new box and then bringing up the new server.
Had anyone ever tried this?
What are the effects on files over 22gig?
Should I also copy over the model/msdb/master database files?
What other potential problems should I be aware of?
My fear is that I start copying them over, run into a problem half way through
and then
have to revert back to the dump/restore method.

Any help would be appreciated, we are scheduled to do the move this weekend.

Thanks again
Susan

View 3 Replies View Related

Moving Databases To Another Server

Oct 7, 1999

how can I easily move my whole SQL Server 7 installation to another different machine as the current one is going down for maintenance during one week?
Thanks.

View 2 Replies View Related

Moving SQL 6.5 Databases To New Server.

Feb 28, 2000

I was hoping to get some input on the best approach for the following situation:
I have been tasked with moving several SQL server databases from one SQL server to another to free up the hardware on the first one for another purpose. The SQL server is version 6.5 and has service pack 5 on it.

I have installed SQL server on the server where the data is going to end up with the same code page and sort order as the current server.

For my next step I was going to create devices for the databases on the new server, then add the databases to the devices. I was going to use the "Create database for Load" statement. Then I was going to load a transaction dump from each database on the old server into the new database on the new server. Is there anything else I will need to do? Will I have to do anything with the master database or is it sufficient to do what I have stated above?

Can anyone direct me to a good technet article or white paper on this process? I did find a short page in SQL books on line which helped a bit.

Thanks in advance,
CK.

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 Views To Other Databases

Sep 27, 2005

Hi

I have a few views i created in a database.I want them to be moved to another database with credentials like dbo.dsatabasename.tablename automatically adding up to the new views.
How can i do that

Vic

Vicky

View 3 Replies View Related

How Is This Documentations For Moving Databases.

Dec 26, 2007

Hi Experts, I need an advice on this documentation. What are the things i missed. Can you please help me.1.Make a full backup of all databases from server 1 (for our safety)

2.Copy all logins , Jobs , DTS packages, folders from server 1 to server 2
3.Copy any folders or files from the server 1 to the new server 2 that will be accessed by Jobs, DTS packages, stored procedures etc.
4.Change all databases to Single user mode so no one can access the server( Downtime)

5.Detach all databases from server1 ( There will be downtime)


6.Stop the SQL Server, SQL Agent Services on server 1
7.Attach all databases to server 2 since both servers are in the same Datacenter.


8.Connect to the server 2, using Enterprise Manager and make sure all the databases are displayed and that there is no suspect databases. Scan through the SQL Server error logs, SQL Agent error logs, and System and Application event logs, to make sure there are no errors. If we find any errors, fix them before you proceed any further.

9.Once we are happy with the new server’s server 2 state, shutdown the SQL Server, SQL Agent and MS Search services.


10. Rename the alias on server 1
11.Rename the alias on server 2 so all the applications will point towards it and reboot the server.


12.Start the SQL Server, SQL Agent and MS Search services on server 2.

13. After we have completed these steps, server 2 is ready to go.

View 5 Replies View Related

Using MSDE And Moving Databases

Jul 23, 2005

Hi all,I am pretty new to dB programming and a have a few questions which I willput in separate threads. I have only coded as a hobby and some limited workprojects. I have recently been asked if I could help implement a simple helpdesk for work. This has only been running for a few weeks and the data iscurrently being stored in excel (I know...I know!! :-)I have installed a copy of MSDE on my development machine and plan on doingthe same on my office 'server'....in fact it may well already be on there. Ialso have another testbed to act as the client.My first question (in no particular order) is how to move the sql db fromthe development machine to the server? I don't have any sql/db tools otherthan anything that is included with MSDE (which I don't think is much).I will be posting other Q's for any experts out there!Thanks in advance.Phil

View 2 Replies View Related

Disaster - Moving Databases

Jul 20, 2005

We've all data and log files located in SAN mount point. In case ofprimary server crash, is it possible to unmount the volumes andremount at secondary server? Do we have any problems with master DBmoving this way. Detach,attach or backup/restore are not our options.Thanks

View 1 Replies View Related

Detaching And Moving Databases

Nov 23, 2006

Not sure if this is the exact forum I need but here goes.

Having a bit of an issue 'moving' a database. I've only just installed SQL Server 2005 on a Development/Test box so I can learn and mess around with it without doing any serious damage to anything important.

One of the first things I've done is install the AdventureWorks database and, having installed it, decided I would move the files to their optimum locations. However having detached the database I can't seem to move or copy the .mdf or .ldf files - something still seems to have its tentacles around them - 'Access denied' error. Even rebooting the server doesn't work. So I've had to delete then re-attach the database. The question then is does Detach serve any useful purpose? Or am am doing something wrong? With SS2000 I could detach a database, relocate the physical files and re-attach in seconds. The same procedure with SS2005 has taken me an hour!

Regards,

Gordon F.

View 4 Replies View Related

Moving ReportServer Databases

Apr 17, 2007

Followed the steps in Books Online to move the Reporting Services databases from one server to another. After resetting all the keys and configurations, the site comes up. However, all my shared datasources have lost their connection strings, and have reverted to requesting login information. Is this how it is supposed to work? If not, how do I fix this easily (as I have about 25 data sources)?

View 4 Replies View Related

Help In Moving RS Databases To Another Machine

Jun 5, 2006

We plan on moving our sql 2000 db's to a sql server 2005 box this week.

What's the best way to point RS to the new database machine?

I'm not extremely familiar with Reporting Services and would like to know are there any "gotchas" in doing this?

Thanks

View 6 Replies View Related

Moving Databases From One Server To Another?

Aug 20, 2015

I have to periodically migrate a copy of database on a production web server down to a web server used by some developers.  The names of the databases are different, as are the underlying file names and locations. I use Management Studio to do this.  we're running 2008 R2.

I make a backup on the production server, then move the file to the developer server.  On that server, I take the db offline, then restore the file into the proper database.  Before I start the restore I change the files to match the default names for the target database.  Then I do the restore.

The main practical problem I have is that the security ends of breaking for some logins, but not for others, and I don't know why.  I use the same login names for users on the source and destination machines, but sometimes they don't come through cleanly, and I have to recreate them.  It's always the same users that break.  I can fix them, but I'd like to understand why some users come across while others don't.Also, what's the best way to move a db from one engine to another, with a name change along the way?  And how do you do it if you have different physical environments and different paths for the actual underlying files between the two systems?

View 2 Replies View Related

Moving System Database Files

Sep 19, 2002

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

View 1 Replies View Related

Moving OLAP Cubes / Databases

Oct 9, 1999

How can I move OLAP Databases / specific cubes or virtual cubes from our Development Server to the UAT or LIVE servers ?

Thanks,

Lee

View 1 Replies View Related

Moving Multiple Databases Of 500GB

May 3, 2006

Hi

I have to move 5 Databases from production server to another server. I know it can be done by Backup and Restore and then make sure that all User names, Passwords and roles are not changed.

Some one can please help me out in giving a step by step process.

Thanks.
Bob

View 1 Replies View Related







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