Moving Replicated Databases To A Replacement SQL Server
May 23, 2006
Any 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
View 1 Replies
ADVERTISEMENT
Feb 27, 2008
I have a small client installable web application which used to work great on 32 bit machines. Well now clients are starting to use x64 Windows 2003 servers and things have begun to unravel. W2K3 x64 does not have any of the JET database drivers needed to communicate to the Access databases and it doesn't look like they're coming anytime soon.
Is SQL Server Express the new replacement for Access databases on x64 machines. Does Microsoft intend on moving some of the JET drivers over from 32 bit servers. I know that you can run IIS in 32 bit mode and everything is fine, but I can't ask everyone to do that.
Is SQL Server Express something that I can expect people to have installed, it sure doesn't sounds like it. After reading some posts, it sounds like it's a bit of a hastle to install.
Thanks in advance for your time.
-Mark
View 3 Replies
View Related
Apr 6, 2004
Is there a way to move a SQL replicated database from one server to another without dropping the publication?
I have SQLCE setup with SQL server 2000; I want to redo the server but needs to move my replication to a temp server and then back.
The problem is that I do not want to drop my subscription and recreate it…it needs to stay the same.
View 2 Replies
View Related
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
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
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
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
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
Apr 12, 2005
Hi,
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
View 1 Replies
View Related
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
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
Apr 10, 2008
Hi!
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?
View 5 Replies
View Related
Jan 17, 2005
I am planning on upgrading one of my SQL 7 servers to SQL 2000. This server is currently and will continue to replicate with a SQL 7 server.
I want to view our current replication jobs & schedules in plan text so I can check when I recreate the replication that I haven't missed anything. Does anyone know if this is possible?
Jon
View 4 Replies
View Related
Nov 10, 2007
Hello All
I hope somebody can help. This is the second time I've seen this issue. The first time I thought it was a one off, but now I'm getting concerned.
I have two clients - one had Merge replication set up on a database that had a log log file that just grew and grew. The database was set to SIMPLE recovery. I could understand the log file growing if it was set up for transactional replication, but it wasn't transactional, just merge. Looking in sys.databasese the log_reuse column was set to REPLICATION.
I ran the sp_repldone stored proc on this database and all was well - I could shrink the log file down to a reasonable size. This server was SQL Server 2005, Service Pack 2.
Now I have yet another client who has a growing transaction log file. Low and behold the log_reuse for the database is set to REPLICATION again. This server has never had any type of replication set up at all!!!! The log file is still growing and DBCC OPENTRAN indicates undistributed transcations. This particular server is SQL Server 2005 Service Pack 1.
Can someone PLEASE shed some light on what's going on here? Is there some kind of bug that I don't know about? Searching Goolgle Groups seems to indicate that other people have had this problem, and they have have to set up replication and run sp_repldone to resolve it, but that's surely uncalled for on a database that has never seen replication?
Thanks
SQLGal2
View 2 Replies
View Related
Apr 21, 2004
Hello -
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.
View 2 Replies
View Related
Aug 9, 2015
I'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?
View 6 Replies
View Related
Feb 23, 2015
I 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 Related
Aug 28, 2015
I am in process to migrate some replicated SQL 2008 databases to SQL 2012. I haven't migrated the replicated/mirrored databases yet however I have migrated some clean databases just having SSIS. This time I am tasked to migrate replicated one. providing  the detail steps to follow to migrate such databases?Â
View 3 Replies
View Related
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
May 5, 2006
My 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 ?
View 1 Replies
View Related
Apr 19, 2001
what is the easiest way to move a database on one SQL server to another SQL server?
View 2 Replies
View Related
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
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
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
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
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
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
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 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
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
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
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
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
