Moving A SQL Server 2000 Database From One Server To Another Advice
May 23, 2006
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
I've got a situation where I need to regularly (maybe each month), detach a DB, copy its files from their highspeed SAN location to a slower NAS, then re-attach it and make it available on-line. We're doing this for our DB's as they age to > 3 years.
Just wondering if any of you have scripts you can point me to so I don't have to re-invent the wheel.
Also, after you re-attach, how do you verify the NAS DB is 100% ok before deleting the original from its SAN location?
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....
SQL Users/DBAs,I'm trying to move data files around for a database that is in standbymode. I can detach/attach the database fine usingSP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the logsequence is broken and I can't restore any more logs. Does anyoneknow if there is a way to move around data files and keep the databasein standby mode?Thanks,JB
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
It's arriving from ebuyer tomorrow for 32 quid. I already have aninstance of MSDE running on the laptop and ideally i would like SQLserver to be installed and use the current instance and not installany other additional services. [hope i got my terminology correcthere.] Basically all i would like is to use the Enterprise Managerwith my existing MSDE database. I was wondering if there is anything ishould be doing before I install. Should i shut down the service orleave it running for instance.Thanks for any tips,AndyB
I am moving my SQL 2000 Server from a Small Business Server to a full version of Windows 2000 Server and SQL 2000 Server. I have had no experience doing this but have backed up and restored my production databases for practice in case a of disaster situation. Is there anything special I need to do to move everything, including the system databases to a new server and not loose any of the user logons, etc.?
Hi there everyone, I have written a database system which tracks the performance of working in a shipping company in access. Im now rewriting the system in sqlserver and the only real problem I have found so far is that its difficult to estimate what kind of a server *cpu* & *ram* would be appropraite. The system currently performs transaction on my desktop machine in a second a quickest and 2 at slowest. There are going to be about 500 users in 3 time zones so there will only really be about 300 max hitting the system in an hour. I was looking at a Dell Poweredge server with twin P3's and half a Gb or ram would this be a good place to start from?
I have developed an Access 97 database that I would like to distribute to anumber of staff, but they do not have Access. At the same time I amconsidering upgrading to SBS 2003 premium edition which comes with SQLServer 2000.1. If we were to upgrade would it be a very difficult job to recreate thedatabase in SQL Server 2000, as in does it have wizards, macros etc.?2. Would the staff be able to use and share the new database from the serveror would they also require additional software on their desktops?3. Is there any way of distributing the Access 97 database with some sort of"run-time" licence - it was not developed using a developers edition?This has been posted to both comp.databases.ms-access andcomp.databases.ms-sqlserverYour advice would be appreciated.RegardsJohn McC
We have a new failover cluster (Windows 2003 SP1, Microsoft SQL 2000 SP4) with each node of the cluster hosting 7 SQL Server instances in a 2-node active-active configuration connected to a SAN. We are planning to move some SQL Server Instances(from existing stand-alone servers) into this Cluster. Any insight into the process of moving SQL Servers into the cluster would be highly appreciated.
My old database server runs SQL 2000 and has the CRM 3.0 app and database, our corporate web site (SharePoint Services 3.0) database, and Project Server 2007 database. The corporate site and project apps and web sites reside on our file server.
I want to move, upgrade or migrate the databases, corporate site and Project site to this new SQL 2005 server. I am a little unsure the best way to do this.
Should I move all the database first, then move the apps?
Should I do a restore of everything on the new server, and after I know it works just turn off the other server?
I have done a bit of searching around and cant find a clear answer to this question.
Current Setup Desktop application (c#) that connects to a SQL Server 2005 express database on the same local network as the application (currently 3 users)
It is only a very small company and has just taken on their first remote worker, but expects to take on another 6-8 over the next few months. They have asked for the database to be moved online.
The application was written in such a way that everything has been done using no stored procs, or views, it is all native SQL.
This will be my first DB hosted online and before I go ahead and do anything I just wanted to make sure what I have to do is correct, sorry if this is a very basic question, although I have been programming for a long time, I have never had the chance to do any online databases before.
Will this work. 1.Find a SQL Server 2005 Hosting company. 2.Move the database to the server. 3.Setup the users permissions. 3.Alter the connection string in the application to point to the new location.
So the only thing that would change would be a new connection string in the application preferences?
Or am I living in a dream world, because nothing is ever that simple.
One thing I am worried about is the security/visiblity of the database and data as it travels from the server to the client and back.
We have a new server (SRV63) and I followed the instructions at http://msdn2.microsoft.com/en-us/library/ms156421.aspx to move the report server database from our old server (SRV38). Everything appeared OK, however, when I went to initialize the instance in the Reporting Services Configuration tool, it shows an initialized instance (checkbox is checked) for SRV38 and and uninitialized instance for SRV63 (checkbox is not checked). Please note that I am not trying to create a scale-out deployment. I tried to initialize SRV63 but received the error below.
--------------------
ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server installation is not initialized. (rsReportServerNotActivated) at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo) at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.InitializeReportServer(String installationId)
------------------
This is the only thing stopping me from shutting down SRV38 and bringing SRV63 into production. Can someone assist me with this issue?
I'm a newbie on SSIS and am trying to grasp my way through this.
I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database.
What I want is to move the data to a staging table, then drop the main table and rename the staging table to the main table, to minimize the down-time of the data. I can't get the workflow to work, because the staging table has to exist when I run the package. I thought I could use an "Execute SQL" task to generate the table before I would run the task, but that doesn't work. Am I going about this the wrong way? Is there an optimal solution to this problem so my data can be accessible as much as possible.
Being a very novice SQL Server administrator, I need to ask the experts a question.
How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?
I missed the days of 6.5 when I can take a database from one server create a device for LOAD on another sever and use the LOAD database command to put that database on another server that has MS SQL SERVER on it..
So what is the trick with 7.0 if DTS is out of the question??
I am using reporting services 2000 with SQL server 2000. The report server is on my machine and report server database is on another sql server. I need to move report server database from server1 to server2.I have tried the method which i found on net i.e 1-Stop the reporting services 2-detach the report server database from server1 and attach it to server2 3-reconfigure reporting services using RSconfig wherein i specified the new server name , database name and authentication. 4-Start the reporting services 5-Restart IIS
After following all these steps when i browse the report server i get the following error :
"The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help "
I have a SQL Server 2005 Express database on my local machince called OpenAssess.mdf. The server we host with has a file extension of .mdb. How can I go about getting my database to the server? I tried changing the extension to mdb on the local machine but then it tried opening the database in MS Access and didn't work. I just need to connect to the database in my web pages. Here is my connection string and then the error which is visible at the botton of openassessment.org. *********************************************connection string************************************************* OPEN_Conn = "Provider=SQLOLEDB;Data Source=connectionToHostServer;Network Library=DBMSSOCN;Initial Catalog=OpenAssess.mdb;User ID=myuserid;Password=mypassword" *****************************************************error********************************************************* Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database requested in login 'OpenAssess.mdb'. Login fails.
i got few questions. i need to move a databases from old server to new server. old server has 25 user databases total datafile around 300GB. heres the questions.
1. what is the fastest way to move all the data to new server? and what's the best way to do it? (DTS? MDF copy over/attach? bak copy over/restore? other theres other way?)
2. i got alot of job and user need to transfer over. i know DTS can do the user.. how about the jobs?
3. do i need to move the master and msdb over too?
4. beside all 3 questions above... do i miss anything that need to be move too?
Hi, I have a database that resides on a server that`s become too old. It spans 2 devices that sit on 2 different drive letters - D: and E:. I bought a new server, installed and configured NT4.0 and SQL6.5. Initially I restored the database to the new server from a dump .It worked fine, however all the login information was lost. Then I tried partitioning the new server the same way as the old one and copying all the DAT files over. Thus I was hoping to retain the information contained in the MASTER.DAT, drop the database, repartition to create 1 physical drivewith 1 database device, and restore from a dump to it. After copying the DATs, though, SQL Server would not start. Help Please!
I am not a SQL person. I want to copy/move a SQL datbase from old server to a new server and eventualy bring the old server down. What is the procedure to do it. Thanks in advance.
Hi all. I know that since you know nothing about my database it will be a stretch to answer this - but does anyone have a boiler plate checklist for moving a database, dts packages, agent jobs, etc...from one server to another? Maybe something that just says I should at least do x,y,z.
Also if anyone out there has any hard won experience/advice about gotchas when doing this I would love to hear it. We have outgrown a server and are installing a new one this week that we need to move over to.
Sql Server 2000 is running on machine havivng two HDD. The HDD where SQL Server resides is reaching capacity. Only 3Gb is remaining. I want to move SQL Server database to other hard drive so that space problem would not occure. How to do it?
I have an existing database on a Windows XP Pro box running MS SQL Server Desktop Engine 8.00.
I want to move this database to another machine running Windows Server 2003 Small Business Server w/full SQL Server (new edition).
I can see the database, and the directory structures are almost identical on both machines. But when I simply copy the data over nothing works. Obviously to me I must somehow back up the original database and restore it somehow on the new machine.
Any tips on where I begin? I have done searches in the Microsoft Knowledgebase for articles on how to backup and restore from one type SQL to another with frustrating results. No clear directions.
We have a couple of MS SQL Server 2000/2005 databases with a bunch of..NET clients written in C#, but may want to replace the dbserver withpostgres instead. The clients will still run on Windows, hopefully withas few changes as possible.We don't have any stored procedures or triggers, so all we need to portare the tables/index definitions.What are the most common issues/problems people run into on the clientside? My guesses are stuff like- identity columns- transaction handling(autocommited vs. implicit)- date and datetime- general error handling and error codesAnything else?I've googled for migration guides and howto's, but without success.Pointers to such are appreciated.Boa
I have detached my database from the server and copied the mdf and ldf files to the new PC do I need to do anything but attach it to the MSDE? What about logins, roles, etc... does all this remain the same or do I need to delete the old and reenter them?
I have to copy (move) a database from one server to another . One of the way of doing it is to take backup at source server and restore it at destination server .
I am doing it with the following series of statements .
================ At Source Server ================
===================== At Destination Server =====================
--Copy file (DBName_Device.Dat) from Source backup folder to Destination Backup folder
USE master EXEC sp_addumpdevice 'disk', 'DBName_Device', 'Irhadvdb02D$mssql7ackupDBName3_Device.dat'
RESTORE DATABASE DBName FROM DBName_Device WITH RECOVERY, MOVE '<Data File Name without .mdf>' TO 'irhadvdb02D$mssql7data<New Data File Name>.mdf' , MOVE '<Log File name without .ldf>' TO 'irhadvdb02D$mssql7data<New Log File Name>.ldf'
Above step works fine for one of the test database which small in size.
But it fails for the large database having Data file (.mdf) as 2.5GB and log file as 1.5GB .It says " not enough space on network drive to restore database" . But fact is that I have 200 GB free space on the server .
Hi all. I know that since you know nothing about my database it willbe a stretch to answer this - but does anyone have a boiler platechecklist for moving a database, dts packages, agent jobs, etc...fromone server to another? Maybe something that just says I should atleast do x,y,z.Also if anyone out there has any hard won experience/advice aboutgotchas when doing this I would love to hear it. We have outgrown aserver and are installing a new one this week that we need to moveover to.Thanks!
I'm developing a website using the new authentication system from asp.net 2.0 and when I try to copy the database to my server and I try to add data to it, it gives me a read-only error. I've tried several ways, but I can't seem to find the solution. It works well in my computer but if I move it to the server it just doesn't work.
Is there anything I need to do? I have all the permissions needed in IIS.
