I checked quickly one network  and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
View 6 RepliesMy 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 just begun to try out differential backups. Almost every reference I found on the internet said to use a .dif file extension. Books Online "Back Up Database Task (Maintenance Plan) MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/ed1ef012-fa14-4ba5-bafe-d1527ba065b3.htm only says :
Backup file extension Specify the extension to use for the backup files. The default is .bak.
                                                                                                                                  Â
Using .dif for the differential shows "Microsoft Office Excel Data Interchange Format" as the type when looking at the backup folder. URL... indicates that .dif has 11 different uses, including "Difference data file." In a couple of posts in this forum in April and May of this year David Dye said that the file extension for differential backups is .bak, the same as for the full backup.
Â
If this is the case, this would cause a problem with our maintenance plan policy which is to retain the two most recent backup files on the server. If we do a full backup for the base on the weekend and differential backups the rest of the week, our full backup would have to be restored from out tape system before we could do a restore. Recoveries are pretty rare, but when we get that panicked phone call that someone accidently deleted all the records of a major client, time is of the essence in getting that point in time restore done.
I got full backup on daily schedule its taking more space on Drive because each file has more than 25GB.I am using SLQ server 2008R2 so I'm looking to take the backup with compression instead of uncompressed Backup. What are the impacts of compressed backup. Is there any problems with compressed backup while restoring the backup file.
View 8 Replies View RelatedI am working on a project for an SQL job. I am:
1.) Taking a database out of an availability group,
2.) Setting the recovery to simple,
3.) Shrinking the log file,
4.) Setting the recovery mode back to full,
5.) Then backing it up.
I need to restore the file to my secondary server with replace and non recovery mode. I am having trouble performing that call? I have the code to reestablish the database to the availability group if I can get the restore feature working.Â
What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy
ONÂ
PRIMARY
( NAME = Dummy_data,
  FILENAME = 'D:....DATADummy.mdf',
  SIZE = 250MB,
  FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
  FILENAME = 'D:....DATADummy_log.ldf',
  SIZE = 50MB,
  FILEGROWTH = 5MB ) ;
