Restoring DB For Files...
Jan 22, 2004
Hi
In order to copy a database from a server to a server, I cant detach the database as there are many users connected to it. The transaction log is large file while the data is about 46 MB, I made a backup for the data file only, zipped it and copied it to the target server.
Ex: the name of the source DB on the source server is x, the file I took as a backup for is x-data
From the Enterprise Manager, right-click the x DB, All tasks, Backup database, in the backup option click the File and Filegroup option and chose the primary file group with x_data file.
Then, I created a new database name it y with y_data , y_log files on primary filegroup. I want to restore my backup to have the DB x on my target server,
I tried:
RESTORE DATABASE y
FILE = 'y_data',
FILEGROUP = 'primary'
FROM disk= 'E:VMSDBFiles est'
GO
But it did not work. Any help?
Thanks,
View 1 Replies
ADVERTISEMENT
Jun 28, 2000
Hi All
I have taken ownership of a SQL 7 Server that has many DB's with each DB's devices spread all over the 6 disks that exists in the Server.
e.g.
DB - Reference has
Data devices on D (20mb), E(40mb) , F(60mb) drives
and Logs having a similar spread.
Now what I want to do is to restore a full backup of the database into a new db but with only one data device and one log. So that basically the server is tidied up. The reason the devices are spread is not due to performance reasons.
The QUESTION IS : How do I restore a full back up of a db with many devices in to a NEW db with only 1 data device and 1 log device.
Thanks
Steve
sfarmer@edfman.com
View 2 Replies
View Related
Aug 7, 2000
Hi,
I restored the data files .MDF , .LDF files from my nt backup. Now i want to apply these file to a database. How i have to apply?
Do i need to create new database and attach these files or how i have to do?
I have no idea, how i have to work on it?
pls suggest me.
Thank you everyone.
--Devi
View 1 Replies
View Related
Feb 14, 2006
I recently was given control of an SQL server that stop working.
We had no past backups of the database and the only file we had was the .mdf
I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the
C:program filesmicrosoft sql servermssqldata
when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??
I even tried
sp_attach_single_file_db @dbname = 'SWATraining',
@physname = 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf'
but reason this error
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
View 4 Replies
View Related
Dec 6, 2007
Hi,
I'm making backups of the database by first making a full backup and then differential backups. The differentials are backed up to separate files.
Restore of the full backup works fine, but I can't restore a differential backup. In Management Studio Express, I first do a full backup restore with option NO RECOVERY and then try to restore a differential backup. But this failes with the message:
"This differential backup cannot be restored because the database has not been restored to the correct earlier state."
Is it possible to restore a differential backup that is backed up to a separate file?
View 8 Replies
View Related
Nov 25, 2006
I have some bookkeeping files that were saved using win98 backup that i need to restore to my accounting program on my winxp computer.. i have no idea how to do that. The files that are backed up belong to the same program on both the win98 and winxp.
View 1 Replies
View Related
Dec 1, 2014
Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.
The server died - so I attached the disk array to a new server - and all the DB data files are visible there.
I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.
I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?
View 9 Replies
View Related
Mar 5, 2007
Alright, here's the deal. I'm testing some backup/restore strategies, and hitting a (slight) sticking point.
We've got collections of database and log backups created by the usual maintenance plans on a 2000 Enterprise machine. I'm trying to run through a restore onto a new 2005 machine (Developer Edition on my test workstation) using the collection of .bak and .trn files copied from the 2000 server. When I try to restore to a new database on 2005 via SSMS, and select all the .bak and .trn files for the restore, I get the ol' "The volume on device '[trimmed]' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set." error.
I'm assuming this just means that SQL Server can't verify that these log backups are in fact part of a functional "set", even if they aren't part of a traditional backup media set. Is there any way to tell SSMS, "It's okay man, just restore the database from these files, in this order - trust me," or is the only solution restoring every individual log file one at a time? (Which seems to work fine, though is a tremendous pain with any more than a few log backups.) Seems like there ought to be a good one-shot method to restore a bunch of backups to a different server, and I'm just not finding it.
View 4 Replies
View Related
Mar 25, 2013
i wanted to test routines using my local sql server engine and thought I could backup the mdf ldf file on the true network sql server in which I am listed as a dbowner with full permissions. In fact i can add users for the at db etc.The backup allows me to browse to a local folder
with the following being the default C:Program FilesMicrosoft SQL ServerMSSQL10_50.CMS_PROJECTMSSQLBackup
i name the file and it executes properly but when i try to restore that file to my local db that path is not even visible to me.In fact when i use exploreer I cannot even find the folders after Microsoft SQL Server!I search for .bak but nothing.How can I see that folder construction in the sql browser but never in explorer -
View 2 Replies
View Related
Nov 19, 2007
Hi,
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
Thanks,
View 5 Replies
View Related
Jan 9, 2007
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
Any advice on how to get this thing finished out?
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
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
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
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
Oct 1, 2004
I am wanting to reduce the amount of Virtual Log Files I have. In reading through the Online Book Documentation, I realize that I have forgotten to move the Transaction Log Files to a different drive. Now that the server is in production, I wanted to get some input about the best way of making this change.
Can I just change the directory the log files are being written to in the DB properties without having any adverse problems occurring?
View 2 Replies
View Related
Sep 17, 2004
Hi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!
View 2 Replies
View Related
Dec 11, 2005
I need to write codes to access many image files and wave files and display them on the webpage. Both of these files are stored on the server (as many experts in this forum adviced) and their locations are in the SQL server database. About the wav files, I think I will have to convert them to MP3 first for fast delivery on internet. Since I am a newbie, any help would be appreciated.
Thanks,
View 14 Replies
View Related
Jul 20, 2005
I have a problem with bcp and format files.We changed our databases from varchar to nvarchar to support unicode. Noproblems so fare with that. It is working fine.But now I need a format file for the customer table and and it is notworking. It is working fine with the old DB with varchar, but withnvarchar I'm not able to copy the data. The biggest problem is, that Igot no error message. BCP starts copying to table and finished withouterror message.This is my table:CREATE TABLE [dbo].[Customer] ([ID] [int] NOT NULL ,[CreationTime] [datetime] NULL ,[ModificationTime] [datetime] NULL ,[DiscoveryTime] [datetime] NULL ,[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,[Class] [int] NULL ,[Subclass] [int] NULL ,[Capabilities] [int] NULL ,[SnapshotID] [int] NOT NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOTNULL ,[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AINOT NULL ,[LocationID] [int] NULL ,[MirrorID] [binary] (16) NULL ,[DeleteFlag] [bit] NULL ,[AdminStatus] [bit] NULL) ON [PRIMARY]GOand this is the format file:8.0131 SQLINT 1 12 "#~@~#" 1 ID ""2 SQLDATETIME 1 24 "#~@~#" 2 CreationTime ""3 SQLDATETIME 1 24 "#~@~#" 3 ModificationTime ""4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""5 SQLNCHAR 2 510 "#~@~#" 5 Name_SQL_Latin1_General_CP1_CI_AS6 SQLINT 1 12 "#~@~#" 6 Class ""7 SQLINT 1 12 "#~@~#" 7 Subclass ""8 SQLINT 1 12 "#~@~#" 8 Capabilities ""9 SQLINT 1 12 "#~@~#" 9 SnapshotID ""10 SQLNCHAR 2 510 "#~@~#" 10 CompanyNameSQL_Latin1_General_CP1_CI_AS11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountrySQL_Latin1_General_CP1_CI_AS12 SQLINT 1 12 "#~@~#" 12 LocationID ""13 SQLBINARY 1 33 "#~@~#
"13 MirrorID """#~@~#" is the field terminator. We have a lot of text files with allkind of charachers in it. So we think this is a set that will neveroccur in our files.Thanks for your help!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jun 14, 2007
Hi All,
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
Thanks in Advance
Gaurav Matkar
View 5 Replies
View Related
Jan 21, 2006
Is there a way to do this with some sort of task ?
View 7 Replies
View Related
Nov 8, 2006
I'm using script task to rename files inside foreach loop files,code like shown below
Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))
dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "" & newname
Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"
I tried to use file system task to perform that,but failed either
Rename file operation in file system only can rename a file in a specified location,who can help me?
TIA
View 1 Replies
View Related
Sep 3, 2007
Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
View 1 Replies
View Related
Oct 18, 2000
Hi, I am restoring a database as a complete step. The database icon, under the Sql server Group in the Sql server enterprise manager, turned into gray and got a word (loading) infront of the database name, what does that means?
Ali
View 2 Replies
View Related
Nov 1, 1999
I am just curious to know whether it's possible to restore a SQL 6.5 DB from a DB device file. For e.g. i have a device file abc.dat which contains a db. Now thru this device file can i restore the data on xyz database.
View 3 Replies
View Related
Jun 2, 2003
Hi All,
I'm trying to restore a database to a completely new database from an exsiting full backup.
On the backup device there are two full backups from different dates.
How can I specify which one of the two backups I want use for my restore (The only difference is the date ??
Thanks,
View 4 Replies
View Related
Feb 27, 2002
I had SQL win2k running on a machine and had 2 dbs. unfortunately the hard drive failed (ntldr crashed). i was able to copy the contents of from the old drive (so i have the DB files) but no backups.
Is there any way to import this data into a new DB?
View 1 Replies
View Related
Aug 18, 2005
Can you restore a backup (bak) file to a different server? E.g. client gets server nicked, they have bak files, can I run a restore to my sql server?
I can see the with MOVE option, but I can't see how to attach (to the new server) if the database wasn't detached (from the nicked server).
View 1 Replies
View Related
Sep 24, 2007
i am getting the following error when i try and restore a datbase
The log in this backup set begins at LSN 110000000011300001, which is too late to apply to the database. An earlier log backup that includes LSN 102000000188100001 can be restored..
View 1 Replies
View Related
Feb 4, 2008
while iam restoring my back up i got this error message
Msg 3117, Level 16, State 1, Procedure res, Line 26
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Procedure res, Line 26
RESTORE DATABASE is terminating abnormally.
View 13 Replies
View Related
