How To Restore Data From A Device File On The Harddisk
Aug 27, 1999i had to fromat the harddisk for some reason , but took a backup of the data device file . is it possible to recover tha data from them .
View 1 Repliesi had to fromat the harddisk for some reason , but took a backup of the data device file . is it possible to recover tha data from them .
View 1 RepliesHi,
What I need to do is restore a full backup of one database to a database where disk space is limited.
so my source device is 2gb for data and 1gb for tranlog
my dest is 1.5gb for data and .5gb for tranlog
I am thinking this is not possible, but thought I would post here anyway!
Thanks in advance!
I want to be able to take a backup device to another server and then restore the last backup file within the device.
My script creates the backup device using the filename of the file from the other server but when I do the restore it only restores the first file in the device.
How can I get it to restore the last file? In EA I can do a manual resotre and look inside the device and choose say backup number 2.
How can I do this in a script?
Thanks
I want to back up my database to a logical drive and restore it from this logical device. But I am unable to see my logical device through sql server management studio interface. I am able to back up to a logical device and restore it from logical device through T-SQL code. I am wondering how come I am unable to see my logical device from sql server management studio interface. Is there any way, I can solve this problem? Thanks in advance.
View 3 Replies View RelatedDOES ANYONE KNOW HOW I CAN RESTORE A SQL7 DATABASE WITH JUST THE DATA FILE?
THERE IS NO GOOD BACKUP OF THE DATABASE AND THE LOG FILE IS MISSING. THE CLIENT THOUGHT THEY WHERE BACKING UP THE DATABASE BACKUP FILE TO TAPE, BUT THEY WHERE NOT. THE BACKUP FILE WAS BEING OVERWRITTEN EVERY NIGHT. THE LAST NIGHTS BACKUP DID NOT GET TO FINISH TO COMPLETION.
PLEASE, ANY INPUT WOULD BE APPRECIATED.
THANKS,
TIM
Our SQL database log file got physically deleted. Now the database is in suspect mode. Is it possible to recover / restore the database just with the data file.
View 3 Replies View Relatedi have sql server 2000 db with two data file... primary data file has extension mdf and secondary file has extension ndf (as per microsoft recommendation)..
when i try to backup the db and restore thru the enterprise manager .. in the restore -> options window ... i see both the files has the same extension mdf.. and when the restore completed, the new database still has extension mdf for both the file..
why this behaviour?
* i even try to create a new test db with two files, still its the same behaviour.
How to write the stored procedure or SSIS package to check the hardisk space?
SQL Server 2005 SP2
Thank you,
Smith
Hai,
I stored a image in a database table and i want to transfer this image to some directory of the hard disk.
Example:
imageid int;
picture image;
insert into myprojectimages(imageid,picture) values (@imageid,@picture);
I am using stored procedure to insert through my programming by passing parameters;
Once i stored the images from harddisk to database table(myprojectimages). Is it possible to transfer the image from database table to some harddisk location (Example: C:projectimagesuilding.jpg)
Please help me in the regard.
Thanks............................
Regards
Kashif Chotu
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It “seems” to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with “MODTRNMaster”
The databases which are created from the restores start with “M1_” and “M2_”. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster
to disk = 'f:kupMODTRNMaster.bak'
backup database MODTRNMaster_IMG
to disk = 'f:kupMODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC
to disk = 'f:kupMODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM
to disk = 'f:kupMODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. I’m hoping that there is just something simple that I’m overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master
go
drop database M1_MSLH
go
restore database M1_MSLH
from disk = 'f:kupMODTRNMaster.bak'
with move 'DEV5_Data' to 'f:mssqldataM1_MLSH.mdf',
move 'MM' to 'f:mssqldataM1_MLSH_1.mdf',
move 'AMB' to 'f:mssqldataM1_MLSH_2.mdf',
move 'DM' to 'f:mssqldataM1_MLSH_3.mdf',
move 'IMM' to 'f:mssqldataM1_MLSH_4.mdf',
move 'ED' to 'f:mssqldataM1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:mssqllogM1_MLSH_log.ldf',
recovery
go
Thanks in advance
:eek:
:eek:
Hi at all,
first post....first help :P .
I hope to help another one (basing on my knowledge) in the next future :)
I'm a beginner dbadmin and I'm looking for help regarding a strange fact relative SQL Server 2005 :)
I migrated a medium(3,5GB) database from sql server 2000 to sql server 2005.
I made a backup in sql server 2000 (it has generated a 3,5GB BAK FILE) and I restored it in sql server 2005 on another server. (I didn't checked data file size).
I configured (with wizard) a maintenance plan on new sql server 2005 with these steps (in this order):
(tellme if the order of the steps is wrong :))
1) check db
2) rebuild index
3) reorganize indexes
4) update statistics (all table and all views)
I planned the maintenance plan on 3a.m. and I went home.
Next day I found that data file (MDF) on sql server 2005 was 22GB large!!
I made a shrink DB but there wasn't free space to erase. It seems there are
22GB of data.
(the data inside is the same of the sql 2000 server..same records same
table...identical, non change of data in the meanwhile)
How is it possible? What am I doing wrong?
I don't understand what can caused the growth of the file, the maintenance plan or the restore? (unfortunately....I didn't checked size after restore...I checked it only the next day).
May be the statistics? (there wasn'int update statistics job on old maintenance plan on sql server 2000)
Thanks in advance :)
Bob
I'm following these instructions: http://technet.microsoft.com/en-us/library/ms156421.aspx
I run this restore letting it create the database. It works fine:
RESTORE DATABASE ReportServer
FROM DISK='\MYMACHINEe$BackupsReportingServicesReportServerData.bak'
WITH NORECOVERY,
MOVE 'ReportServer' TO
'G:DataReportServer.mdf',
MOVE 'ReportServer_log' TO
'J:LogsReportServer_Log.ldf';
GO
I run this command and get this result set, with the fileIDs in red.
Restore filelistonly from disk = '\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'
ReportServer2005
d:Program FilesMicrosoft SQL ServerMSSQLdataReportServer2005.mdf D
PRIMARY 3407872 35184372080640 1 0 0 9A65122E-36B7-4D93-A0FB-F1A894641A09 0 0 0 512 1 NULL 62000000029300037 D49504CB-7E10-4BA3-B737-F2F9F2070F39 0 1
ReportServer2005_log
d:Program FilesMicrosoft SQL ServerMSSQLdataReportServer2005_log.LDF L NULL 3211264 2199023255552 2 0 0 0D6FE3C7-C5CC-49FB-A66C-43272EB47763 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1
then I run this LOG restore:
RESTORE LOG ReportServer
FROM DISK='\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'
WITH NORECOVERY, FILE=2,
MOVE 'ReportServer2005' TO
'G:DataReportServer.mdf',
MOVE 'ReportServer2005_log' TO
'J:LogsReportServer_Log.ldf';
GO
Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 2 on device '\MYMACHINEe$BackupsReportingServicesReportServerLog.bak'.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I see file ID 2 identifying the log file. I've never done a restore like this - using copy_only and backing the database and log into separate .bak files, so maybe I'm misunderstanding something here.
Thanks for any help
Sam
A device solution I'm working on at the moment involves SQL compact. It is added to the currently selected project in VS2005 when I click on "Add New Data Source". The particular project it is added to within the solution is just a class library project (dll) which uses the database. The startup project on the other hand includes the windows forms etc. The problem I'm finding is that when the sdf file in the dll project is set to "copy always" or "copy if newer" it is not copied to the device at all. If I ensure that the sdf file is included in the startup project as "copy always" or "copy if newer", it does copy over to the device. Is this a bug or a feature?
View 1 Replies View Relatedrajesh writes "Hi All
When i attach my production database i got an device activation Error
cannot
attach database."
HiI have a backup device file ... ".bck" whick has grown pretty large.Is there any way I can reduce its size ?ThanksDavid Greenberg
View 4 Replies View RelatedHi,
I am trying to connect to a database on a Windows Mobile device using the Management Studio. I am able to select server type "SQl Server Mobile" and navigate to the file on the device. When I try to connect I recieve the following error. Should this work? If so what must I do to allow the registry access?
Thanks,
Ron
Requested registry access is not allowed. (SQL Server Mobile Edition ADO.NET Data Provider)
------------------------------
Program Location:
at Microsoft.SqlServerCe.Client.TdsParser.ThrowExceptionAndWarning()
at Microsoft.SqlServerCe.Client.TdsParser.Connect(String host, SqlCeInternalConnection connHandler, Int32 timeout)
at Microsoft.SqlServerCe.Client.SqlCeEngine.FileExists(String filename)
at Microsoft.SqlServerCe.ManagementUI.Utilities.GetUniqueFilename(String path)
at Microsoft.SqlServerCe.ManagementUI.BrowseForFiles.OnBtnNewDatabaseClick(Object sender, EventArgs e)
Can anyone help me to recreate the .NDF file if it was lost and not restorable? I have a stored procedure written that will then help me rebuild all indexes on all tables, but I can't seem to figure out how to create the index file again so I can run it. I have tried index files from other databases to no avail. There is no backup to go to.
View 10 Replies View RelatedWhat's is the between backing up a database to a file and a logical backup device?
If I point the logical backup device to a file on the filesystem, it's same as backing up to a file? isn't?
Thanks
When I install SQL 2012 on MS Server 2008 R2, I get this error..The following error has occurred: The system cannot open the device or file specified. Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.
The file in question is Install_VSShell_CPu32_Action. when I retry, the error is..The following error has occurred:SQL Server Setup has encountered an error when running a Windows Installer file.Windows Installer error message: This update package could not be opened. Verify that the update package exists and that you can access it, or contact the application vendor to verify that this is a valid Windows Installer update package. Windows Installer file:
Windows Installer log file: C:Program FilesMicrosoft SQL Server110Setup BootstrapLog20130522_143111VSShell_Cpu32_1.logClick 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.
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 RelatedI have several clients who are reporting a negative file size on their database devices in Enterprise Manager. The sp_helpdevice procedure reports the size correctly. Any suggestions?
View 1 Replies View RelatedIt's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...
The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (ServerBSQL_backupsdvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date2009/08/08 06:43:30 AM
LogJob History (Backup_AssetData_2009-08-08)
Step ID3
ServerSQL01
Job NameBackup_AssetData_2009-08-08
Step NameBackup Database - Second Attempt
Duration00:05:33
Sql Severity16
Sql Message ID3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: COMPANYSERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData(
iscsrv-dcm04RISCSRV-SQL01_Backupsdvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The file is generated, the same size as a successful backup, but the job terminates unsuccessful.I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.
I have a few databases that backup to the UNC described above (different files) that don't fail..My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.
My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.
##20090812## UPDATE
I tried another way to do the backup; with the same results:
Backup database Infovest to disk = '
iscsrv-dcm04
iscsrv-sql01_backupsdvc_Infovest_Z.bak' with INIT,STATS=1
[code]....
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
I have a simple class libarray that I have compiled into DLL using csc.exe
Source code is as follows: -
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Microsoft.SqlServer.Server;
namespace RTFTextParser
{
public class ParseText
{
[SqlFunction(DataAccess = DataAccessKind.None)]
public static String ParseRTFText()
{
return "Imtiaz";
}
}
}
When I try to run the following
CREATE ASSEMBLY RTFTextParser FROM 'E:Class1.dll'
I get the following error
Msg 6501, Level 16, State 7, Line 1
CREATE ASSEMBLY failed because it could not open the physical file 'E:Class1.dll': 21(The device is not ready.).
We run std 2008 r2. I'm looking at the files this transform is complaining about.  They seem to be named appropriately. The customerid folders don't exist when this runs. I'm going to put one in place to see if that is the problem.
The errors i'm getting are...
[Export Column [22]] Error: The file name "c:usersmyuserid heprojectnamecustomeridafilename.doc" is not valid. The file name is a device or contains invalid characters.
[Export Column [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Export Column" (22)" failed because error code 0xC020207F occurred,
and the error row disposition on "input column "FILENAME" (29)" specifies failure on error. An error occurred on the specified object of the specified component.Â
There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Export Column" (22) failed with error code 0xC0209029
while processing input "Export Column Input" (23). The identified component returned an error from the ProcessInput method. The error is specific to the component,but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
hi all,
i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?
thanx.
:-)
hemant
I had a major disk failure, and the only SQL Server files I was able to recover were master.dat, msdb.dat,msdblog.dat, two text files and two database devices. My question, is it possible in SQL 6.5 to restore a database from just the database device, and if so, how? Thank you.
MH
Hi,
I have tempdb data device size default 2 MB, which has completely filled up. I am trying to expand data device to it.
I created new device tempdb_data_ext (250 MB) and tried to expand tempdb data device. But everytime I do it, it ends up adding space to tempdb log device. How can I expand tempdb data device?
It's extremely urgent.
Thanks
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?
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 ??
I am using 6.5 .
Here is the error that i get
Think that tempdb is small how do i change that or what is this error about'
--------------------------
AIMSMan
---------------------------
Application-defined or object-defined error 40002
37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Can't allocate space for object '##RevByNetSALIMJUMMA' in database 'tempdb' because the 'default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.( 1105)
ODBC
I only have data and log device how do i increase the tempdb device
let me know
SEJ
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC
2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original
3) Took the new database offline
4) deleted the MDF and LDF files of the new database
5) put "MyDB_0.LDF" in the place of the LDF file I just deletedÂ
6) put the database back on-line
7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)"
8) tried to do Tail Log Backup with this command
  BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak'
WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3
Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I want to update data on the device which has a sql server mobile database.
the main database is sql server.
I use web services to enable the mobile application to access the server.
Can anyone recommend a way of updating the data on the device without using replication? I basically want to read a dataset from the server via webservice and put this in to local db - but not row by row as too many records.
Regards