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.
I have a database with Broker_Enabled set via the following command:
ALTER DATABASE 'xxxx' SET ENABLE_BROKER
All works well, I made a backup and want to move to my development machine. When I run I still get an error saying I need to enable broker service again.
"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."
How can I move the database and get this setup? Where are the BrokerID's Stored?
Thanks,
~ Steve
I have a DB with TDE enabled on Enterprise edition sql server 2008r2. I am actually planning on moving the DB to sql server 2008r2 standard edition. How to accomplish it properly. I don't think SQL Server 2008r2 standard edition support TDE. I can only think of turning off the encryption, but is there anything else I should know?
View 3 Replies View RelatedWhen I try to create a subscription to my SQL Server Compact 3.5-database file, it gives an SqlCeException-message that says that the file is not enabled for replication. How do enable it?
My SQL Server Management Studio won´t connect to my compact server file right now, so that method is not an option for me right now.
SQL Server 2005 sp2 Standard Edition.
I have a bunch of databases to relocated. relocating them on the same server - I just want to split the data and log files onto their own Volumes. these DBs have Full Text Indexing enabled.
This Server is a brand new build and NOT in production yet
How is the best way to move these Dbs and how do locate and move the full text catalog for each?? or do I even have to worry about that?? Is back-restore (with move) better for this?
When I look in BOL.. here is the syntax for an attach
USE master;
GO
--Detach the AdventureWorks database
sp_detach_db AdventureWorks;
GO
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON
(FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data.mdf'),
(FILENAME = 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_log.ldf'),
(FILENAME = 'c:myFTCatalogsAdvWksFtCat')
FOR ATTACH;
GO
Do I have to move the Full text Catalog? If so, how do I find it's current location. If I do have to move it, where is the best place to locate the catalogs???
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 :(
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
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.
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.
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?
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
What is the easiest way to move SQL Server 2005 databases from one oldserver to a new server?
View 1 Replies View Related
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
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?
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?
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
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 RelatedFollowed 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????????
Currently we have a SQL 2008 transactional replication setup in which the Publisher is replicating to 6 different subscribers. We need to move the Publisher SQL instance from VM server A to VM server B, which has a different host name. The actual move is expected to take a few hours.
how to handle the replication piece correctly. Steps needed to properly shut down replication before moving the SQL instance, and then the correct way to start replication back up again.
Because the server name is changing, I am thinking that will we have to create a new publication & subscriptions ? And if so, does this mean we HAVE to apply a new snapshot?
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 ?
We have bulk copy option enabled for our DB and we really use it. Will it be possible to set up a snapshot replication over the Internet of particular tables to a remote server from which the data will be only retrieved and never changed? Also, is it necessary to have PKs in all tables for this one-way snapshot replication? (for transactional replication it is needed, as I know)
Thanks a lot!
Andy.
what is the easiest way to move a database on one SQL server to another SQL server?
View 2 Replies View RelatedI get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:
Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture
Is my only option to change the TRUNCATE to DELETE?
[URL]
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
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
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.
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
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 RelatedI 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.
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 ?
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
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.
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