We are building a smart client that ships with it an offline db - Sql Express.
I wanted to check if there is a preferred/recommended approach to deploy the database ? I'm essentially asking between
1. Copying a database file and
2. Creating the database file by connecting to Master and issueing create database etc and then running the scripts as we need it.
Any help will be great,
Thanks,
Avinash
PS: The app is a multi user app and may be used by multiple windows users on the same machine. So we currently use the Auto Attach feature in the connection string with database files in the Special Application/User Data folder. So we isolate files b/w users.
We have a laptop that has SQL 7 installed on it, and we would like to copy an existing database to the laptop. The laptop does not have any connectivity to our network. I did backup the database on the network, and place the backup copy on a CD. I then was hoping that I could create a new DB on the laptop and restore the copy from the CD. But, I have not been able to figure this out. Can anyone help?
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
I have a database on the server which is in production and I would like to create another copy and use it for testing purpose. so the application can point to the test database for testing purpose.
What is the best way to do? I guess I have to name the test one with different name right?
can I do it without detach the production one? or just copy the database - tables structures from the currnet one?
How do I create a copy of an existing database using vb.net? I have not been able to find solutions to the problem using vb.net. Can someone please help me. Thanks
All, I am trying to create a copy of an existing database and place on a different sql server for testing purposes. Currently my method of doing this is to create a backup, then restore a database on my new sql server. But here is the problem I am running into. When I look in Enterprise manager I can see all of the stored procedures and tables and data just fine, which is how I would like it. But, when I open up Query Analyzer I am not able to run any queries because it says "invalid object name" error. I know that the object name is correct. I think that I am having a conflict between the users which were carried over from the source backup file and the users that are on my 2 new sql server. If anyone could help, I would really appreciate it. I am stuck at this point. I am guessing it is probably something simple I am just unaware that I need to do.
I have a database that I have been creating and testing. I have added some junk data and some data into lookup tables. Is there a way to create a clean copy of the db and keep the lookup table data? Also will I be able to create the db under a new name?
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables. How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
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 table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
Hi! I did: alter database mydb set single_user with rollback immediate; exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got >>Cannot copy <myfile>: Access is denied Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success. I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files? Best regards
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 am attempting to use the copy wizard to copy databases from SQL Server 2005 to SQL Server 2008 R2 w/ FP1.
The copy fails with a login failure to SQL Server 2005. I have a user id & password under Windows for both servers. I have a user id and password under SQL security with the called for admin security rights.
The 2005 server has two instances, 20 databases, two dozen maintenance plans, and over a hundred users. I really would like to use the utility so I don't have to recreate everything manually.
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)? For example, how much insert row at below sample?(the max value of nCount) for(i=0 ; i<nCount ; i++) { pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData)); }
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ? BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
------------------------------------------------------- My solution is like this. Is it correct?
// CoCreateInstance(...); // Data source // Create session
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
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?
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
Just a quick one. I have some create scripts that were created for mysql and i am wanting to convert them to a
SQL Server create script. I am just wondering if anyone knows of any utilities or codeplex projects, etc that can perform the convert process.
The script is fairly basic but it is not fully compatible but i have a lot of them thus why i want to automate the process. The majority of the script is just table create statements like the following:
Code Snippet -- ----------------------------------------------------- -- Table `dbo`.`Staff` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `dbo`.`Staff` ( `StaffId` INT NOT NULL AUTO_INCREMENT , `IndividualId` INT NOT NULL DEFAULT 0 , `PositionId` INT NOT NULL DEFAULT 0 , `EmploymentStartDate` DATETIME NULL , `EmploymentEndDate` DATETIME NULL , `SupervisorStaffId` INT NULL , `TerminatedEmploymentReasonId` INT NULL , `DeletedFl` BIT NOT NULL , `CreateDateTime` DATETIME NOT NULL , `CreateUserId` INT NOT NULL , `ChangeUserId` INT NOT NULL , `ChangeDateTime` DATETIME NOT NULL , `VersionNum` INT NOT NULL , PRIMARY KEY (`StaffId`) , INDEX Individual_Staff_IndividualId_Ref (`IndividualId` ASC) , INDEX Lookup_Staff_PositionId (`PositionId` ASC) , INDEX Staff_Staff_SupervisorStaffId_Ref (`SupervisorStaffId` ASC) , INDEX Lookup_Staff_TerminatedEmploymentReasonId (`TerminatedEmploymentReasonId` ASC) , CONSTRAINT `Individual_Staff_IndividualId_Ref` FOREIGN KEY (`IndividualId` ) REFERENCES `dbo`.`Individual` (`IndividualId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Lookup_Staff_PositionId` FOREIGN KEY (`PositionId` ) REFERENCES `dbo`.`Lookup` (`LookupId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Staff_Staff_SupervisorStaffId_Ref` FOREIGN KEY (`SupervisorStaffId` ) REFERENCES `dbo`.`Staff` (`StaffId` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Lookup_Staff_TerminatedEmploymentReasonId` FOREIGN KEY (`TerminatedEmploymentReasonId` ) REFERENCES `dbo`.`Lookup` (`LookupId` ) ON DELETE NO ACTION ON UPDATE NO ACTION);
Thanks for the help
Anthony
Note: i know if you have a mysql database you can go from one to the other, but i specifically need to take these mysql create scripts and convert them to sql server create scripts and there are a LOT of them i.e. more than 1000 script files that each contains a couple of hundred table create statements.
I've tried those two operations in the Management Studio. Though we can create a mining structure and mining model in Management Studion, but we cannot process the analysis-service database.
(1) I create only a mining structure through CREATE MINING STRUCTURE. No error reported. But if I process the analysis-service database in Management Studio I always get error
'Error : The '<mining_structure__name>' structure does not contain bindings to data (or contain bindings that are not valid) and cannot be processed.
I then tried to create it by creating and running an XMLA script. It was successful. However, it's much harder to learn XMLA.
If any of you created an analysis-service database in Mgt Studio, and create a mining structure in the same place using DMX script, can you process the database?
(2) Is there any use of CREATE MINING STRUCTURE operation without binding to any table? Examples I saw so far did not show relating it to do. In my experience processing the analysis-service database with that mining structure is doomed to fail.
(3) Is there any way we can create mining structure through CREATE MINING
STRUCTURE operation in Management Studio and use RELATED TO clause
to bind it to any Relationship to an attribute column (mandatory only if it applies), indicated by the RELATED TO clause
(4) If this is the fact, is there any use of CREATE MINING STRUCTURE operation? If we use BI Dev Studio, it's much easier to use the wizard.
(5) I found I cannot create a mining model inside a mining structure through operation CREATE MINING MODEL. If you call that operation, you end up having a mining model and a mining structure with the same name. I found that in order to create a mining model inside a mining structure you have to call operation ALTER MINING STRUCTURE ADD MINING MODEL. Is it true this is the only way?
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
