Hi,
I am trying to create a scheduled task taht will restore a database from a backup file. I do not store my backups on a backup device, but on a local disk. To restore a DB from a backup device, the following statement will work:
"Load DBName from BackupDeviceName". Does any know what statement to use to restore from a file, if the file is "E:DBName_db_dump_199909272220".
Thanks in a advanced.
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
If databases on a physical drive [G:] are fragmented, and the drive isextended by adding more hard drives to the array, does it make sense tobackup and restore the fragmented databases?The Windows Server should be able to find contiguous space for eachdatabase, since it shows 75% free space on the SQL Data drive withoutany file fragments on it.Or will it restore to the original location, in which case does it makesense to delete the databases and restore them from the backups?Thank you very much!
View 1 Replies View RelatedHi,
I have formatted my server because of serious problem and i did not backup my database. I have only a phisical copy of the disk containing data on another disk. :( How I can recover my db? Thank you in advance.
i have a SQL 2008 R2 RTM production instance, in which we run dBCC CheckDB every weekend to check on the DB. This weekend this sql job returned the error:
DBCC RESULTS
--------------------
<DbccResults>
<Dbcc ID="0" Error="8928" Severity="16" State="1">Object ID 866531312, index ID 1, partition ID 72057602979266560
, alloc unit ID 72057603064397824 (type In-row data): Page (1:7650240) could not be processed. See other errors
for details.</Dbcc>
<Dbcc ID="1" Error="8939" Severity="16" State="98">Table error: Object ID 866531312, index ID 1, partition ID 720
57602979266560, alloc unit ID 72057603064397824 (type In-row data), page (1:7650240). Test (IS_OFF (BUF_IOERR, pB
UF->bstat)) failed. Values are 12716041 and -6.</Dbcc>
<Dbcc ID="2" Error="8990" Severity="10" State="1">CHECKDB found 0 allocation errors and 2 consistency errors in t
able 'tblDistpatch' (object ID 866531312).</Dbcc>
We tried to rebuild the indexes in the table: tblDistpatch...the non clustered indexes ran fine however the cluster index rebuilt returned an error:
Error: The statement has been terminated.
Msg 829, Level 21, State 1, Line 1
Database ID 3, Page (1:7650240) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.in TEST environment we were able to reproduce this error by restoring latest backup.
we ran :
dbcc checktable (tblDistpatch ,REPAIR_ALLOW_DATA_LOSS )
then we ran dbcc checkdb and no errors where found.
my question comes with in your experience is this the best possible way to fix this table?only one table, but running this in production environment will required to put db in single user mode first.
We're having some issues with where our backups write to, so I've been watching and monitoring the performance, when I noticed today that restore labelonly from disk has been running almost non stop for the past few hours.
The account running the query is the SQL Server's service account, and the program is "Microsoft SQL Server".
Every minute or so the SPID changes which made me think it was related to the transaction logs, the "restore labelonly" runs for as long as each database in the transaction log backup.
Example: Database A transaction log backup takes 1 minute and the SPID XX for restore labelonly runs 1 minute
Database B transaction log backup starts and there is a new SPID for restore labelonly.
I hope this makes sense because I normally don't see this restore labelonly running.
When I select All Actions>Backup Database and then click the ... button to choose a location on the hard drive, SQL Enterprise Manager Hangs and I have to kill it from Task Manager.
All other areas of EM browse the disk drive without a problem. (DTS, File Groups, etc.)
Anyone ever run into a similair problem?
Thanks
Hi all
every time I try to verify the backup file I get the error:
backup set on file '1' is not valid (translated from italian).
this is my backup script:
BACKUP DATABASE [ahr_sistema] TO DISK = N'C:ProgrammiMicrosoft SQL ServerMSSQL.1MSSQLBackupahr_sistemaahr_sistema_backup_1.bak' WITH FORMAT, INIT, NAME = N'ahr_sistema-Completo Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'ahr_sistema' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ahr_sistema' )
if @backupSetId is null begin raiserror(N'Verifica non riuscita. Impossibile trovare le informazioni di backup per il database ''ahr_sistema''.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:ProgrammiMicrosoft SQL ServerMSSQL.1MSSQLBackupahr_sistemaahr_sistema_backup_1.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
It's probably a really easy thing to do, but I can't find out how to do it, so hopefully somebody can spare the time to help.
I want to read in T-SQL contained in a file held on disk and execute it in a stored procedure.
Anybody know how to get the contents of the text file into a variable so I can exec it ?
Thanks for your time.
It's probably a really easy thing to do, but I can't find out how to do it, so hopefully somebody can spare the time to help.
I want to read in T-SQL contained in a file held on disk and execute it in a stored procedure.
Anybody know how to get the contents of the text file into a variable so I can exec it ?
Thanks for your time.
I am getting occasional failures of a SQL Server 7.0 complete backup to disk on a production database. The errors seem to indicate that another process has the disk file open at the time of the backup. The errors contain the following texts : -
'Cannot open backup device'
'Operating System Error=32 Process cannot access file because it is being used by another process'.
The only other process that should access the disk file is an ARCserveIT scheduled job to copy the disk backup to tape but this is completing long before.
Any ideas or suggestions?
Hi All,
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
Thanks.
Hi All,
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
Thanks.
My logfile has grow the disk full - the logfil is 25 gb and I have 4 gb free.
I can't shrink the log fil !
Can I set the log file to null ??
I have backup my datafil successfully!
Help!
Hi all,
I have to give support to this new client of ours and the system out there was a real mess.The log files for all four databases were ranging from 5 to 9 GB's, no maintanance no nothing was done since the setup of the system. I have truncated the logs after taking proper backups since it all started after they compalined a DTS was not working, which was due to no disk space available ;).
Now I have to shift the log files to a seperate disk.
I know it can be done by detaching the database and attaching it back, I was wondering if we can do it without bringing the system offline?
Anycomments welcome.
Thankyou.
regards,
harshal.
Hi everyone,
Due to running out of disk space and reducing I/O contention, I plan to seperate data files and log files to different disk arrays.
My plan is to
- detach all databases
- copy log files to another disk
- attach all databases
The total size of log files is about 60GB. It becomes a problem becasue it spend too much time passing files. To reduce down time, is there any way I can do?
Can I just create new log files and remove the current files by using sql management studio?
any other suggestions? Thanks in advance
Hi,
I'm trying to write a restore script. In the past I've always used the SSMS GUI to restore. When running the script below, the process got stuck at around 90%. Eventually I aborted the restore and found out that my C drive was full. The log file of the database I was restoring was 32GB. Can anyone tell me what I'm doing wrong? Thanks.
Code Snippet
ALTER DATABASE [oms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [oms]
FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupOMS_Blank_Production.bak'
WITH FILE = 1,
REPLACE,
STATS = 10
GO
ALTER DATABASE [oms] SET MULTI_USER;
GO
I have a poorly performing SQL box.
I have run perfmon and the avg read queue length is pretty much permanently maxed out at 100%.
I have run a database index defrag.
On further inspection the file system is highly fragmented. There is a file fragmentation of 98% with the mdf file fragmented in 25,000 pieces. Running a standard windows defrag does not resolve this.
Two questions?
1- Is heavy file fragmentation of the MDF file a likely cause of the read queue length bottlneck?
2 - Why is the MDF file not defragmenting? Does the SQL server have to be taken offline? Is it possible to defrag a MDF file?
catcat writes "Hi All,
Our MSSQL 2000 server crashed yesterday and one database cannot be restored. the latest backup we have is june. We can only recover the MDF file from the harddisk and according to our admin, it cannot be restored anymore because no proper shutdown was made.
Will appreciate your help here."
Hi all
My Transactional log size increased to 39GB, it is in full recovery mode,
To regain the space, i have done the following
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINK_FILE (LOG_FILE_NAME,500)
But not able to regain the space in the hard disk.
No Transactional backups to truncate the log file were setup.
Can you please tell me why the space was released and what should i do further to clean up the sapce
In SQL Server 2005, via the GUI, I wish to backup a database to an additional disk file (there is already an existing backup disk file for this database), so that I can have more than one backup. I've added the new disk file name, highlighted it, and clicked OK.
I get an immediate error (see below). Note, the 2nd error message is specifying the existing backup disk file, not the new one I'm attempting to create.
"Backup failed for Server 'WCS-DEV-TPA'. (Microsoft.SqlServer.Smo)"
"System.Data.SqlClient.SqlError: The volume on device 'D:Program FilesMicrosoft SQL ServerMSSQLBACKUPWCS_ADV_Longmont.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. (Microsoft.SqlServer.Smo)"
Does anyone know what causes this and how to correct it?
Do you have to move the entire content of the log folder to the new location?
View 4 Replies View RelatedIf I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
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 have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.
how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.
what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?
what is 64 KB cluster has 128 sectors?
tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB
Hi all,
Ok here goes,
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
Thanks
Hi everyone,
I am using SSIS, and I got the folowing error, I am loading several CSV files in a OLE DB, Becasuse the file is finishing and the tak dont realize of the anormal termination, making an overflow.
So basically what i want is to control the anormal ending of the csv file.
please can anyone help me ???
I am getting the following error after replacing the '""' with '|'.
The replacng is done becasue some text sting contains "" wherein the DFT was throwing an error as " The column delimiter could not foun".
[Flat File Source [8885]] Error: The column data for column "CountryId" overflowed the disk I/O buffer.
[Flat File Source [8885]] Error: An error occurred while skipping data rows.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (8885) returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
[DTS.Pipeline] Information: Post Execute phase is beginning.
apprecite for immediate response.
Thanks in advance,
Anand
Database file or filegroup method was used to perform the backup and now i tried to restore that into different server by using SQL Server Enterprise Manager.
I got this error message:
"The database you are attempting to restore was backed up under a diffrent sort order ID (54) than the one currently running on this server (52), and at least one of them is a non-binary sort order. Backup or restore operation terminating abnormally."
Why i got this error message? And what is "backed up under a diffrent sort order ID (54)" ?
Please anyone help me
Hi
My backup file is BAK FILE and it's in D:MSSQL7/Backup/Sales.BAK.
I want to restore that into a different server in D Drive. For that i created a database called "RestoreB". (RestoreD_Data.MDF and RestoreB_Log.LDF). I tried to restore using Enterprise manager, but i got error message.
"File 'Sales_BAK' is not a database file for database 'RestoreB'. Backup or restore operation terminating abnormally'
How do write the statement to restore that file?
Anyone can help me to restore that file...
Thanks in advance