Hello,
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
i tried to copy the database manually by using sqldmo
here r my codes:
On Error GoTo CopySQLDB_Err
'Create Objects
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
'Connect to the server
sql.Connect strSrv, strLogin, strPwd
'Set the database object
Set db = sql.Databases(strDatabase, "dbo")
'Determine the location of database files, primary and log
strMDFfilePath = db.PrimaryFilePath
strMDFfileName = _
Trim(db.FileGroups.Item(1).DBFiles.Item(1).Physica lName)
strLOGfile = Trim(db.TransactionLog.LogFiles(1).PhysicalName)
'Clear out the database object
Set db = Nothing
'Detach database to allow copying
sql.DetachDB (strDatabase)
'Copy database files to new names
FileCopy strMDFfileName, strMDFfilePath & strNewName & ".mdf"
FileCopy strLOGfile, strMDFfilePath & strNewName & "_log.ldf"
problem comes when i tried to attach the db using enterprise manager, error come out :
"passed to log scan in database "SIPS' is invalid" come out ...what is the problem of this?
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
Hello,
i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok?
Autoclose = true and recovery model = simple.
Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections).
Can i copy at any time even when transactions are active?
I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.
This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..
Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?
Is this enough to have a valid backup? Backup would be an attach of the .mdf file.
Or must i use the BACKUP and RESTORE stuff? Why?
If so, for what reason is the AUTO CLOSE property there?
Regards,
Markus
I have to perform disk maintenance on current drive - Drive 'D' where it has sql data (mdf file) and I have added new drive - Drive 'E' By the way Drive 'C' have the program files for SQL Server 2008 R2 What is the correct process to transfer sql data (mdf file) from Drive 'D' to Drive 'E' and later remove Drive 'D' from the server.
View 12 Replies View RelatedI have a file in Fire bird Database (30 GB with .ydb extension). which needs to be restored to SQL Server. I Have created a linked server and done it but it is taking very long time to update the records.
View 8 Replies View RelatedHi,
I'm new to SQL Server 2005 SSIS. I'm trying to do something very simple, but I cannot figure it out, PLEASE HELP!
I have a flat file, which I read and then insert the data in a database table, that works fine. The problem is that I don't want to insert duplicate records. For example; if I run the package again, it will appent to the table. What I need to do is that if the package runs again, check if the record already exist, based one two columns, date and hour, and do not insert the record.
Thank you,
Aldo
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
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 RelatedHow do I transfer/copy the stored procedures in my Test DB to my LIVE DB? IT won't allow me to export keeps giving me an error.
View 4 Replies View RelatedI 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.
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
Hi~,
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
- any other guide lines
hi,
I need to transfer my database from one computer to another. I am using SQL SERVER 2000. So I made the script of the database and transfered to other computer. Now when I try to run the script it gives me error that (mydatabase name).MDF file missing. So I tried to copy the .MDF file from my computer. but it says that access is denied and sharing volilation. Can anyone please help me that how can I copy the .MDF file and remove the sharing volilation.
Can anyone give best way to copy file from one server to another in the network in an automated way?
Di.
how can i create a mdb file which represents a current database in sql server managment studio? i thought this would be obvious but it's not..
View 1 Replies View Relatedhi guys,
anybody know the syntax in SQL to copy file to other server.
we want to copy our db backup files automaticaly to other server.
thanks
-wei
The Sql Server database can only see the local drive.
I would like to set up a batch file that will copy a SQL Server
backup file from the local drive to the network drive. I would
like to append the file date to the end of the copied file. I
assume a batch file can accomplish this but I am new to batch
file writing. Does anyone have code that they already created
for this sort of task??
Thank you!
I need to copy files from one machine to another machine. It should be done automatically. Could you please give me a suggestion. I have no idea about this. It should write a script or program. But I have no idea yet,
Thanks
i have other question here, i would like to copy file from server to a PC. I create a script (.dat file):
xcopy \serverNameUsersyyu
\ComputerNamecyyu
Can you guy help me? Many thanks.
hi
i am using isp_backup store procedure to get the daily backup of database and this is work fine but i have copy the database file in network sharing folder to use
following cmd
EXEC master..xp_cmdshell 'D:filename M:foldernam'
M: is network folder to window2003
but their no copy this folder
thank
I have a windows service that connects to a regular sql server 2005 database and basically bulk copies data into a SQL express database. Afterwards, I just want to copy the MDF file into a different directory. I keep getting the "Process cannot access file because it is being used by another process" error. I've tried changing the connection strings but nothing seems to work. I'm closing the connections in the code as well. Here is the code. Any thoughts or help would be appreciated. RefreshDB calls Sync 3 times and if all three calls are successful, it attempts to copy the database file to the specified location. This is where I get the error.
Public Function RefreshDB() As Boolean
Dim success As Boolean = True
Dim tables() As String = {"Job", "Equipment", "PMScheduled"}
For Each tableName As String In tables
If SyncTable(tableName) = False Then
success = False
Exit For
End If
Next
If success Then
'copy the new database to the target directory
Try
File.Copy(My.Settings.DBFilePath, My.Settings.TargetDirectory + "EMField.mdf")
Catch ex As Exception
My.Application.Log.WriteEntry(ex.Message + "(RefreshDB)", TraceEventType.Critical)
Return False
End Try
End If
Return success
End Function
Private Function SyncTable(ByVal tableName As String) As Boolean
Dim reader As SqlDataReader
Dim sourceViewName As String = String.Format("EMField{0}View", tableName)
Dim connectionString As String = My.Settings.EMFieldConnectionString
'insert the path to the database into the connectionstring
connectionString = connectionString.Replace("[DBFilePath]", My.Settings.DBFilePath)
Try
'clear the target table first
Using targetConnection As New SqlConnection(connectionString)
Using truncateCommand As New SqlCommand(String.Format("TRUNCATE TABLE {0}", tableName), targetConnection)
targetConnection.Open()
truncateCommand.ExecuteNonQuery()
targetConnection.Close()
End Using
End Using
'create a datareader from the source database
Using sourceConnection As New SqlConnection(My.Settings.EMLiteConnectionString)
Using readCommand As New SqlCommand(String.Format("SELECT * FROM {0}", sourceViewName), sourceConnection)
sourceConnection.Open()
reader = readCommand.ExecuteReader
BulkCopy(tableName, reader, connectionString)
reader.Close()
sourceConnection.Close()
End Using
End Using
Return True
Catch ex As SqlException
My.Application.Log.WriteEntry(ex.Message + "(sync)", TraceEventType.Critical)
Return False
End Try
End Function
Public Sub BulkCopy(ByVal tableName As String, ByRef reader As SqlDataReader, ByVal connectionString As String)
Try
'bulk copy from source to target database
Using bulkCopy As New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = tableName
bulkCopy.WriteToServer(reader)
bulkCopy.Close()
End Using
Catch ex As SqlException
'throw exception back to calling sub
Throw ex
End Try
End Sub
Hi Guys,
What approach should I use to copy content of a text file. Is BCP capable of doing this? How about SSIS?
Example of the text file's content:
Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90
In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.
Regards,
Lars
Hi,
I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:dest and then upzip the files. Once this is done, sent an notification email to user@mydomain.com.
Thanks,
According to the help for SSIS, one method of deploying an SSIS package
to a SQL Server,
http://msdn2.microsoft.com/en-us/library/ms137565.aspx, is to use the
File...Save a Copy of <package file> as... menu option.
I don't have that menu option at all. And yes, the package is in
focus. My save menu options are simply; Save Selected, Save
<package file> As... and Save All.
I am using Version 9.00.1399.00 of the SSIS Designer.
At one time I did have the Management Studio's CTP installed.
However it was uninstalled before installing the tools from the
Standard Edition. (it would seem like not completely however)
Your help would be greatly appreciated. Thanx much.
p.s. Almost forgot to mention... I am already aware of using the
DTSInstall utility as a workaround. It should be noted, however,
that despite enabling the "CreateDeploymentUtility" property, the
DTSInstall.exe is not copied to the binDeployment directory.
What is the syntax for using xp_smdshell to copy a file from 1 server to another?
Our Report server is restored from our production server and I want to copy the .dat
file from the production server to a folder on the report server.
Is it possible to Export a SQL passthrough query to a text file
Like
@A1 int
bcp '"select * from bank where id = @a1" query out ....... "
Or I have to use sth. like SQLDMO ???? But I dont know how to do it ..
Please Helpppppp
Edwin
Hi!
What should I do to copy backup file from remote server directory to my machine?
Thank you,
Elena.
Hi,
I am creating a job that runs this command:
EXEC xp_cmdshell 'copy f:dados_sqlmssqlackup
ecom_tb_basico.bak \stalingrado_2c$ emp',
The result of this job is:
"Access is denied.
0 file(s) copied.
NULL"
But I am running this command with sa user.....
Wich kind of permission is missing to execute this copy?
When I execute the same command to copy the backup from the server to itself, it works fine!!!!
Does someone have an idea to solve this problem?????
Hi,
I'm looking to use ActiveX in a DTS to copy a file from on elocation to another.
I have the code for this (attached below kindly supplied by a colleague), so that's cool.
dim filesys
set filesys=CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("D:CreditsCredits_MTD.mdb") Then
filesys.CopyFile "D:CreditsCredits_MTD.mdb", _
"C:CreditsCredits_200602.mdb"
End If
But what I'm looking to do is go one step further.
As you can see, it renames the file to yyyymm, but this is hard coded, and I'm looking for a coded solution so I can rename the file with yesterday's date.
Can anybody help me please?
Hello:
I want to copy one table not whole database to text file. How to do it? Using DTS could not allow me to select the specific table.
Thanks,
Snow:rolleyes:
sql server 2000
when I run under SQL in sql analyser
exec master.dbo.xp_cmdshell 'c:mssqldata est_data_mdf \server1data /y'
return : invalid drive specification
but I can run 'c:mssqldata est_data_mdf \server1data /y' from CMD
any solution?
Thx
Hello All,
I want to copy flat file(.txt) from server to another server.
server A(Source Folder) ====> server B(Achive Folder)
Can I do it?
Please, help me.
Thank you very much.
Chonnathan