There are 3 applications each one of has respective databases.Now i am trying to design a database which should capture the important entities from these systems.
Is it possible to create diagram from existing database? Need to understand the schema of database which has no documentation. So would like to create diagram for that database.
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:
CREATE TABLE [dbo].[VRes]( [VResID] [uniqueidentifier] NOT NULL, [Mes] [varchar](max) NOT NULL, [PID] [uniqueidentifier] NOT NULL, [Segt] [int] NOT NULL,
[code]....
Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?
I normalized the below tables but I am finding it difficult to copy data to the new tables. How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it. CREATE TABLE SKU_DATA ( SKU Integer NOT NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer). SKU and SKU_Description are candidate keys. Primary key is SKU.
I have the following problem that I need to find a solution. I have created a generic user id and login in the model datbase on SQL 7. However the genericuser does not show in the explorer window for some databases, does not work on some databases. I have to go into TSQL and use dropuser to get rid off them and re-link them again.
Does any1 have a clue about why this happening? How it can be solved?
Hi there, the subject says it all. I have two databases on one server. I want to replicate 1 table from one Dbase to the other. Can it be done? Am running SQL 6.5 on NT 4
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, * FROM msdb.dbo.backupmediafamily WHERE media_set_id =(SELECT TOP 1 media_set_id FROM msdb.dbo.backupset WHERE database_name='MyDatabase' AND type='D' ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.
Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.
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 would like to have my data rearranged to look like the following:
timestamp :var1 value :var2 value :var3 value ________________________________________________ 11:00 x y z 12:00 a b c ....
i am working with my database in a c sharp application. so if anyone does come up with a solution for this maybe keep that in mind so that i can discuss how to impliment it in the code.
I am new to sql and my boss want me to write the program for database and he wants to pull the info from existing table from sql server which is used by Microsoft GP Dynamics and write me code or some kind of front end so when he wants he can pull same data from GP dynamics table and generate some report with other custom table.
How i can do this task? What I have to do in sql so I can use same table to view info in real time so that means if i enter new data in the table it will show up same time in the front end as well.
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF', MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF', REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
Hi Anyone Knows I want to store SQL 2005 Existing Database into SQL Server Through .Net.if i give Database Name in .Net and that Database automatically stored in SQL Server 2005.P(including that Existing Database Tables and Stored Procedures).Please Help me.
I've plan to writing Stored Procedure ("SP"). This SP contains program that produce a result and this result will insert into already define summary table. This SP will running every 30 minutes.
What is the best solution? I should put this already define summary table in existing Database or i should create a new database and create the summary table into this new database. Can i using ReportServer Database to create this summary table?
I have a MSSQL database from a pre-existing app. The data files do not have an MDF exension, they have a .dat extension. I can however use Microsoft SQL Server Management Studio Express to run queries against the files, but I can not get VB 2005 to connect. When I attemp to add it as a project data source, I get the following error:
"An attempt to attach an auto-named database for file c:dataMSSQL$DATA_SQL....dat* failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
*Database names = company names are omitted to 'protect the innocent'.
Does anyone have any ideas? Since Microsoft SQL Server Management Studio Express worked so cleanly with the database I was expecting to easily get to the same data using VB.
I have a Systema that uses a DataBase an I Want to Install y other machines, I need to Install a Visual Studio Program that uses a Database, How do I Install The Visual Studio Program, the Sql Server Express, and the existing DataBase.
How do I go about creating a cloned copy of an existing database on the same server? I basically want to set up a test copy of a production database, but let it reside on the same server with a slightly different name, prefixed with 'Testing_".
I typically just use Studio Express Manager and not scripts to do admin tasks. Here's what I tried that didn't work so well:
* Had full backups of production db * Detached production database I wanted to clone * From within Win Explorer, copy the .mdf and .ldf files within same data folder and gave them a new name, prefixing with "Testing_" * Re-attached prodution db * Attached "Testing_..." database
Sql Express seems confused at this point with new database I attached. It references the full path of the database I copied from (c:program filesmicrosoft sql servermssql.1data...". I rename the datbase, removing full path and prefixing with "Testing_".
* Using Sql authentication, changed the default database for the Login account to the new cloned copy of database. * Did some query level data sanitizing to the cloned copy of the db. I am positive I performed this on the correct db. * Was able to connect/authenticate to the cloned copy through a custom VB app on my dev machine, and all looked well. * Tried to connect to the cloned db from another machine and couldn't. Went back to my dev machine and noticed the original production db looked hosed -- there were no tree nodes available to it! Was still able to connect to the cloned "Testing_" db copy, but not able to connect/authenticate anymore to the production copy. * Detached cloned copy of "Testing_" db and moved it out of the data folder. * Detached production db. Got an error msg indicating operation failure of some kind, but the database was gone from the tree. * Reattached the production db but couldn't hit it from my custom vb app. Re-did the login and user accounts used by the app and was able to hit it. However, the data sanitizing I had done earlier in the cloned copy clearly was visibile in the production db! * Had to restore production db from backup. Had problems authenticating from app, and recreated User account before the app was to connect.
Q1: What I am doing wrong? Sql Express was obviously as confused as me somewhere along the line.
Q2: Is specifying a default database used by a Login account necessary for a VB app like mine. I think it was something I discovered in the past, however, this would seemingly present a problem whenever I want my app to hit a different database, established through a connection string.
It may sound like crazy question, But just curious to know if any one tested this scenario.
I was restoring an existing database from a backup. While the restore is in progress, I found i was restoring with old back, then I canceled the operation.
Now Will it restore the partial data or it rolls back the entire transaction and brings back to original status or it will currupt the database??
Please help me in this regard, i am desparately need of this queries.
1. I have database and i want to copy the same database with another name(schema and Data remains the same on). Then the newly created database should be transferred to another location of the hard drive.
Can you write a script for me to the newly created to removed from the object explorer and copied to some hard drive location. Example "C:mydatabaseTest2Database.mdf", "C:mydatabaseTest2Database_log.LDF"
2. How to create a proper index on a table because some of the tables i don't have primary key. Give me an example.
3. write a script for "Attach" and "Detach" database.
please write a script to the above problem. Because i am working with Visual Basic programming. I will use these scripts to call from stored procedures.
Hi I am running my sql 2000 database server and I just want to copy that database and paste or put on another server which has also run same version of sql 2000 database. I tried to copy and paste the ldf & mdf extensioned files which are located in "C:Program FilesMicrosoft SQL ServerMSSQLData" into my 2 server, but when I open up the enterprise manager, I cant see the new database. So this way dont work, I tried to do something with export and import wizard but it just creates another database and copies the data in that database in the same server. I want to copy that data and put onto another system, please guide me. I'm confused, Thanks a lot
Hello All, I was wondering if anybody can help me with the following question: I'm working on the application where the Database, it's table (2) and several stored procedures are involved. The database is SQL Server 2000. It's also very old and involves a lot of operations, stored proc and so on. I just need to re-write a piece of the app which is using existing stored proc. Most of them are DELETE, INSERT and so on. I don't want to work with real stage DB and need to make a copy of the Database to my Dev box. So I tried: * Right click, All Tasks, Export Data into the newly created database on my dev box. That doesn't work, every time I try doing it, it fails somewhere in the middle of the process. I'm thinking it happens because of complexity of the database. I tried several options there already. Still nothing. I need the whole databse to be copied because I'm not sure which stored proc the app is using so I need them all, and tables too. Is there another way of doing this? Thank you, Tatyana
I would like to create a test environment on a separate server running advanced server 2000. The current production server is NT4.0. What is the easiest way to copy the databases from production to test. I installed SQL7.0 on the new box then created the backup devices. Next I copied the backup files for all of the databases including master etc. to the new backup device folders. I tried to restore the master db first - then things went down hill. Any recommended procedures regarding this? Any help would be greatly appreciated.