Moving SQL Server 2005 Databases
May 19, 2007What is the easiest way to move SQL Server 2005 databases from one old
server to a new server?
ADVERTISEMENT
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
SQL Server 2005 Instance Is Dead After Moving System Databases!
May 20, 2008Followed 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????????
Moving System Databases SQL 2005
Mar 16, 2007I 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.
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...
Error Connecting Database Problem After Moving Databases 2000 To 2005
Nov 9, 2007I 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
Moving Databases From One SQL 7 Server To Another
Apr 19, 2001I'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 :(
Moving 29 Databases To Another Server
Aug 23, 2001We 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
Moving Databases To Another Server
Oct 7, 1999how 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.
Moving SQL 6.5 Databases To New Server.
Feb 28, 2000I 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.
Moving Databases From One Server To Another?
Aug 20, 2015I 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?
Moving Databases To Remote Web Server
Apr 12, 2005Hi,
I just got hosted on a site which supports .Net, and my forms seem to be coming up okay. I have a database residing on my computer that accompanies the pages I have written.
I was told that I need to export my database to their site, and I have an ip address to use. Can anyone tell me how I would go about with this export ?
Do I just add the site to my Enterprise Manager ?
Thank you for any input.
Mike
Moving Some User Databases To New Server
Apr 10, 2008Hi!
I have a SQL 2000 server with a lot of databases (about 100) and I would like to move a great deal of them (+/- 50) to a new SQL 2005 server. All the databases I would move has the same word in the database name. Let's say the all include "Customer" in the Database name.
It would be a huge job to detach and attach every db manually. Is there a method that can detach db's based on a query againts the master db with a select on the name of the db?
If so, I also need to attach the db's on the new server with a script after moving the files to a new location.
Any good ideas?
Moving Databases From One Server To Another With Replication Enabled.
Apr 21, 2004Hello -
I have 2 databases on a remote server on which Replication and Replication Monitor is enabled. Now I want to move this databases from different remove location.
How do I transfer bboth databases with replication and replication monitor enabled on it with all the user logins.
Please help me out.
Thanks.
SQL Server Admin 2014 :: Moving Databases From One PC To Another?
Aug 9, 2015I've got an old version of SQL Server 2008 R2 Developer Edition on an old PC which is failing. I've got a new PC and have put SQL Server 2014 Developer Edition onto it. Now before the old machine completely dies, I've gotten into SSMS on the old machine and did a backup of the databases I want to save. I've moved the .BAK files to where I could get to them from SSMS on the new machine. I've gotten into SSMS and tried to do a restore the database to my new machine. However I'm getting an error that does not make any sense to me.
The database I'm I've backed up is named JobSearch. When I backed it up, that was the only database I had selected. Like I said I copied the .BAK to the new machine. Got into SSMS, told it that I wanted to restore the JobSearch database, telling it where I wanted to put it, and it then immediately fails with a:
"Restore of database 'JobSearch' failed. System.Data.SqlClient.SqlError: Logical file 'VideoLibrary_Data' is not part of the database 'JobSearch'. Use RESTORE FILEISTONLY to list the logical file names."
Well of course VideoLibrary isn't "the logical file". But neither did I select VideoLibrary (which is a database I also want to move, but I'm doing one at a time). So what in heck is going on here? Why is it complaining about a database I haven't even selected to back up? Why, when I check everything on the old machine, it's backing up JobSearch, but on the new machine it sees VideoLibrary?
Moving Replicated Databases To A Replacement SQL Server
May 23, 2006Any advice that can be offered on this subject would be much appreciated.
We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?
We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.
Do these things need to happen in any particular order?
Does the timing of the renaming of the SQL Server matter?
At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?
Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?
Thanks,
Dick Campbell
SQL Server 2008 :: Moving Backups / Restoring Databases
Feb 23, 2015I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies View RelatedMoving My SQL 7.0 Databases To A New Server To Replace The Old Server
May 5, 2006My existing SQL server is an old box that can no longer cope with the load. I need to replace it with a brand new box.
I am looking for a comprehensive set of steps that I could follow to ensure that the transition to the new server goes well.
I'm looking to transfer all my user databases but I'm not so sure whether I have to backup and restore my system databases to the new server as well.
I want to name the new server the same as the old server since I will be taking the old server out of production but I'm not sure whether I should rename the server AFTER I move over all the database and how that would affect SQL. Or should I first rename the old server, remove it out of the domain, then name my new server with the same name as my old server, and then proceed with the SQL installation and the moving of the databases.
Also, I do have a maintenance plan, DTS packages and some jobs set up on my old server that I would also like to move to the new server.
Is there a web page that explains all the steps in granular detail ?
Moving Databases From One SQL Server To Another SQL Server
Apr 19, 2001what is the easiest way to move a database on one SQL server to another SQL server?
View 2 Replies View RelatedMoving Ms SQL Databases
May 5, 2005I'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
Moving SQL 7 Databases
Apr 11, 2002What 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
Moving Databases
Aug 4, 2000Hi,
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.
Moving SQL Databases
Dec 15, 1998I 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
Moving All Databases
Jan 11, 2008hi 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 RelatedMoving Data Between Databases
Jan 5, 2006I 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.
Moving Databases Files.
Feb 7, 2005I 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 ?
Moving Views To Other Databases
Sep 27, 2005Hi
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
How Is This Documentations For Moving Databases.
Dec 26, 2007Hi 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.
Using MSDE And Moving Databases
Jul 23, 2005Hi 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 RelatedDisaster - Moving Databases
Jul 20, 2005We'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 RelatedDetaching And Moving Databases
Nov 23, 2006Not 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.
Moving ReportServer Databases
Apr 17, 2007Followed 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 RelatedHelp In Moving RS Databases To Another Machine
Jun 5, 2006We 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