My company is considering purchasing MS SQL Server to run an
application on (SASIxp). I am mainly familiar with Oracle, so I was
wondering how long it would take to copy a database. Basically we have
database A and each night we want to replace database B with the
contents of A. How long would this take say if we had a 10GB database
or a 20GB database.
What would be the technique to do this nightly, the Copy Database
Wizard, Snapshot Replication, Attach & Detach...? We need to automate
this process, and the source database can be made unavailable when
this happens.
i'm using DTS to copy one database into a new database, copying over everything, i.e. stored procedures, views, etc.. not just the tables.. how long should it take? it's been stuck at 22% now for about 15 minutes?
I have a database A include five tables, and have more than 1,500,000 rows. There is a replica database of A. First of all, there is no data in the two dbs. When I finish inserting data into A, the replica db seems still work, the log file size still changes. How can I know the replication finished or not? How long it will take to finish replicate 1,500,000 rows of data?
Hi, I have some SSIS Deployment Manifest Files that needs to be copied over into another server. I am just using the Package installation wizard to do this. But it is taking to long to run.( I have 19 simple packages and it is taking 15 minutes).
Is there any way that I can use to achieve better performance in this?
My environment are Win Vista, Ms sql 2005 express, Ms Sql Server Management studio express. I have a database Mysearchdb.mdf and want to replicate Myseachdb.mdf to another database with exact same features. What is the best way to do it? I don't want to create each tables, functions and store procedures manually. Thanks
we have a server in our environment, we would like to make a copy of it on the other server in side the company and also do the same on the remote server.
Can u tell me how to do it or any sites where i can find the stratergy's which have been already implemented for review.
i want to replicate the data from one table into another table in the same database,all the transactions in table "a" should be replicated in table "b" and vice versa. Note:this should be done without using triggers
Very new to the world of databases. Our current database situation is a train wreck, but I'm not at the point I can fix it. So, all I'm trying to do right now is copy our 'live' database so that we can use the copy for development, without worrying about blowing things up.
Anyhow, the problem I have is that when I backup, and then try to restore the 'live' database, I'm told the drive doesn't have enough space, which is accurate (only has 1gig, needs 3gigs). FYI, EVERYTHING that has to do with our databases is currently on a 14gb partition, of which 13gigs are being used. There is an empty partition of 120gb just sitting there.
Can somebody tell me what is the best way to get this 3gb development database up? I tried to route the Data and Transaction Log paths to the empty partition, but it still seemed to want to mess with the 14gb partition and told me there isn't enough space when I tried to restore.
Hopeing this is something I can accomplish. I use MS-SQL for close to100 CMS web servers. Everytime I create a new website I have to createa new database and then use the export from SQL to SQL option and copyall data / SP's / etc... Just no security information. My question iscan I automate this? Can I create a script that will run all this?Thanks for any help, and thanks for reading.Chris AuerJoin Bytes!
Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!
Thanks for ANY information you can give me... ~Jepadria
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest Set Partner = N'TCP://SQL2.mycom.com:5022'; go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
I have a database called sky and its tables, views, procs and functions owned by sky. I need to replicate the sky database to another server. I had problem because those objects have ownership sky not dbo. I can not change ownership when replicate the database. How do I replicate database objects that are not owned by dbo? Is this possible or I have to change ownership from sky to dbo before replicate the database?
Thank you very much for your input and suggestions.
Dear group,I have to replicate remote data to a SQL Server in the headquarter.The data are on a site which does not have permanent online-connectionto the headquarter.I have written a script which replicates the data and i want to set upa process / batch on the headquarter-machine which roughly does thefollowing1) Connect to the remote site2) start replication script (in fact a stored procedure)3) on success disconnect from the remote siteAll this has to run automatically e.g. during nighttime.Could someone please outline how i set up the connection and keep itup during my database-action and how to automatically disconnect aftersuccess of my replication script.I'd be jolly grateful.Thanks in advance and Greetings from ViennaUli
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
I have configured replication between Always ON Availability Groups  (Listener) (PUBLISHER), remote distributor to XYZ SUBSCRIBER...with above link ...
Now, I want to know how to replicate Data from XYZ SERVER a PUBLISHER to Always ON Availability Groups  (Listener) (SUBSCRIBER)? Distributor Database being on XYZEX:
XYZ SQL SERVER as PUBLISHER, and DISTRIBUTOR to Always ON Availability Groups  (Listener) SUBSCRIBER...
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?
I made a website in ASP.net and using sql server 2005 as database. There is sometime processing data that need long time processing ( about 20 minutes ) and big data. It works fine in dev box, but when I place on shared hosting, and some people access it crashed. The website can not be accessed. Hosting support told me maybe I need to reprogram my code. So anybody has solution for this problem ? Should I create new thread ?
Hey I was wondering if you all could help me with my database. I am very noobish as databasing and I am not sure what I have is good or bad. This is very long since I tell you basically what my whole site does/will do. This is in hopes that it will give you a better understanding of how many database will work.
Japanese WebSite
Purpose of the site:
This sites intention is to help people learn the 2 basic Japanese character sets Hiragana and Katakana. Hiragana is usually used for Japanese words and contains 46 basic characters, plus a set of special characters. Katakana is usually used for foreign words and also contains 46 basic characters plus a special set of characters. Hiragana and Katakana are written differently when they are written with Japanese characters. However if you right them in romaji (this is basically writing words as if you where writing English and does not involve using characters. This is used for foreigners while they are learning how to write the Japanese characters).
Hiragana and Katakana are pronounced exactly the same. This site will use a quiz style format. The user will first choose from the set of Hiragana characters then the Katakana characters. Once chosen the website will randomly choose a character and display the Japanese symbol of it. The user will then write the romaji representation in the text box provided.
It will be then checked and if the user is right they will get a new symbol. If they are wrong they will have try again. The purpose for this is for the user to get use to see how the character looks like but at the same time writing in romaji what will be easier for them. This should make it easier for them the next time they see the character to know how to say it.
Version 1
The site will go through many versions. This version I am focusing on using C#,asp and ms sql. Later on in future versions ajax and javascrip will be introduced and parts will be changed. Version 1 also uses CSS and html.
Version 1 will contain these features.
1. A practice section. A user will go through a wizard like setup that will first let them choose from the basic 46 Hiragana characters (the other special characters will be added later). They will then go to the next setup and choose from the 46 basic Katanana characters. After they go to another section that asks them how many repetitions they want to go through. If the user is a member(free membership) they will have the option to save their settings that will be stored and later available on the front page called quick links. The next page of the wizard is actually going through the quiz. Once they are done they will see a summary of how many they got right,wrong and how many they needed assistance on. At this point they will be able to view a chart to see their progress(current chart showing there last attempt, a week one and a month one). These charts will be either made using c# or ms sql(I saw a something that says you can make something like that but have not confirmed it). I may do both ways for a learning experience.
2. A Registration form, login and logout.
3. Quick link. Quick link will be for registered members so they don’t have to go through the wizard every time to make their practice quiz up. A user will go through the wizard once and choose what they want and save it. Quick link will be on all the pages in the form of mostly likely a grid view. A user will be able to store 3-5 different quiz’s (in the future maybe more). A user can then click on the quick link and it will just take them to the quiz. The user will also will be able to update their quiz through the grid view. This will take them to the start of the wizard and they can go through it again and then just save over it. They will also be able to delete there quick links through the grid view.
Currently done
Half of step 1 has been completed. Currently a user can go through the wizard and select what they want and choose how many repetitions and go through the quiz. However the summary and charts are not done.
Future versions.
Design will be a major part of it since I have not focused on this at all. Also since I wanted to experiment with dynamic controls I made all the checkboxes for the selections dynamically. This caused a problem that a user must hit a save button since the controls have to get recreated and updated.
I think normally this might not have been a problem but since I since I am using a multiview(to create a effect that the wizard is all on the same page and not the whole page has to be reloaded) it caused some problems(I can’t really remember since it was a couple months ago when I started but had to stop due to the amount of school work). This is why I want to go back later and make them with ajax. This also makes another learning experience.
Where I am at now.
When I started this I really did not think much of my database mainly because I am not good at data basing and don’t really like it that much. I have had a course in data basing (was with oracle though)that was part of my program. At the time I did not understand very much since I found the teacher going too fast and he started at chapter 8….. Also when you got 7 other courses with it it’s hard to learn lots.
I have gone through some tutorials and stuff. I am not very keen on reading a database book since I just don’t have the time since I want to also read a book on c#, javascript, ajax and so forth. Also I don’t know how much I will retain/learn from a book. With some of the stuff I just find its better to try to something where you will be interested in and then when needed read chapters of a book or ask questions. This is what I done for my c# stuff like I had a c# with asp course and 2 months after the courses I started this site and I had to ask myself what the heck did I learn in that course since it seemed I knew nothing(and I was close to top in my class). With pounding it out and reading I was able to accomplish what I needed. Like I still want to read a book on C# but for now it’s just not going to happen same with Data basing.
All the pervious information was so you can understand what my website is trying to do so you can better evaluate my database design and answer my questions.
First Design of my database.
This is when I just made it up on the spot and did not do any database design or anything.
Question 1: I forgot how to limit select query results. Like If I just want to show 10 query results how do I do this?
I have currently 2 tables How one called Hiragana and the other Called Katakana
I quickly found that this design was very bad since I was unable to add new rows very easily. Each character set has each of its characters in rows of 5. Some rows however have only 3 romaji characters so what they do is leave the other cells blank representing that they don’t exist. I wanted to do this too so I figured the easier way would be to have just null rows of data. So say for “ya,yu,yo� it would be like this “ya,null,yu,null,o�. That’s how it would appear in the database so when my c# would read it in it would not have to account for rows that had less than 5 characters since it would have already the nulls for it.
You can check a chart out here: http://www.alpha.ac.jp/japanese/img/moji.gif
So with this poor design it would result me in basically rewriting the database.
I have thought of 2 possible ways to. I am not sure if either way is good but I will try to explain my reasoning as much as I can.
Possible Way 1
In this approach I have 5 tables.
Hiragana
This table stores all the information about Hiragana Characters
This table will store the users quick link information.
CREATE TABLE [dbo].[QuickLinks](
[QuickLinkName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserID] [int] NOT NULL,
[SavedCharacters] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SavedImagePaths] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Users
This will store login information.
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmailAddress] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTimeStamp] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
Charts
This will store all information that will be used for charts.
CREATE TABLE [dbo].[Charts](
[PracticeNum] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[Correct] [numeric](3, 0) NOT NULL,
[Wrong] [numeric](3, 0) NOT NULL,
[AssitanceNeeded] [numeric](3, 0) NOT NULL,
[TimeDateStamp] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Charts] PRIMARY KEY CLUSTERED
(
Relationships
Users and QucikLinks have a PK to FK relationship this is because quicklinks is dependent on the user having a user account. It also away to identify which QuickLinks will belong to who.
Question 2: Currently I don’t have a primary Key for this table what would be a good primary key for this table?
QuickLinks also does not have any relationship to either to HiraganaCharacters or Katakana Characters. This is because I did not feel it was necessary to grab the information from here. I simply could just grab it from the array that holds this information from my c# code.
Question 3: How can I make this table for all users? Like Say they choose 5 characters and save it as QuickLink1(as the QuickLinkName). Should each of these 5 characters gets its own line or should it all be saved in one row? Or should I even have another table to hold this data?
The thinking for this one is that Charts will use the UserID to tell who this data belongs too. It will store the number of correct answers, wrong answers and AssitanceNeed. This table will also hold a timeDateStamp. The reason for this is because I want to have a chart that displays the last Quiz they did and also the stats of a week’s worth of quizzes, and month’s worth of quiz’s. With a timeDateStamp I should be able to add up all those columns and then display them for whatever period I choose.
This way uses 4 charts instead of 5. The main difference is that the Hiragana Charts and Katakana Charts have been merged into one table.
This way has another relationship between Characters and Quick Links. This will store the Character ID instead of the actual Characters and Path through the use of C# code. When a user selects a box it will make a note of the CharacterID. When it is time for the user to use the QuickLink it will join the tables together and filter out the CharacterIDs. Giving me the CharacterName and Path.
The problem with this way is that well the code I written will need to be changed since currently it grabs stuff from 2 tables into one. I don’t know if this would save anytime over the first way either.
I hope these are sort of close to an alright design since I did try to think about how my website would interact with the database.
Sorry for it being so long.
Summary of Questions
Question 1: I forgot how to limit select query results. Like If I just want to show 10 query results how do I do this?
Question 2: Currently I don’t have a primary Key for this table what would be a good primary key for this table?
Question 3: How can I make this table for all users? Like Say they choose 5 characters and save it as QuickLink1(as the QuickLinkName). Should each of these 5 characters gets its own line or should it all be saved in one row? Or should I even have another table to hold this data?
I had a database of electronic resources which had 28000 records earlies and was working fine. Now we have added a whole bunch to make it 800K records which has increased the search time to 14-22 seconds which is not acceptable. I have all the tables indexed.
Please help me how to solve this problem. Let me know what other information I should put up here to make my problem undestandable. Thanks in advance, Archana
I have a database with 2 almost identical tables. Each one is about 1.2 Billion rows, 0.5 Tb each. I've truncated one of them and started SHRINK DB WITH REORGANIZE PAGES It is running already for 3 days. When should I start to worry :)
We are setting up a test lab environment with 100 machines. Â We want one master testing db that gets replicated to each to run scripted application tests nightly. Â
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. Â I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
When I import data from MS Access databases, some fields appear as "<Long Text>" in SQL. I cannot edit these fields in SQL. I have to export the table to Access, update the field, and then import again.
There's got to be an easier way. What am I missing here?
I'm trying to rebuild my master database for sql server 2000. The process of rebuilding stared fine. But it is almost 4 hours since it got started. Performing it on a test system. Got doubtful and started the same on another test system. Issue is same and it is almost 2 hours. The Db size is less than 100 MB in both cases. IS IT NORMAL? I've tried the same for SQL SERVER 2005 and it got finished in couple of minutes. Please advise.
HiI have have two linked SQL Servers and I am trying to get remote writesworking correctly (fast).I have configured the DB link on both machines to:Point at each others DB.I have security set up to map each others server loginsand Server Options: Collation Compatible, Data Access, RPC, RPC Out, UseRemote Collation all checkedMy problem is that when a SP performsBegin TransactionUpdate Local TableUpdate Remote TableCommit TranIt takes several seconds to complete. (about 7 seconds not acceptable tous)This is due to the remote update - how can I improve the response time?example of a stored procedures that takes timewhere ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
I have SQL 2014. When I try to restore a user database using SSMS GUI, the Restore Database Pop up box never pops up. This happens for any database on this server at any time. Sometimes I get the pop up, some times I dont get.
So I tried to click on Databases on Top and Restore Database, and then select the db that I need to restore from Drop down, then it shows "creating restore plan selecting backups" but it takes forever.
We have full backup and trn log backups every 30 mins. So is it trying to get all these backup files in the background causing this issue? If yes then how to overcome this?
I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current.
I'm not interesting in changing this method as I'm not the maintainer (as yet).
Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination?
I'm using SQL Server Management Studio Express and I'm trying to figure out how to copy a table(s) from my local database to my web hosting database. I know how to do it in 2000, but it's completely different now. Is this feature not allowed on SSMSE? If so, then how do I deploy database tables to a web host?Also, how do you add local database(s) to SSMSE? I tried to use 'attach database' in SSMSE and it wouldn't allow me to navigate to My Documents folder where the database resides. Thanks...
We have 3 maintenance jobs configured in this particular DB instance:
Daily backup of system database - SubPlan1 (Check Database Integrity Task --> Rebuild Index Task-->Backup Database Task)Daily backup of user databases - Five subplans for each task : (Check DB integrity --> Rebuild Index -->Backup User Database, Backup Log -->Cleanup History)Weekly maintenance - SubPlan1 (Check Database integrity job (system+user DB)Â + rebuild index job (system+user DB) )
PROBLEM: I just noticed that the User DB Rebuild Index task has been running since the 03/04 and the Weekly maintenance plan - subplan1 since the 12/04.
Which job is "safe" to stop without impacting the database?
If I want to copy the data from Table1 in Database A to Table2 in Database B but Table1 column name is code , Table 2 column name is vesselcode. (Code = vesselcode)
How to copy all data from Table1 in Database A to Table2 in Database B ? Do I need to write the SQL statment ? and Can I use Server Enterprise Manager Tool?Thx a lot.
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
