Hi All,
Could anybody tell me how to make a copy of an entire database on the same server? The copy wizard won't allow the a copy to be made on the same server.
And I believe I won't be able to detach the database and re-attach it to a newly created database with a different name.
Thanks in advance!
How can I copy the entire database (all the objects from one database to another database)?
thanks in advance.
I'm a newbie to script writing. I'm trying to write a script to copy alldata from a table to the same table in a 2nd database. Both databases areon the same server and are identical in design. I can do this with DTS butwanted a script I could email to a user to run in Query Analyzer.Example:Copy entire table called 'Customers' in the 'Data01' database totable 'Customers' in the 'Data02' databaseI want to overwrite all data in the destination table.Thanks
View 1 Replies View Related
I have table in my SQL database, I want to copy(or insert) one record(one entire row using primary key, which is auto)
I am thinking something like this
Insert Into Table1 (a, b, c,d,e) values(select a,b,c,d,e from Table1 where Primarykey=1 or any number)
telll me how do I do that
maxs
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor
how to copy one database tables, columns, store-procedures. to another database without any error
View 2 Replies View RelatedI have tried searching all over and have not been able to find what I need, but this is what I have pieced together.
Background:
I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?
This is what I had in mind.
-Install SP4 on the source server
-Backup source to tape
-Restore from tape to destination server (restore system dbs as re_master,re_msdb)
-Shut SQL services down on destination server.
-Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
-Restart SQL services
-Restore User databases from tape.
Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?
Also, are there any issues going from Windows Server 2000 to Windows Server 2003?
Thanks in advance.
Has anyone here been having issues where SQL Server Management Server will sometimes ignore selected text and run all scripts on the current tab?
This has been happening most recently after a connection times out or is forced shut. From a disconnected tab, if I add "Use Master" at the bottom of a page full of script, it will often run ALL statements starting from the first line.
I have SP2 installed which rolls management server to 9.00.2047.00 - but this has been an issue for me even before SP1 & SP2.
I can find nothing in the knowledge base regarding this, but perhaps my search string wasn't very thorough.
I'd just like to know if others are experience this issue so I can report to MS.
Thanks.
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
sp_MSforeachdb 'Select ''?'' as Dbname, SPECIFIC_SCHEMA, ROUTINE_NAME
From ?.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME like ''%YourSPName%'''
I was having this problem:http://groups.google.com/group/micr...78?dmode=sourcewith it taking an inordinate amount of time to enumerate the databaseson my local SQL Server instance, so I went through all the localdatabases and unchecked "Auto Close" on the "Options" tab of theDatabase properties in Enterprise Manager.This cleared up the problem nicely. In EM or through SQL-DMO, thedatabases on the local server enumerate quite quickly now. However, anynew databases that are created have "Auto Close" checked, so eventuallythe original problem will resurface.Is there some way to tell SQL Server to create any new databases with"Auto Close" set to No?
View 9 Replies View RelatedThis problem may appear trivial to you guys but is troubling me since quite some time now! The problem is that I created a website using express studio happily and it worked flawlessly on the local host. Now I want to move it to a remote server and my host created a database name ASPNETDB for me on the server. The problem is that that ASPNETDB database is virtually empty and I want to copy all my tables,stored procedures etc etc(complete database) to the remote ASPNETDB database. How can I do that? In the management studio express edition there is no command available to copy paste all contents of the database to another database. Please help I am quite confused!
View 4 Replies View RelatedAt my current employer we are struggling with the best way to manage security and deployment of a project that contains databases, SSIS, SSAS and SSRS components, using configurations.
Environment (Dev):
3 SQL Server databases, all using mixed-mode security, using SQL Server security credentials.
12 SSIS packages; one master package, eleven child packages, 3 shared data sources
1 SSAS database; one cube, 15 dimensions, three referenced data sources from the SSIS project (in same solution)
6 SSRS reports, one data source to cube (not shared- doesn't appear SSRS can share datasources among other projects in the solution? Why?)
Everything runs fine in development. Now comes the tricky part.
Deploying SSIS and SSAS into production environments:
-Packages use XML config files for connection strings to three relational data sources.
-Deploy to SQL Server storage. Deploy wizard copies package dependencies (including XML config files) to default location set in INI file. When I do this, no config file shows up in remote server (remote server not set up identical to local, so directory does not exist. Need UNC path?) So, being a developer with no "special" permissions on the PROD server, what security permissions is allowing the deployment wizard from copying files to this location on a production server?
-Using a deploy script using dtutil doesn't copy the SSIS dependencies. Is this matter of using COPY or XCOPY to copy the configuration files to the dependency location? Again, in real-world practice, do developers typically change this location in the INI file to another location, or stick with the default. In either case, how does security work that allows files to get copied to the remote folder? (i.e. manual, or SQL Server manages this file folder permission through some other magic)
When using SSMS and running the package after being deployed on the remote server, if the config path is the default (e.g. C:program filesMicrosoft SQL Server90DTSPackages...) it appears to be read from the local machines directory rather than the remote machines directory path (do I need to use UNC paths? The wizard doesn't give this option it seems)
-When scheduling the job from SQL Agent, does the proxy account need permissions to the folder the config files sit in?
-What about the roles security on the packages themselves? Where does the server roles come into play (dtsltuser, dtsadmin)
-Because the SSAS project uses connection references to the SSIS project in BIDS, and SSIS project uses configurations, will SSAS pick up on these connections?
-What about impersonation levels for SSAS? Leave all data sources set to default, and set the database impersonation level to "UseServiceAccount"? What if the developer is not the same as the OLAP administrator on the production server? In this case, Use Service Account isn't an option, and neither is the current users credentials.
-SSAS database also has security for Full Control, but still doesn't prevent security at the data source level within the database (talking about impersonation level, not source db credentials)
-How can SSRS connections leverage other shared connections?
As you can see, there are a ton of security considerations, none of which are intuitive and can be configured multiple ways and actually work (and a ton of ways that won't work).
I need a simple cheat-sheet about each step to take to configure this so multiple developers can work without interruption, hot-deploying SSIS, SSAS, and SSRS changes into different environments (QA, PROD).
-Kory
Hello,
i have a problem that i don't understand why it occurs: i have a package like this (resumed and ordered by its correspondingdata flow):
1. move data files to a path through by a file system task
2. import data of data files to a sqlserver database (each file through its own data flow). The origin of data is in files that have been moved to the path of step 1
3. execute a procedure of database (sql task) to manipulate data and finally export resume (data flow) to a new file in the path (moved by other file system task)
PROBLEM: if i execute the package task by task (one by one), it runs ok, but then when i build and run package as a whole (debug), it produces an error in the data flow such as it doesnt' find the path to take data of data flow (it doesn't find the source). Really it doesn't find source because the data files aren't in its correct path (like step 1 had failed). In summary, it looks like if the order of the task of package aren't correct when i execute the entire package, because the log doesn't thread any error about step 1 (file system task).
ERROR MESSAGE: an OLE DB error has occurred. Error code: 0x80040E37
Thanks for any help.
Gema
I need to search all of the tables in a DB for a specific number (say, 123456). I am unsure of which tables may have this number in it. Is there a way to search an entire DB for a specific criteria?
This is what I want:
Select *
From [db]
where [column] like '%123456%'
I understand why this dosen't work (the DB doesn't have column names), but is there a way to display table names where this number exists? If I had that, then I could search the tables individually.
What is the best way to create a blank copy of my database? I really want some of the tables to keep the data in them, these are some of the lookup table.
I appreciate your help.
Hi, I'm a noob who sucks at programming, and sucks even more when it comes to database.
I'm operating a small website with an SQL Server database that drives my shopping cart. I want to close this website down, but I don't want to lose all the data in the database in case I want to do something in the future.
How do you backup the entire database into a file so i might be able to import it at later times? Do I have to do this with SQL, T-SQL, or the manager?
Hi.
I have a long TEXT column and it cuts off at a certain point
in the Query Analyzer. I tried copying and pasting to notepad
but it is still half the actual string. How do i get the whole
TEXT string from a given column?
Thanks!
I have built a template database which I'm finally pleased with, however I want to periodically duplicate the design - not data into a new database. How can I duplicate a database? I was hoping to right mouse, copy, then right mouse, paste, and then be prompted for the new name but no such luck.
View 6 Replies View RelatedIs there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.
Any ideas?
Thanks in advance.
How do I "ltrim" an enire colum?
Thanks,
Hi friends
Suppose i have a table of 100 cols and 10000 rows i want to search a particular field called 'Newyork' . I dont no what the col is ?
Can anyone tell me how can i search that
Vicky
Hi, this is my first post on these forums, so please excuse me if this topic has already been covered.
I'm currently working in a power station for student vacation work placement. I need to export data from a database that gets it's data from machinery and inputs out in the plant. The machines that provide this input put it into a database, and I need to find the relevant data to export.
My problem is that, in some cases, the sample data that i'm given may be under different field names, in a completely unrelated table. I was looking for a way to search the entire database (250+ tables) for a certain string, so I can find where it is in the database, and run queries on the table it originates from. For example:
My sample data shows me that I have an object with the ID Y03A3DEA_TH1. I know this ID will occur somewhere else in the database, but i'm just not sure where.
If anyone knows of any way that I can search the entire database for specific data, either using tools in MS SQL 2000, or 3rd party apps, i would greatly appreciate their help.
Thanks a lot,
Jack Smith
Hi! I've some data in a table, and I want to add these records to another table, but I tried yet with select ... Into ... and I lose all the data from this one. I need to ADD the data from table to table. Any suggestion?
Thx
Hi,I need to lock a table so that Inserts are prevented as well as deleted andupdates. At present I'm thinking this might do it:SELECT * FROM myTable WITH(UPLOCK)but then again I'm not sure whether this will cover the insert case.Thanks,Robin
View 5 Replies View RelatedRather than setting by session I would like to configure the DB as readuncommitted.Thanx Advance.
View 5 Replies View Related
Hi,
Is it possible to copy a database from the existing database. I mean to say creating a duplicating database.
Note: I can do it by using "Generate SQL Server Script Wizard" or "Database Publishing Wizard" which saves a some filename.sql and later executing this will create another database by assigning some xyzdatabase name.
My main motive is copying a database in a single line script without using this lengthy sciprt.
Example
FirstDatabase (contains 10 tables 10 stored procedures)
SecondDatabase (also contains 10 tables 10 stored procedures)
If possible, can you write a script that attach a database from the C:MyTemporaryDatabase
Because this i need through programming Visual Basic using SQL Server. I want to create some temporary database.
Waiting for your reply.
Thanks.
Best Regards
Kashif Chotu
Im building a site on my pc using SQLExpress and i have a question about something. The database im working on has data inside the tables and i was wondering how i would move that to a different server though a script with all the data in the table.
View 3 Replies View RelatedHow can I do in order that a table show all its rows in a page?And if the table can only show a number of rows cause arrive to the end of the page show the table in the next page?
Thanks!
Hi,
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
Thanks for any help,
KT
I am giving the following statement outside the begin tran block in a stored procedure:
select * from table1 with (holdlock) where column1 = @colValue
I have the following two questions regarding above situation:
Being outside the begin tran block, will the select statement cause a lock to be held or it has to be within the begin tran block for the lock to be held?
Will the select statement hold a lock only on the row or rows matching the where clause, or it may lock a page or even the entire table in this situation?
Thanks
How can I instruct SQL Server to keep entire table in memory? ie the memory pages should not be swapped to HD.
View 2 Replies View RelatedI need to move everything from one 6.5 server to another. I have already installed 6.5 to the new server. (The new server has a new drive configuration) How can I move the Master Database and all other databases including sp's and logins?
Please Help ASAP!!!!
Thanks,
Jason