I am developing a .NET 2.0 application that uses Sql Express as its backend.
I use OleDb Provider to connect to Sql express.
One of the options in the app allows the Admin to create a Backup of the database. As part of this process, I write a log entry to one of the tables in the database, commit & close the connection, and then copy the physical database(.mdf) file to some pre-determined location using the following instruction (in VB):
My.Computer.FileSystem.CopyFile("Path to Database File", "Destination Path")
It throws an exception saying that the file is in use. Upon checking, I found that the file has been locked-up by the Sql process.
I ensured that my app closes all connections to the database, that there is none other trying to connect to it, and then again tried to execute the above instruction in my app, again raising the same error.
However, when the app terminates, then I can easily copy the databse using explorer. I also used the OleDb method ReleaseObjectPool(), but the problem persisted.
So, how can I copy the database in such a situation from within my app??
This is a pretty basic question, but I've generated a backup file for one of my SQL 2000 databases, and I need to copy the backup file to another computer so I can burn it on CD. Whenever I try to copy the file, I get an error message saying that the file is in use by another process and can't be copied. I have tried detaching the database, shutting down Enterprise Manager, stopping the MSSQL service, but all to no avail. There must be a way to do this, but I can't get it to work. Does anyone have any ideas?
Hi, I'm trying to copy one SQL Server Database into another SQL Server DB within the same server. I learnt a bit about this copying, that it copies only the Database structure, the Tabels, constraints view stored procedures etc etc. Is there a tutorial where I can get clear instructions to do this. I just need the table structure without any data to be copied into this. Have even tried exploring creating and executing DTS packages but cudn't get much help with this. Any help wud be really appreciated.
Does anyone know how to copy a database from one server to another? I want to backup a production database and restore it to a different server (a test server) as a test.
I used a sample from the MCDBA study book and all I got was two copies of Northwind on the same server. Any idea what I did wrong? Seems like you should be able to backup on one server and restore on another. Here's the code I used:
BACKUP DATABASE Northwind TO DISK = 'Sd-appsmssql7DataNwind.bak' RESTORE FILELISTONLY FROM DISK = 'Sd-appsmssql7DataNwind.bak' RESTORE DATABASE Northwind2 FROM DISK = 'Sd-appsmssql7DataNwind.bak' WITH MOVE 'Northwind' TO 'Sd-sqlmssql7DataNorthwind.mdf', MOVE 'NorthwindLog1' TO 'Sd-sqlmssql7DataNorthwindLog1.ldf' GO
I need to take a copy of a database present in a SQL server to other SQl server using VB.net code. I can make it out with wizards but i need the query to execute it thru vb.net.
We recently got a new SQL Server 2000. I'm not really a SQL/Network admin but I was tasked to migrate some of our databases in the SQLSVR7 to SQLSVR2K.
I tried using DTS EXPORT but getting errors. Is there a better way to do this?
Im running SQL Server 2005 Express with SQL Server Management studio installed on my laptop. After updating my database, how do I copy the entire contents to the main server in the office, which is running windows server 2003 and SQL Server 2000 Developer Edition. And is it possible to do it all from my laptop?
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
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.
I'm new to database development and have created a database-driven website which now needs to be uploaded to the client's hosting company server. What is the easiest way for a newbie to upload a copy of the databse to the host. Thanks in advance.
I have a customer who I have set up a SQL Server Express database for. The database was running fine, but I copied it over to my pc over the weekend to update some data. I have copied the data backwards and forwards plenty of times, but this time when I came to copy it back and reattach it on the customers' server I get the following message:
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Attach database failed for Server 'SBSSERVERBKUPEXEC'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 6, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Could not open new database 'ACServe'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
First of all let me explain that I am a complete newbie to this SQL stuff. I am in the process of reading a book, and followed the books suggestions for what I want to do, but it doesn't work. I know this has been covered several times in this forum, but no one post in particular covered exactly what I want to do.
I want to copy the database from the server to my local machine.
First, do I need to have SQL running on my machine in order to copy the database? I do have Enterprise Manager running, does that mean SQL Server is running locally?
Secoond, I tried using the Copy Wizard, but when I get to the target list, my local machine is not listed. How do I get it listed so I can copy the database?
I hope my questions are not too stupid... but then I'd be stupid if I didn't ask questions.
I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).
I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:
Server user 'username' is not a valid user in database '123foo'
(123foo is the first database of all those on the server)
I need have it look only at my database which I have permissions for to copy it. How do I do this?
I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?
I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.
Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.
What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?
This 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!
We have SQL Server 2012 running on Windows 2008 Server. We need to copy five databases from our 'sandbox' to our test server and then to our production server. The database backup file sizes are 3 MB, 20 MB, 344 MB, 645 MB and 17 GB. We are planning on using the backup and restore method since we already have full backups and the scripts to recreate the logins/users/permissions. We believe this method provides more flexibility and control over the process. However, we have a few jobs, maintenance plans and ssis packages.
To get the jobs to the new server instance, the plan is to script out the jobs on the 'sandbox' and execute the scripts on the test server instance. Is this the best or only way to handle the jobs?How to get these maintenance plans to the new server instance? (There is no 'script out' maintenance plan option.) We may have to just recreate them on the new server instance. Is this the best or only way to handle the maintenance plans?
We have a few ssis packages. How to handle getting the ssis packages over to the new server instance (using the backup restore method)? These packages use the Project Deployment Model. Therefore, should we restore the SSISDB or open up the package file using VSS on the new server instance and change the connection information to point to the new server instance.
Just wondering if there is any reason or advantage to use the Detach and Attach method or Copy Database Wizard method? I have read where the Copy Database Wizard method handles the database's dependent objects like logins, jobs, maintenance plans, user-defined error messages and shared objects from the master database. Are there any other move/copy database methods to consider? Just trying to make sure we have thought out everything and are using the best method to copy our databases over to another server instance.
I tried to copy db from server to server by sa user ( sql login) but this error raised and the copy failed
Executed as user: NT ServiceSQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:55:24 AM Progress: 2015-05-11 09:55:24.45 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2015-05-11 09:56:31.87 Code: 0x00000000 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task
An error occurred while transferring data. See the inner exception for details. StackTrace: at Microsoft.SqlServer. Management. Smo. Transfer. TransferData()The Execution method succeeded, but the
[code]....
number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:55:24 AM Finished: 9:56:32 AM Elapsed: 67.892 seconds. The package execution failed. The step failed.
I am not sure I understand the problem I am causing, but I am a beginner!
Here's the situation: I have a table located on MS SQL server database number 1. Said table, which we'll call WIDGET_PRICES, is accessed regularly by my existing source code and has no problems.
At some point, I decide to move operations to MS SQL database number 2 and do a very simple database copy of WIDGET_PRICES from database 1 to database 2 using the Microsoft SQL Server Management Studio.
The end result, inevitably, is that my source code can no longer access the very same table as it is located on the new database server. The code hasn't changed, it's still trying to access WIDGET_PRICES as always. And, from what I see on my screen through Management Studio, WIDGET_PRICES appears just fine.
An example error is the one I just got:
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'YB_ITEMS'.
/yardbark/tampabay/header.asp, line 27
The only clue is that while my transferred tables often look named like "database1.WIDGET_PRICES on database 1, they wind up looking like database2.WIDGET_PRICES on dabase 2.
I include a little more detail and screenshots of the tables in questions at this web page.
While trying to find a folder on SQL an OLE DB error was encountered with error code
0x80004005 (Login failed for user 'sa'. The login is a SQL Server login and cannot be used with
Windows authentication.).
- Start SQL Server Agent Job (Stopped)
- Execute SQL Server Agent Job (Stopped) -------------------------------------------------
The attach/detach route also fails with the same message. Although I create a SSIS proxy, the Copy Database Wzard shows only the SQLServerAgent as the proxy. Why? ---------- Does copy database wizard use OLEDB for the transfer? Does it mean you must have both Source and Destinaton with windows authentication?
When copying data to a remote SQL2K5 destination from a SQL2K5 source database, both using mixed sql server security mode, my job generates the following error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
This occurs after the destination database tables have been truncated and replacement data from the source would begin to copy.
The same process can be successfully completed from the Management studio with a simple data export process. However, when I run the saved package again from the BI interface, I get this error.
My search engine searches have yielded numerous hits of others having the same problem with one microsoft rep indicating it was a bug and would be resolved in sp1. I am working with sp1. Oddly, there is only mention of this in the forums. No KB article from MS addresses the problem and I do not see it addressed elsewhere at sqlservercentral.
It appears that others have switched to Integrated Security and resolved the problem. However, I do not have that option with a remotely hosted database.
Does anyone have any information concerning this problem?
I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).
I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:
Server user 'username' is not a valid user in database '123foo'
(123foo is the first database of all those on the server)
I need have it look only at my database which I have permissions for to copy it. How do I do this?
I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?
Hello, As the heading states, I'd like to copy a database table from VWDE over to SQL SME, where it'll replace its namesake. I've tried the 'attach' method but was denied due to server permissions. Is there another way of doing this, or will I have to delete the database and then run a script to reinstate (annoyingly convoluted)? This would be so much easier if the host supported SQL 2005 Express. Thanks in advance
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET. I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page. Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer. Cheers ~ J
Hi, I have been having fun and games for well over a week now trying to get an xml file copied into an sql file, but still have no joy. I and using the bulk copy to do this and think I am close to solving it but just need a final push in the write direction. In green below is the full source code, I think that the trouble is with the try statement part here:
Try bulk.WriteToServer(xd)
This comes up with the error : Unable to cast object of type 'System.Xml.XmlDocument' to type 'System.Data.IDataReader'
Basically how do I get the XML data into a reader (or IDataReader) format so that the writetoserver command can interpret it
I would be so greatful if someone could help resolve this it is becoming increasingly more frustrating Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim objXML Dim objXSL ' destination table
Dim connectionString As String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionString Dim myNewConnection As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connectionString) myNewConnection.Open()
Dim productreader As XmlReader Dim bulk As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(myNewConnection) bulk.DestinationTableName = "Product" Dim productcount As Integer = 0
Dim settings As New System.Xml.XmlReaderSettings() settings.IgnoreWhitespace = True settings.IgnoreComments = True
Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml")) Using reader As XmlReader = XmlReader.Create(xs, settings) While (reader.Read()) If (reader.NodeType = XmlNodeType.Element And "product" = reader.LocalName) Then productcount += 1 End If End While End Using Response.Write(String.Format("found {0} products!", productcount))
Using bulk bulk.DestinationTableName = "Product"
Dim xd As New System.Xml.XmlDocument() xd.Load("C:Documents and SettingsSimonMy DocumentsVisual Studio 2005WebSitesWebSite1App_Dataxmlfile.xml") Dim xr As New System.Xml.XmlTextReader(Server.MapPath("~/App_Data/xmlfile.xml"))
bulk.ColumnMappings.Add("TDProductId", "TDProductId") ' map First to first_name
bulk.ColumnMappings.Add("name", "name") ' map Last to last_name
bulk.ColumnMappings.Add("description", "description") ' map Date to first_sale
bulk.ColumnMappings.Add("imageUrl", "imageUrl") ' map Amount to sale_amount
bulk.ColumnMappings.Add("productUrl", "productUrl") ' map UserID from Session to user_id
I have a SQL db on my server in internet the server and my DB is online I want to copy mdf file to my computer via FTP but I get an error that file is in use and I can`t copy it
I wanted to know is there any way that i copy information in that MDF fle to another DB on my computer
I have tried doing this with a test table and this is the output NULL Starting copy... NULL 3 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 16 NULL
and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.
I have a VB project which uses SQL Server 2005 Express. When the project first runs, it copies its DB files from the data directory to a directory created on the C: drive. This works fine.
In a later part of the application, it is necessary to copy the DB file to another location e.g. memory stick so that it can be used to merge data onto another PC running the same application.
The data merging works too.
My problem is, despite the fact that it worked okay this morning, I am now unable to copy the DB into a second location (memory stick or location on local disc). I get an exception stating that the file is in use.
I have no other applications running (I shut down Visual Studio and was running the installed version).
What is more, the form that implements the file copying, first closes down all other forms in the application to ensure that nothing is connected to the DB.
Furthermore, the form that performs this task has no DB connections whatsoever - it simply allows the user to select a location, then creates a directory and copies the DB files to it.
If I copy/paste the code plus the FolderBrowserDialog into the first form that copies the original database out of the data directory - it works!
The code for the whole form is below - I would be hugely grateful for any ideas ...
Public Class Export
Private Sub Export_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub NextBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextBtn.Click
'Close all other windows to ensure that the databases are not in use.
Dim Counter As Integer = My.Application.OpenForms.Count
Do While Counter > 1
For Each f As Form In My.Application.OpenForms
If Not (f.GetType Is GetType(Export)) Then
f.Close()
Exit For
End If
Next
Counter -= 1
Loop
' Set the Help text description for the FolderBrowserDialog.
Me.FolderBrowserDialog.Description = _
"Select a location to export the data to e.g. a memory stick. Or, you can export to a local directory and then burn to CD or e-mail to another user."
' Do not allow the user to create New files via the FolderBrowserDialog.
Re SQL Server 2005 ExpressWhen I attempt to copy the master file for backing up, it is being used byanother application, even after shutting down the PC completely and poweringit up again. What can I do to make back-ups?Zach
Hi All;We are going to change our application server. We will copy all of ourDatabase from Mic 2000 Server OS to Mic 2003 Server OS. I found anarticle that how to move all the folders from same OS. My question isthat Would the 2003 OS be a problem when we copy all of the MC SQL 2000folders to New OS ? Has anybody done this before? Could you gimme yoursuggestions please?ThanksASA
How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database. I do not wish to use the sp_addalias as it is available only for backward compatibility. Is there a better way of doing this ?