GO
I was told by my website provider that they do not support the .mdf extension and only support the mdb extension. What am I not getting here? I am using SQL Server Express 2005. Is .mdf a subset of .mdb?
View 1 Replies View RelatedExecuted as user: S233683-AD01S233683NJ3SQL05. ...at file already exists.' [SQLSTATE 42000] (Error 22048) Â ERROR -- Could not backup database: master - BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 50000) Â xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'
View 2 Replies View RelatedHi,
I need to read a binary file with extention *.dat like this:
tat.dat and import it in ms sql server 7.0.
Thanks for any help
Dr Bangaly Diané
Hi,
If access database has an extension of .mdb then what is sqlserver2000 file extension?
Hello there!
I am in need of urgent help. i do believe that when you used the command SPOOL filename without specifying an extension name, it automatically puts an .lst extension name. Can you help me identify if there is any way i can generate a file without an extension name? I am in need of help right now. I hope someone will answer my post as soon as possible. I really really appreciate it!
Thanks,
Jen
Hi All,I am receiving the following message when I run Dts package which iscreating a cube.Error Source : Microsoft Data transmission Services (DTS)PackageError Description: File name or extension is too longAny help will be appreciated!Thanks in advance,Mohammed SarwarOcp dba oracle 9i,8i,8.0*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedIn document for encryption in SQLServer with application XP_Crypt, i see file with extension .pem when using private key or public key. How to create file .pem. Please explain me private key or public key.
View 1 Replies View Related
Hi,
I'm trying to change the file extension of one of the database files of a big database that contains 10 years of EPOS data.
When this file was created, at the beginning of 2008, with the following script:
USE [Live]
ALTER DATABASE [Live] ADD FILE (NAME = 'Live_2008',
FILENAME = €˜E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataLive_2008') TO FILEGROUP [2008]
the .ndf file extension was not added to the filename. So the file now has a file type of "File", instead of "SQL Server Secondary Data File"
I tried, in a test environment, to bring the database offline, change the filename to "Live_2008.ndf" and bring the database back online, but it gives me an error message saying that "cannot open the Live_2008 file", i'm guessing because it's looking for "Live_2008" and it finds "Live_2008.ndf" instead.
This file is quite big (5Gb) and I cannot afford to risk to lose any data.
The situation is complicated even further by the fact that it seems that a partition for 2008 does not exist.
How can I find out what data this file contains?
Is it going to be an issue if I leave the file like that?
Thanks
I set up a basic ForEach enumerator loop and specified files of type *.sql.
In the directory, I had some files I needed to keep but didn't want the package to touch, so I changed the extension to *.sqlo. Much to my dismay, the ForEach loop picked up those files. (Though it did skip the *.xml and *.bat and a few other types...)
Sounds like a bug to me.
I programmed a Source extension, that accesss a text file. In my code I have the following code to open a file:
protected override void reader(PipelineBuffer buffer)
{
TextReader reader = File.OpenText(_filename);
The komponnet works well. How do I grant permissions to access this text file? Because I got problems when I try to run my package using a task created in the SQL Server Agent.
i 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.
Hello,
I try to add an data extension who take the information to connect to the database in a config file. But It's could not retreive the values corresponding to the key.
I'm using the class ConfigurationManager from the namespace System.Configuration.
ConfigurationManager.AppSettings["QueryModel"]
Without the config file, the extension is working well.... I got the impression that reporting service and Visual Studio BI don't find the config file.
If someone can help me or have some advices. I will be pleased
I have a source file as filename.mar it is microsoft access report. When I am loading into my database the file name was something like filename.nov (its current month).
When I ran the package its shows error system cannot find the file filename.nov.
I use an execute sql task and excel connection manager to craete excel file.
Execute sql task:
In the connection:
Â
It works. But when i choose file path to: C:1111.xlsx
And excel version to micorsoft excel 2007. When open the file it created,it promotes the format is not correct. How to solve this?
When I am saving a SSRS report in excel format in a shared network drive, how can I add the date extension to the filename ?I assume this is something that needs to be handled within the SSRS reporting side rather than the subscription side.
View 5 Replies View RelatedUsing SSRS 2008R2 is it possible to change the file extension of a CSV data driven subscription? Â I'm outputting a text file with a .csv extension, but users have access to these files and I don;t want them opened with Excel and then saved back in an incorrect format.Â
These are the options I have from the report "manage" option. Â
I have implemented a package to load multiple files to a destination. Since the source was a txt file, i have created as flat file source. However now we are getting files in excel format as well.
Is there anyway the source gets changed dynamically based on the file extension, output of the foreach file enumerator? I can think one solution to have 2 dataflow tasks based on precedence constraining and expression one is for .txt and other one is for .xls.
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
Recovery model: Simple with Full backups
begin transaction
begin try
       insert into [EthnicRace] select * from [OEPRD].[CIS_Source_Data].[dbo].[EthnicRace];
       insert into [MilestoneOwner] select * from [OEPRD].[CIS_Source_Data].[dbo].[MilestoneOwner];
       insert into [PS_ACAD_CALTRM_TBL] select * from [OEPRD].[CIS_Source_Data].[dbo].[PS_ACAD_CALTRM_TBL];
       insert into [PS_ACAD_DEGR] select * from [OEPRD].[CIS_Source_Data].[dbo].[PS_ACAD_DEGR];
end try
A job on our production server goes out to Oracle and gets raw data from our student system once per night. A job on our development server then uses the above coding to copy that raw data from production to the tables in our development system. My question is, will the log continue to grow until the "end try" is encountered or is it cleared at the end of each INSERT statement?
I am trying to shrink the log file as it is eating my disk space. increased to 100GB..message after running the query"Cannot shrink log file 2 (mydatabase_log) because the logical log file located at the end of the file is in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
"
(1)use mydatabase alter database mydatabase set recovery simple with no_wait dbcc shrinkfile(mydatabase_log,1) database [mydatabase] set recovery full with no_wait
(2)USE mydatabase;
GO
Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE mydatabase
SET RECOVERY SIMPLE;
GO
Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (mydatabase_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE mydatabase
SET RECOVERY FULL;
GO
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
What is the difference between "Files aned File Groups" backup and Partial Backup?
Looks like both are same.. Please comment.
In SQL 2012 I'm trying to delete a log file (both physically and logically). There are two log files and one of them is unnecessary. When I click 'remove' in SSMS it will delete the log file, but then when I go back under database properties it's still showing up even though the file has been physically removed from the OS. I'm wondering what steps I can take to get rid of the file permanently?
View 12 Replies View RelatedIf we want to change something about a custom server side trace in SQL 2012, do we wipe the current trace with sp_trace_setstatus <trace_id>, 2 and then re-create it with the new definition? It looks like there's no way to modify it in place.
View 6 Replies View RelatedOne of our database came to Restoring mode. I suddenly stop my SQL service and Copied only MDF files again Started SQL service ,unexpectedly i dropped the Database. Now i cant able to attach the database only with my MDF file.
I tried below Scripts. All scripts shows same error.
EXEC sp_attach_single_file_db @dbname='PhoenixPolice',
@physname=N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf'
GO
CREATE DATABASE PhoenixPolice ON
  (NAME = N'PhoenixPolice',
  FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf')
FOR ATTACH_REBUILD_LOG
GO
CREATE DATABASE phoenixPolice ON (FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf')
FOR ATTACH
All scripts shows below error
File activation failure. The physical file name "F:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAPhoenixPolice_1.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'PhoenixPolice'. CREATE DATABASE is aborted.
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
i have  .bak file  downloaded from internet , and i also have istalled  sql server  2012.my problem that i can not restore  this  .bak file and get this error massage :
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASRO_VT_SHARD.mdf'. (Microsoft.SqlServer.SmoExtended)
- .bak file  version  = 661  10  50  1600 = sql server  2008R
- my sql version   = Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)