Transferring MS-Sql Database Between Two Servers
Dec 1, 2006
hello friends,
I need to transfer my database from one server to database on another server every 24 hours. I can create windows application but it will be cumbersome to write bulk of code . So can u suggest me some service or any other way through query or stored procedure by job scheduling which can run every 24 hours and move my data from one database on one server to another sql database. Both databases are sql server 2000 but servers are diffeerent so how to connect them while transferring dbs. ? plz help me , its urgent.
regards,
max
View 3 Replies
ADVERTISEMENT
Dec 1, 2006
hello friends,
I need to transfer my database from one server to database on another server every 24 hours. I can create windows application but it will be cumbersome to write bulk of code . So can u suggest me some service or any other way through query or stored procedure or by job scheduling which can run every 24 hours and move my data from one database on one server to another sql database. Both database systems are sql server 2000 but servers are diffeerent so how to connect them while transferring dbs. ? Any help is appreciated.
regards,
max
View 1 Replies
View Related
Feb 15, 2002
I need some advice on copying databases, stored procedures, views, logins ..etc from a SQL Serevr 7.0 server, to a new SQL Server 2000 server.
Is it better to backup the databases on one server and then restore them to the other. Or id it easier to use the Wizards to Import everything from new server, or to Export everything from the old server ?
Any advice would be appreciated. I need to ensure that I do not miss anything, in particular Stored Procedures in the old master database.
View 2 Replies
View Related
Jan 11, 2000
I tried using Import/Export to transfer a database from one SQL Server box to another using NT authentication.
The database appeared to transfer OK, however the process crashed with an error suggesting that a SQL Server login was not present.
How are the SQL Server logins transferred ?
Richard
View 2 Replies
View Related
Aug 27, 1999
Hi!
I have weekly full db backups, daily differential log backups and transaction log backups every 15 minutes on the prod. db server. What is the fastest way to make the staging server have the current version of the db. in the production server? Do I always have to take a full db. backup on the prod. server, zip the file, copy the file to staging, unzip the file and then restore it on staging? Is is possible to use the existing combination of full, diff and trans. log backups to make the restoration process faster?
Thank you
Praveena
View 4 Replies
View Related
Feb 2, 2006
Transferring objects between sql servers having db created using different usernames :
We have a local sql2000 db created using username abc, and another SQL2000db at a remote location where we have hosted our database. In our remotelocation the db username is ourdomain. How do we transfer
all objects from local db created using username abc to remote db created using ourdomain.? Is it possible to exclude usernames while transferring objects between sql servers?? Please help
K006b
View 2 Replies
View Related
May 14, 2006
I need to append data from a database on one server to a table in a databaseon a different server. Both servers are running SQL 7. How can that be done?Thanks.
View 2 Replies
View Related
May 12, 2005
I have 2 tables with the following structure:
CREATE TABLE [dbo].[table1] (
[RID] [int] IDENTITY (1, 1) NOT NULL ,
[RText] [varchar] (400) NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2] (
[GrpRID] [int] IDENTITY (1, 1) NOT NULL ,
[GrpID] [varchar] (10) NOT NULL ,
[RID] [int] NOT NULL ,
[Status] [bit] NULL ,
[SortOrder] [int] NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
GO
I am transfering 2 table between 2 SQL server based on GrpRID from table2.
Since RID is identity in table1 sometimes it is different text for spesific
Rid in second server. Some how I need to get the match the right text from server1
to server2 and if text doesn't exists create a bew entry in table1 with the update to table2
wich should reflect correct RID.
View 1 Replies
View Related
Jan 2, 2001
My question is, why did I have to use the allegedly optional named argument @DefLanguage when using sp_AddLogin? I had often tried to use the @SID parameter of sp_AddLogin to synchronize users betwixt our servers. Previous efforts had failed.
Per Transact-SQL Help, here’s the syntax for AddLogin:
sp_addlogin [@loginame =] 'login'
[,[@passwd =] 'password']
[,[@defdb =] 'database']
[,[@deflanguage =] 'language']
[,[@sid =] 'sid']
[,[@encryptopt =] 'encryption_option']
We have over a dozen User DBs, and I’d written a cursor to add the login and then GRANT access to the appropriate DBs. I used the following variables:
DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)
Then I SET the variables:
SET @Loginame = 'MyUserName'
SET @Passwd = 'UsersPwd'
SET @DefDB = 'UsersDB'
SET @SID = 0x6370170BDAAFF640AD5CEB586EA87C2C
-- UserSID for MyUserName from sister server
Then I EXECuted the proc.
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @SID
This generated the following error (instead of ‘?’, I saw squares, as in unprintable characters):
Server: Msg 15033, Level 16, State 1, Line 0
'????????' is not a valid official language name.
I didn’t understand this at all at first, but out of desparation, I DECLAREd & SET the @DefLanguage argument...And it worked! That is:
DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)
DECLARE @DefLanguage SysName
Then added:
SET @DefLanguage = NULL
-- per sp_Configure, RunValue = 0
-- Master..SysLanguages reveals that language ID = 0 corresponds to ‘us_english’
Then I EXECuted:
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @DefLanguage , @SID
Success!
Again, my question is, why did I have to use the allegedly optional named argument @DefLanguage?
View 1 Replies
View Related
Sep 7, 2000
Hello All,
Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.
Many thanks in advance for any and all help,
Gurmi
View 1 Replies
View Related
Jul 23, 2005
Hi all,Here is my situation.I have an application that uses SQL 2000. The client has a programthat uses MSDE. The client MUST have the MSDE version of the Database.What I have done is to create a SQL 2000 Database that is exactly likethe MSDE version. What I wish to do, is at regularly scheduledintervals, update the SQL 2000 Data with the newest MSDE Data.On my clients server, they have both MSDE and SQL 2000 installedthe msde has a user name and password to access it, whereas the SQL2000 uses authenticated mode to access it.What I'm trying to do is to write a bunch of SQL Queries, that I canrun that will copy over the new Data, but I'm having problems accessingthe msde database in query analyzer from the SQL DatabaseCan anyone give me hints on the best way to try thisThanksScott
View 1 Replies
View Related
Mar 23, 2001
Hi !
Currently I need to transfer existing database to another server. Unfortunately, after restoring all database file, user information is not available. It is stored in Master database.
I would like to know how can I transfer user login, database access, store procedures to another server ?
Please advise. Thanks !
View 3 Replies
View Related
Nov 23, 2006
Can anyone give me some ideas on how can i transfer data from access database to sql 2005. It is bit complicated any help will be highly appreciated.
View 1 Replies
View Related
Feb 14, 2008
I want to transfer ACCESS DataBase(ABC.mdb) to SQL Table Using c#.(SSIS Programming)
i need to use this query on access database
which will when run create 4 fileds
ABC.mdb
Sorce_Db_Id_Col
Attribute_Type
Attribute_Value
Query_Flag
And sorce database
Indentity_col
Att_typ
Att_value
Att_catg
I m very new in this stuff. so please please any one have any sample code for this then send me !
please i realy need it
My email id is ripal.parikh@softwaresolutionsindia.com
Thanks lot!!!!
View 3 Replies
View Related
Feb 5, 2008
Hi,Ive been building a project on a local server and now need to transfer the files and database over to the live server. I have managed to transfer the files quite easily, however I am having a few problems transferring the database using Microsoft SQL Server Management Studio. When I try importing data to the live database, all the data and tables are brought across however some of the values for the fields are lost. For example all the fields which had a primary key no longer have one, and all the set default values for the fields are also lost. Ive been considering just going through every table in the database and re-entering the correct values for the fields, but this seems a bit time consuming and I'm also concerned about possible errors which could arise as there will be an inconsistency.Another method I tried was copying the SQL query across which created all the tables and the correct values for the fields. However when I tried to import the data an error was produced as you cannot import data into a table which has read only fields etc..Does anyone know a solution?Thanks for your time.
View 2 Replies
View Related
Nov 13, 2000
Good morning one and all,
I need to transfer a database (contining one table) containing over 35 million records from one server to another. I have two options at present :
(a) Use DTS to do the transfer
(b) Copy the mdf file across and sp_attach_db it
Does any1 have a better idea, or does any1 know which of the two methods will be the quickest?
TIA
Gurmi
View 1 Replies
View Related
Jan 2, 2008
I have a SSIS package that transfers a couple of tables from one database to another database on the same server. It works fine in most of the machines we tested with. However on one of the customer machines, it consistently fails with the error message, The Return value was unknown. The process exit code was -1073741795. The step failed.
This package runs as a scheduled job on the sql agent. When i did a sql profiler to see what is going on, i noticed that the last step before the bulk insert, it gets the collation and the schema id of the 2 tables. My guess is, it compares these values from the source and destination databases and makes sure everything is ok before copying.
On a machine where this works, it goes ahead with the the next step which is the bulk insert itself. Whereas on the machines where it doesn't work, it stops right after this step. i.e. it does not even bother to call the 'bulk insert' api. Which makes me think it is doing some kind of validation with these values and it is getting something that is not expected.
If the collation or the schema was an issue, why throw an 'return value unknown' error? Can the error be more specific?
Any other possible reasons for such a behavior? Any clues?
Thanks
Gopal
View 2 Replies
View Related
Oct 16, 2006
I set up a basic Transfer Database task (online, copy) to copy a DB. It works great except for the fact that it isn't transferring the PK's and FK's. It also looks like it did not transfer the views. Any idea why? Anything else the Transfer Database task doesn't actually transfer?
View 2 Replies
View Related
Nov 6, 2005
coycoy wrote:you wanted to join some columns coming from two different tables...right? if that so, use an SQL query. Try using the "inner join" statement.
i wanted to combine the data from two tables. these two tables belong to different servers: table1 is from sql server found in cerebrum station and table2 is from mysql server found in copernicus station. i have this code but the problem is i can only use this code if the two tables belong in the same database server.
</P>
<P>string limitReghrsvalue = "select statuslog.ActId as STATUS_ID,DalsDataNew.ID as MANHOUR_ID,statuslog.ActDate as DATE,statuslog.PrjCode as PROJECT_CODE,statuslog.MapNumber as MAP_NUMBER,statuslog.Activity_Code as ACTIVITY_CODE,DalsDataNew.ActivityMedium as MEDIUM_CODE,statuslog.RegHrs AS REGHOURS,statuslog.OTHrs AS OTHOURS,statuslog.Status AS STATUS,DalsDataNew.Flag,DalsDataNew.Approvedby from statuslog,DalsDataNew where statuslog.PrjCode = DalsDataNew.ProjectCode and statuslog.PIN = DalsDataNew.PIN and statuslog.ActDate = DalsDataNew.Date and statuslog.Activity_Code = DalsDataNew.ActivityCode and statuslog.RegHrs = DalsDataNew.RegHours and statuslog.OTHrs = DalsDataNew.OTHours and statuslog.PIN = 'P120' and statuslog.ActDate >= '"+this.firstdate.Text+"' and statuslog.ActDate <= '"+this.lastdate.Text+"'";</P>
<P>
Sql Server in Cerebrum: database dals
DalsData
ID | Date | PIN | ProjectCode | ActivityCode | ActivityMedium | RegHrs | OTHrs | Approvedby | Flag
123 |9/17/2005| P120| 1234 | B | W(P) | 5.50 | 0.00 | P083 | 1
124 |9/17/2005| P120| 1234 | I | W(PC) | 1.50 | 2.25 | |
MySqlServer in Copernicus: database stat
Statuslog
ActID | ActDate | PIN | ProjectCode | MapNumber | ActivityCode | RegHrs | OTHrs | Status(%)
1 | 2005-9-17 | P120 | 1234 | map01 | B | 5.50 | 0.00 | 100
2 | 2005-9-17 | P120 | 1234 | map01 | I | 1.50 | 2.25 | 75
the output in datagrid should be:
ID | ActID | Date | ProjectCode | ActivityCode | MediumCode | MapNumber | RegHrs | OTHrs | Status | Approvedby | Flag
123| 1 |9/17/2005| 1234 | B | W(P) | map01 | 5.50 | 0.00 | 100 | P083 | 1
124| 2 |9/17/2005| 1234 | I | W(PC) | map01 | 1.50 | 2.25 | 75 | | could someone help me how would i do this?
View 5 Replies
View Related
Feb 16, 2004
Dear All,
I am trying to create a trigger on table A existing in Database A on Server A which on insert,update,delete would reflect these changes on table B in DatabaseB on Server B. I wrote the following syntax but it doesnt work
CREATE trigger trg_upd_tableA
On dbo.tableA
for UPDATE
as
--update the fields
declare @cust_no varchar
SELECT @cust_no = cust_no FROM inserted
update ServerB.DatabaseB.dbo.tableB
set
entity_type_cd = 'MFG',
cust_no = 6699,
name_en = 'NOVARTIS',
status ='A',
create_date=getdate()
,modify_date=getdate()
where cust_no= @cust_no
I get the error below
Another user has modified the contents of this table or view ; the database row you are modifying no longer exists in the database
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server] MSDTC on server 'DatabaseB' is unavailable'
I would like anyone to reply to me as soon as possible please because i need the solution urgently
Thanks alot in advance,
Noha
View 2 Replies
View Related
Apr 14, 2004
How to backup database on the other servers in the LANS.
View 8 Replies
View Related
Apr 10, 2008
I am attempting to move a User Database from the Production Server to a Training Server. What is the best/most simplistic way for me to accomplish this task and place this copying action on a schedule of say "every saturday morning @ 7am"
I tried the "Copy Database Wizard" within Enterprise Manager and it successfully copies the database, however when I try to schedule it to happen at a different time...it does not copy the database. It seems to only work when I tell it to perform this action now.
Please help.
Thanks,
JC
View 11 Replies
View Related
May 8, 2008
Hello All,
I need some help with a web application we are running. Is it possible to have 2 seperate instances of SQL 2005 running and each one pointing to the same DB. We want to load balance our users on the different DB servers. They are performing read and write transactions.
Any suggestions.
View 3 Replies
View Related
Dec 9, 2007
Hi,
I've got two servers with sql 2005 express on, the first is a shared server at our isp, we rent one database from them. The other server is our own and we have full sa access.
Is there an easy way to copy the database from the shared server onto our own server. We're unable to use the backup.
Thanks
View 3 Replies
View Related
Sep 18, 2007
Hi,
I read the following article related to "Federated Database Servers" But i am not getting what is this "Federated Database Servers", May be the language of this article i think little bit difficult. So, can anyone please explain me this.
Wht are thse servers and when, why and where we use these servers?????
http://msdn2.microsoft.com/en-us/library/aa213244(SQL.80).aspx
Thanks,
View 2 Replies
View Related
Jul 31, 2007
Hello. I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server. I'm trying to use a SELECT INTO statement. Any idea how to do this?I've tried SELECT * INTO DestinationServer.dbo.DestinationDB.DestinationTableFROM SourceTable AS SourceTable_1 But this doesn't work, saying there are too many prefixes. Any idea how to do this?
View 5 Replies
View Related
Oct 16, 2015
I have two production servers with two different databases and I was thinking about using Linked Servers, but never did this before.Found this stored procedure
sp_addlinkedserver('servername').Would you just execute this and then run your query after the SP?
View 8 Replies
View Related
Apr 4, 2014
I would like to know if it is possible to split a database across different servers, in the same manner you can split it over multiple drives on the same server We are trying to balance the load cause we are finding that the current server can't handle the load
View 4 Replies
View Related
May 28, 2014
I want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.
View 9 Replies
View Related
Mar 26, 2008
Can someone recommend a utility (preferably open-source) tosynchronize changes across servers? I need to bring only data over insome cases, and only objects in other cases. Any ideas?Thx!
View 5 Replies
View Related
Feb 10, 2008
I have a SSIS package which is used to update the OLAP database daily. It includes various processes from the initial dropping of the raw data source tables, rebuilding it, dropping of the OLAP datamart tables (dimension tables and fact tables), re-building it, updating the OLAP dimensions and finally updating the OLAP cubes. I wrote a series of the Sql scripts to perform the tasks except the updating of the OLAP dimensions and cubes, and assigned them to the SSIS package. Everything is working great and it was scheduled with the sql agent to execute at the early hour everyday. We have a single server enviroment for the "Production/Processing" i.e. Database engine, SSIS, SSAS, SSRS are all installed on a single server. By the way we are using Sql 2005.
Recently we split the "Production/Processing" enviroment into two seperated servers. We purchase another server "Production" server and use the existing server as the "Processing" server. I want to copy the success updated OLAP database from the current server "Processing" server to the new "Production" server. How to copy an OLAP database between servers?
Idealy, I believe that the following 5 processes should be assigned after the completion of the OLAP cubes updated at the "Processing" server:
Stop the SSAS at the "Production" server.........I know how to do this in SSIS
Re-name the existing OLAP database at the "Production" server.......not sure how to do this
Copy the newly updated OLAP database from "Processing" server to the "Production" server. If the copy process is succeed, drop the OLAP database that has just been re-named at the "Production" server. If the copy process is failed, re-named the OLAP database that has just been re-named back to the original name.....not sure how to do this. Should it be a Sql script?
Start the SSAS at the "Production" server........I know how to do this in SSIS
Thanks
View 1 Replies
View Related
Sep 22, 2007
Hello,
I am working on an ASP.NET apllication which will work with sensitve data that needs stored as encrypted. The ASP.NET application and SQL Server 2005 will be hosted outside of our company.
My questions are:
When I create a database master key, a certificate and a symetric key to encrypt and decrypt my data, will I be able to use these (master key, cert and symetric key) in other databases (say my local development SQL Server 2005 instance)?
Is a managed hosting a better solution to have total control over the the keys in the database?
Is there a white paper or guidance document for security in hosted databases?Thanks
-Al
View 3 Replies
View Related
