Restoring DB With Many Device Files
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
ADVERTISEMENT
Jun 28, 2000
Hi All,
I am trying to restore transaction log using T_SQL:
restore log transfer from transfer_log with file = 2 ,norecovery
and also I was Trying--
restore log transfer from disk = 'c:mssql7ackupransfer_log.dat' with file = 2,norecovery
I am getting this error Message-
Server: Msg 4306, Level 16, State 2, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Any Ideas,Please Let me Know ASAP
Thank You ALL--
View 3 Replies
View Related
Dec 7, 2004
Hello,
Every hour I make a backup of the log files of a database to a backup device. Each backup is appended to this backup device.
Now to restore at a point in time I normally use the following script;
RESTORE DATABASE mcs_carolus
FROM disk='E:sqlserver_databasesmcs_carolus_data.bak'
WITH REPLACE,
NORECOVERY
go
RESTORE LOG MCS_CAROLUS
FROM disk='E:sqlserver_databasesmcs_carolus_log.bak'
WITH FILE = 1, NORECOVERY
GO
RESTORE LOG MCS_CAROLUS
FROM disk='E:sqlserver_databasesmcs_carolus_log.bak'
WITH FILE = 2, NORECOVERY
GO
RESTORE LOG MCS_CAROLUS
FROM disk='E:sqlserver_databasesmcs_carolus_log.bak'
WITH FILE = 3, NORECOVERY
GO
RESTORE LOG MCS_CAROLUS
FROM disk='E:sqlserver_databasesmcs_carolus_log.bak'
WITH FILE = 4, NORECOVERY
GO
RESTORE LOG MCS_CAROLUS
FROM disk='E:sqlserver_databasesmcs_carolus_log.bak'
WITH FILE = 5, NORECOVERY
GO
RESTORE LOG MCS_CAROLUS
FROM disk='E:sqlserver_databasesmcs_carolus_log.bak'
WITH FILE = 6, NORECOVERY
GO
RESTORE LOG MCS_CAROLUS
FROM disk='E:sqlserver_databasesmcs_carolus_log.bak'
WITH FILE = 7, RECOVERY,STOPAT = 'Nov 25, 2004 11:59 AM'
Is there a simpler way of restoring without going through this long process.
Regards,
Albert
View 1 Replies
View Related
Aug 11, 2005
When you create a dump device, then add backups with the NOINIT arg, the space taken up on the device grows, is there a way of removing individual backups from the device without (i guess) purging the whole lot by running WITH INIT?
I can get a list of the files by running the RESTORE WITH HEADERONLY, but I can't see how to remove individual ones.
View 4 Replies
View Related
Nov 3, 2006
I created a backup device and a job to backup a full dbs and transaction logs. When I first created the backup statement it was created with RETAIN DAYS = 10
Its been 15 days and the ones created after 10 days appear to be marked for deletiong but I am obviously missing a statement in my backup statement or in another job I need to schedule to come through and delete the old files from the backup device. Please help me figure out what this statement needs to be.
All the backups and T-log backups are written to one device which is one file.
Thanks
Scott
View 6 Replies
View Related
Nov 10, 2007
I am trying to attach to aMDF and LDF file that are on the remote location. When you try to do this though getting an error..
"File filename.mdf is on a network device not supported for database files."
How to do this.
Could you please help me to sort out this problem.
Thanks in advance
WillSmith
mail2willsmith@yahoo.com
View 3 Replies
View Related
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
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
Nov 17, 1998
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
View 1 Replies
View Related
Jun 19, 2006
Basically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
View 54 Replies
View Related
Apr 14, 2006
Hi to All
when i m trying to execute following code
backup database web
to disk = 'c:inetpubwwwrootackupmybakup.bak'
with format
I m Getting Error like :
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:inetpubwwwrootackupmybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!
but not for any subfolders of wwwroot.
Can Any One Help Me ??
View 6 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
Aug 30, 2000
Hi,
On my test database I have log device size grown to 4 GB from 2 GB. Is there anyway to reduce it? I have truncate log on checkpoint option on.
I tried dbcc shrinkdatafile. Didn't help..
Any suggestions???
View 2 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
Jul 31, 2000
I have a Device of 6GB and a Transaction logof device of 1.5Gb , the database of the associated device had gone suspect.so i dropped the
database , now i a m trying to delete the device it says cannot delete as its in use by some database , i have checked all the
Dbs butits not associted with anything what shold i do????
Regards
View 1 Replies
View Related
Nov 26, 2000
Hi all, hope someone can give me some guidence on this.
We have a database on SQL 6.5 sp5a that is 25GB in size. The transaction log is 10GB in size. We do transaction log dumps every hour throughout the day. On a very heavy day the transaction log backup device will be just over 2GB in size. Around 8GB wasted because no-one knew how much space would be used.
Is there an easy way to resize the transaction log so we can reclaim some space. I was thinking of creating a new smaller database device, then using sp_logdevice to use the smaller device as the log and get rid of the 10GB device.
Any thoughts, ideas would be great.
Thanks
Phill
View 1 Replies
View Related
Oct 13, 2000
We have a 6.5 database that is comprised of 4 device files. As the database grows over time a new device is created for the space expansion. The last one was created on the wrong partition and the database was expanded onto it. I now need to move the device to the correct partition/directory. My question is; what is the easiest way to accomplish this move? Is it possible to move the device file and manually modify sysdevices? If so, what order of events should be followed?
View 1 Replies
View Related
Aug 6, 1998
I want to expand a log device and am having a problem. When in Edit Database Device, the `Change Now` button is disabled and the size of the device is shown as a negative number.
View 1 Replies
View Related
Sep 28, 1998
On occasion, I notice that some of my backup devices have become "offline" and the scheduled tasks associated with them will no longer run.
I get this error:
Can`t open dump device `d:MSSQLBACKUPdvbk_AFISlog.DAT`, device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)
Does anyone know how to get them back online without having to create a new device? Thanks in advance...
View 1 Replies
View Related
Jun 25, 1999
hi, is there a way to find out what which database resides in which Database device.....
thanks
Ali
View 1 Replies
View Related
Dec 1, 1998
I am trying to drop a device that as no associated file in Windows NT Explorer(.dat) that shows up in my Server Manager Windows
How can I delete them?
Thanks
View 2 Replies
View Related
Jul 24, 2000
Hi,
I am trying to take a dump on to a disk with one of device A..its a transaction log dump every 15 minuter with no init and from the
same device which is connected to my standy server i am doing the load, the time diffrence is 15 minutes between standy and
production in terms of dump and load i am getting this error very frequently the device is offline the message is
"Can't open dump device 'j:ABC.DAT', device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)"
Now can this error come when the dump and load is happing at the same time tru the same device...???????
Can their be a way to control all this...??
Replies will be appreciated
View 2 Replies
View Related
Mar 5, 2001
I would like to free up some space on a SQL 6.5 server. My tempdb is on a second device (other than master) that is 500M. I don't need tempdb to be this large anymore. What would happen if I shut down SQL, renamed this device so that SQL Server wouldn't use it, and restarted SQL Server. Could I then create a new, much smaller device for tempdb? Would this work?
TIA!
JJ
View 1 Replies
View Related
