Attach Db With Same Files As Existing Db
Jun 3, 2004
In SQL Server 2000, I would like to attach a Database that is the file of the Database on another system. I made a copy of my Client's Database at his site. Now I would like to attach it to my server and then copy the data from my Client's DB to mine.
The only problem is they have the same file names, so when I try to attach the copy I get an error message. Is there any way to change the name of the data file and log file that a database points to so that I can attach it to my Server?
Thanks in advance for your help!
View 1 Replies
ADVERTISEMENT
Mar 30, 2006
Hi All,
Can any body tell me that how we can attach IDENTITY property to an existing int column
View 1 Replies
View Related
Jan 1, 2006
I have a data file, call it Data.mdf, that I am accessing through Visual Studio. It does not show up in SQL Server 2000 Enterprise Manager.
How can I get it assigned to, or imported into, the local server so I can manage it?
Thanks
Glen
View 6 Replies
View Related
May 26, 2008
Normally we create a new table for tabler partitioning, like:
Code Snippet
CREATE TABLE [dbo].[Sales]
(
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[SalesDate] [datetime] NOT NULL,
[col_01] varchar(50) NULL,
[col_02] varchar(50) NULL,
..
) ON [ps_Sales](SalesDate)
GO
with
Code SnippetCREATE PARTITION SCHEME [ps_Sales]
AS
PARTITION PFN_Sales TO ([FG_20080501], [FG_20080516], [FG_20080601], [FG_20080616],
[FG_20080701], [FG_20080716], [FG_20080801], [FG_20080816],
[FG_20080901], [FG_20080916], [FG_20081001], [FG_20081016])
But what if I have [dbo].[Sales] existed in db, that have attached to [PRIMARY]?
Alter table?
or I need to create a temp table first?
View 1 Replies
View Related
Apr 24, 2008
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
View 16 Replies
View Related
Nov 1, 2007
Using Microsoft SQL Server Management Studio Express when trying to attach a database, and clicking the Add button, the folder hierarchy appears, but access to personal folders is restricted. How can this be fixed?
Thanks,
Grant
View 5 Replies
View Related
Aug 19, 2002
First, let me say that I have already reviewed information posted by experts within the chat area.
Someone completely deleted my SQL Server 7 database. I retrieved the .LDF and .MDF files from a network backup. Now I am trying to attach the database. I have tried this without creating an instance of the database by using the attach stored procedure to attache the .LDF and .MDF files I retrieved from the network backup. Also, I have tried it by creating an instance of the database and doing a detach of the newly created db .LDF and .MDF files and an attach of the .LDF and .MDF files I retrieved from the network backup. Neither of these approaches have worked.
Here is what I have tried in the Query Analyzer, but to no avail:
For the following example, I created a database instance called 'qarun_diamond_48_brett' with brand new .ldf and .mdf files and then tried to detach and then attach the .ldf and .mdf files from the network backup:
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett_log.ldf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett.mdf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
I tried the following attaches of the retrieved/recoverd .ldf and .mdf from the network to the newly created db instance. That didn't work, so I tried attaching to a db that had not yet been created.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2_log.ldf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
If I use a different db name I get the following error:
Server: Msg 5105, Level 16, State 13, Line 1
Device activation error. The physical file name 'qarun_diamond_48_brett' may be incorrect.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2.mdf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
I have also tried these statements wtih the EXEC sp_attach_db and EXEC sp_detach_db commands from within the Master db in Query Analyzer.
Any help would greatly be appreciated.
Thanks,
-Brett
View 2 Replies
View Related
Dec 8, 2006
Is there a way to send an e-mail from SSIS that attaches all the files in a specified folder?
View 5 Replies
View Related
Apr 13, 2008
Just to verify that this was an issue, I downloaded web developer 2008 and I do not experience this same problem.
BUT when I go to add a dataset in vs2005 for an asp website - all my db files come up in the dialogue box but everyone that click (every db file) I get "This file is in use. Please enter a new name or close the file that's open in another program."
But, like I said, I downloaded 2008 and it does not occur. Plus I KNOW that the db's are not being used. Can someone give me a remedy to this?
View 1 Replies
View Related
Feb 27, 2015
1) What is the current version of SQL Server Express?
2) HOw much SQL Server Express costs (figure about 500 branch servers)
3)Are there any Tools from Microsoft to convert Access 97 directly to SQL Server express, and how much do they cost?
4)Server Hardware requirements to run SQL Server Express - disk size, memory size, security settings, pre-requisite, service needed.
5)Maximum capacity / capabilities of SQL Server Express-max # of simultaneous users,tables,rows,database size.
6)Any installation instruction for SQL Server Express.
View 4 Replies
View Related
Mar 15, 2007
Can multiple instances of SQL 2005 Express attach to the same database files on a network share? I have seen this done before with MSDE where the database files are stored on the server, but instead of having a SQL server running on the network and then connecting to it, only the database files exist on the network share and the users connect through MSDE running on the local machine. Is this possible with SQL2005Express? I do not have the ability to share an SQL instance from one workstation to another nor do I have the ability to install an instance on the corporate server. Is it as simple as creating the database and storing the files on the share then attaching the database to the SQL Instance on each workstation?
View 3 Replies
View Related
Aug 20, 2015
I have an SSIS package in VS 2010 that uses flat files to load database tables. I would like to check for the flat files existing before continuing to run the package. The flat files each have their own connection manager. I was wondering if I could use the connection managers to determine the file names instead of creating a Script Task and hard-coding each of the file names to check.
View 4 Replies
View Related
Feb 28, 2008
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
View 7 Replies
View Related
May 7, 2007
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.
In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.
Linda
Here is the old VBScript Code:
Public Sub Main()
Option Explicit
Function Main()
Dim MovementDataDir
Dim MovementArchiveDataDir
Dim MovementDataFile
Dim MovementArchiveDataFile
Dim FileNameRoot
Dim FileNameExtension, DecimalLocation
Dim CurMonth, CurDay
Dim FileApplicationDate
Dim fso ' File System Object
Dim folder
Dim FileCollection
Dim MovementFile
'======================================================================
'Create text strings of today's date to be appended to the archived file.
FileApplicationDate = Now
CurMonth = Month(FileApplicationDate)
CurDay = Day(FileApplicationDate)
If Len(CurMonth) = 1 Then
CurMonth = "0" & CurMonth
End If
If Len(CurDay) = 1 Then
CurDay = "0" & CurDay
End If
FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)
'=====================================================================
' Set the movement data directory from the global variable.
MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value
MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value
fso = CreateObject("Scripting.FileSystemObject")
folder = fso.GetFolder(MovementDataDir)
FileCollection = folder.Files
' Loop through all files in the data directory.
For Each MovementFile In FileCollection
' Get the full path name of the current data file.
MovementDataFile = MovementDataDir & "" & MovementFile.Name
' Get the full path name of the archive data file.
MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name
DecimalLocation = InStr(1, MovementArchiveDataFile, ".")
FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)
FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)
MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension
If (fso.FileExists(MovementDataFile)) Then
fso.CopyFile(MovementDataFile, MovementArchiveDataFile)
' If the archive file was coppied, then delete the old copy.
If (fso.FileExists(MovementArchiveDataFile)) Then
fso.DeleteFile(MovementDataFile)
End If
End If
Next
fso = Nothing
folder = Nothing
FileCollection = Nothing
Main = DTSTaskExecResult_Success
End Function
View 6 Replies
View Related
Nov 21, 2007
An attempt to attach an auto-named database for file C:WebApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
My web.config file, the connection strings
<connectionStrings>
<add name="POAdatabaseConnectionString1" connectionString="Data Source=sss;Persist Security Info=True;Initial Catalog=POAdatabase.mdf;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
</connectionStrings>
I cann't really find the statement for attaching aspnetdb.mdf. I don't where to find it. Is there any other web.config file in the C drive? Thanks for help!
View 3 Replies
View Related
Jul 30, 2007
I have a huge MDF File - 120 GB File (Had setup as 1 MDF initially) -- Did not anticipate that the DB would grow to that size!!
Anyways.. I heard that the general performance woul grow if i had them as "File Groups"..
Is there anyway - to split the existing MDF file into Mutliple files as a File Group?
Where should i start? Can someone please direct me..
View 1 Replies
View Related
Aug 8, 2007
Hi,
my problem is like this problem i guess
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477517&SiteID=1
The problem is SQL server could not attach files which are not on the same server?
i have a backup file copied on my machine, and not being able to restore because SQL attempts to access the FileList from the original sourec location, which is not present / over the network.
how do i tackle this....
please help
Regards
Mave
View 5 Replies
View Related
Nov 14, 2007
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies
View Related
Aug 13, 2007
Hi All,
I would like to restore database using RESTORE DATABASE ... REPLACE command.
If database exists already and has any open connections this command will fail.
I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command.
I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Please advice me how to do it.
Thanks in advance,
Roman
View 3 Replies
View Related
Jan 9, 2015
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies
View Related
Mar 13, 2008
In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
View 3 Replies
View Related
Feb 24, 2015
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
View 6 Replies
View Related
Oct 8, 2007
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
View 20 Replies
View Related
Mar 17, 2002
Help!
I did a sp_detach_db of a database, then deleted the log file because it was too big. Now, when I try to sp_attach_db, or even sp_attach_single_file_db, it will not re-create the db.
View 3 Replies
View Related
Aug 19, 2002
When I tried to attach a database from Enterprise manager, I got "error 3624, location: scanrid.cpp:321 Expression: m_len != 0 spid 51 proseccid 436......" The files are all there, but it seems there are problems of activating files, or the files are crashed. Any one got idea? Actually, we just need one table in that database. Is there any way to save a table from mdf file or ndf file? Or repair files?
View 1 Replies
View Related
Jan 6, 2006
I have database DB1 on Box1 and I have the full backup of that database and also have data file and log file.If I want to restore the database on a diff box How can do that.
1.How can I restore the Database?
2.How ca I attach the data file to the database on new Box?
Thanks.
View 1 Replies
View Related
Jan 16, 2004
I received three Files .mdf .ldf .ndf and a text how to use attach
but i always receive an error message "database is write protect ......."
What I do wrong ?
bye
View 9 Replies
View Related
Jul 23, 2005
I am thinking about replacing the INSERT data scriptfiles that I have with XML files. This way I can open the XMLfile using an XML Editor and see the values in a GRID andmake changes easier.Do you see any problem with this approach?I managed to put together some code that is exportinga SQL table with its data to an XML file and also a codethat reads the XML file's data and inserts it into a table.Now I am researching on XSD, td:datatype, DTD...(I am new to XML) in order to figure out how I canuse a single xml file that will hold both the sql serverfields, the datatypes and their values.If you have links to some sample code that has anythingto do with the datatype export and import I am workingon, can you please share them with me?Most importantly what do you think about the idea of usingXML files vs sql scripts?Thank you
View 4 Replies
View Related
Jun 25, 2015
I have a scenario where I need to convert RDF files to PDF files? may I know is this achievable in SSIS - writing C# code?
View 6 Replies
View Related
Dec 16, 2007
I've created a database inside my ASP.net project, the database is in the App_Data folder as exepcted.I can't run the aspnet_regsql until I have attached it using SQL Server Management Studio, but when I try to attach the database it doesn't let me navigate through my folders properly. The database is in C:UsersWardieDocumentsFirstProjectApp_Data but SQL SMS only lets me navigate to the folder C:UsersWardie and no further for some reason, meaning I can't attach the database and can't use the aspnet_regsql service to create the membership tables.Does anyone know how to fix this?
View 3 Replies
View Related
Nov 26, 2001
Hi guys,
I had user who would like attach trial database and de-attach database when he do development. I do not want him had any other permission except the database he attach. I give him database creator role. But it did not seems work. I have to give him database creator role and system admin role also. Any one have better ideas about this?
Thanks for help
Helen
View 1 Replies
View Related
May 6, 2002
For a test I detached a database, copied the ldf to a new location, and re-attached successfully. OK, now I want to use the orignial ldf, so I detach, and when I try to attach to the origninal ldf I get:
Server: Msg 3624, Level 20, State 1, Line1
Location: recovery.c:2693
Expression: seenCkptEnd
SPID: 8
Process ID: 109
Connection Broken
Any Ideas on what happened and how to reattach to my original log file?
Rob
View 1 Replies
View Related
Sep 7, 2005
I know the chances of getting a positive answer on this are remote but here goes:
I have a db that I need to move to a new server (that isnt on the same network !)
Firstly tried, backing up the DB, copying the backup to various removable media, copied it onto the new server, then tried restoring. The restore almost completes, then comes up that backup is incomplete !
If I restore the DB on the original server from the same backup file it works fine !
Second method, detached the DB, copied the mdf file to a memory stick, copied onto the new server, attempted to attach the mdf file and get:
I/O error (bad page ID) detected during read at offset 0x00000003f80000 in file ..........mdf
Error: 823, Severity: 24, State: 2
If I re-attach the file on the original server this works !!!
My next step will be to get the two servers connected over the network, so that I can use DTS, but this is not ideal and would like to avoid this if possible.
If anybody has any suggestions, I would be eternally grateful.
p.s. I have tried restoring/attaching on other servers in case it was a problem with the new server, but get the same problems.
Regards
Tom
View 4 Replies
View Related
