how can i copy a complete database (tables, views, stored procedures) with/in the sql server 2005 "server mgm. studio". the import/ export function only copys the data (tables).
sql server 2000 had a nice tool for that (import/ export data). but how can i do that with the sql server 2005. can't find anything ...
Script: [URL] ....
I need to move all of the contents of one database into anther with the same schema, and it looks like this might be just what I need. But it is from 2007, so I wonder if it is still current?
Also, having tried to run it on another database to generate the script that will actually do the copying, I have a few questions. It looks like it generates statements to import the data twice. For example:
BULK INSERT [TaPerfGDB].[dbo].[i1]
FROM 'C:Tempi1.Dat'
WITH (FORMATFILE = 'C:Tempi1.FMT',
BATCHSIZE = 1000000,
ERRORFILE = 'C:TempBI_i1.ERR',
TABLOCK);
And a little later:
INSERT INTO [TaPerfGDB].[dbo].[i1]
SELECT *
FROM OPENROWSET(BULK 'C:Tempi1.Dat',
FORMATFILE='C:Tempi1.Xml'
) as t1;
That does not really make any sense to me. It also generates statements like this:
bcp "[TaPerfGDB].[dbo].[GDB_GEOMNETWORKS]" format nul -n -CRAW -f "C:TempGDB_GEOMNETWORKS.fmt" --S"PGALLUCC-M7" -T
What is the deal with the double hyphen by the servername? Won't it just see that as a comment? It can be easily fixed, but I am just suprised that it is still there after all these years. My purpose in doing this is a desparate attempt to salvege a database that sits on a server with multiple drive errors. These prevent backups, so I cannot just restore the database on the new server. That is why I want to try an approach that goes table by table, so that at least all the tables which are not touched by the drive errors can be moved.
It is a 3 TB database running on SQL Server 2008 R2 std. ed.
I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.
An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.
Hello all! A while ago I started cataloging my collection of old radio recordings and I wrote a simple Visual Basic 2005 program to display them in a nice UI. I have implemented an SQL database with the project and all is fine, when I run the program it connects to the sql server and downloads the data. But when I try and change something by connecting from within Visual Basic at runtime with this code:
Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
Dim ConnectionString As New SqlConnection("Server=MARK-HOMESQLEXPRESS;Database=Library.mdf;Uid=MARK-HOMEMark;Pwd=sodoff;")
Dim sqlString As String
ConnectionString.Open()
sqlString = "UPDATE JackBenny SET Rating='Excellent' WHERE [Episode Number]=" & epNumber
ConnectionString.BeginTransaction(sqlString)
ConnectionString.Close()
End Sub
I get this error code in the log and a general unable to login error within Visual basic itself:
2007-11-22 15:44:39.78 Logon Error: 18456, Severity: 14, State: 5.
2007-11-22 15:44:39.78 Logon Login failed for user 'Mark'. [CLIENT: <local machine>
The state of 5 refers to "User ID is not valid." but I can login to SQL Server Management Studio Express just fine with the username and password in the code posted. Also of note, I can login to the Server Managment with the sa account username and password. When I use the credentials in visual basic code, same error. Any ideas? I am trying to connect to a database which is on this computer (local) in the root folder of the application, called Library.mdg.
Thanks so much all
Hi,
How can I encrypt / decrypt whole database (data, objects ... everything) in SQL Server 2005 Express Edition?
Quick solution from any champion of Databases would be highly entertained.
Waiting ..
Thanks a lot.
Hello,
I want to truncate all tables present in the particular database, Is there any simple way to do it? or do I have to do it on individula basis (table by table)?
regards
A couple of days ago I installed SQL Server 2005 developer. To pave the way for this I removed SQL Server 2000 and 2005 Express. Since then I've been unable to connect from any of my applications or websites in Visual Studio 2005 to either:- my new 2005 Developer database engine- my colleagues 2005 developer database engine. My colleague can connect to my databases from his visual studio on his machine. I am receiving unspecified OleDb errors.Anyone else had this happen? Anyone know where I should start looking? I've uninstalled all .Net frameworks and re-installed 1.1 and 2.0.Many thanks, Sam
View 3 Replies View RelatedFacundo writes "Hi:
How can i add a timestamp to a generated file of a complete database backup, the idea is to archive full database backups using the date to identified it.
Thanks a lot.
FB"
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
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
View 2 Replies View RelatedHello,
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.
Or is the datebase name part of the .mdf file?
Regards
Markus
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?
View 1 Replies View RelatedBackup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies View RelatedHi guys,
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?
Cheers
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...
View 8 Replies View RelatedIf 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
View 1 Replies View RelatedHi,
I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
thanks
hii want to copy one database table to onther database table using script?my database is ms-sql server 2000
View 4 Replies View Relatedi have an database created with MSDE using the windows authentication,
and now i want to send this database to someone, can i just backup this
database and send it,
can other people access my database with windows authenticaiton in his computer ??? or i need to do some extra stuff???
Running SQL 2000 and need to copy an entire database (structure and data) from our inhouse development server to our 'live' server at our hosting co. What's the best solution? I can't do it directly because both machines are behind firewalls or course.
Thanks in advance!
Jim
I am pretty new to SQL Server 7.0 and am having a problem. I have two databases, both on seperate SQL Servers. The problem I am having is I have a copy of one and a backup of the other. I need both of these to be restored into a third SQL Server I have locally. None of these Servers see each other so I can't register them. All I have to work with is the Backup and the copy. Can someone tell me how to accomplish this. I have tried to do restore and import.
Thanks so much in advance for any help.
What is the best method to copy databases among all the options.
* bcp
* sp_detach and attach
* DTS
* Back up from source restore to destination
Hi,
What is the quickest and easiest way to make a copy of an entire database ie. create a test environment from production?
Thanks in advance,
Darrin
Hi,
I have to make a copy of my db with all users/passowrds/views/sp...etc to same server with diff.name.
What is the best way to do this and how long will it take to transfer around 5gb of data.
Thanks
How can you copy a database from one server to another without taking it down?
I know I can detach the database and then copy it thru the file system, but how can it be done with the database running?
Thanks
what would be the best way to copy a database from one server to another.
Thanks.
I wanted copy a database from one server to another server.So far I'm doing this is by detaching and attaching the DB.But when i tried to restore the DB on the new server its giving some error. Is't a good process to detach and attach.PLease let me know if there is any better way of doing this.
View 5 Replies View RelatedI need to move a database from sql 2000 to sql 2005. My research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio. Are there step by step directions for this or can someone tell me how to do this?
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. Thanks
when i'm copying my database from one server to another server the package is faling with the follwoing errors
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 28000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'mmmservicestart'.
Step Error code: 80004005
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:700
What is the exact problem with these errors and what should i do to overcome this.Can anyone help me in this regard.
Thanks.
Hi to all,
i would like to know how i can solv e my problem.
I have two databases, one is a work in progress(from now WIP) and the other is a container about all the data (ALLDATA). The databases have the same structure, the first is stored in a MSDE 2000 and the second in a SQL Server 2000.
When something happen, i copy a part of data from the WIP to the ALLDATA and i clear the WIP from the copied data.
Now i'm using the insert into instruction for each table.
Is this method right to work?
Can you suggest me something differen?
Thank you.
Emanuele
I have copied a Database from one server to another server by detaching and attaching.Everything looks fine but i didn't get the logins associated with the DB onto new server.
How can i get those logins into the database on new server.
Thanks.
Hi
How can i copy database to another name.
I have acct database now i want to copy acct database to oldacct database. How can i do this.
thanks
asm