We have 2 SQL Server 2k5 servers running the same build - 9.0.2047 . When I backup any database from one server and attempt to restore it to the other, the log file generally increases by 100 fold. It errors out after I try to restore a 100MB db and it tries to create a 9.8GB log file. This happens both when I use the GUI to restore and when I restore from a T-SQL script. What am I doing wrong?
Thanks in advance.
Hi everybody,
I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it?
The features of this database are:
SQL Server 2000
Recovery Model = Full
Auto Update Statistics = Yes
Torn page detection = Yes
Auto create statistics = Yes
Full database backup taken once daily.
No log backup is taken.
So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions???
My free disk space is very low.
Thank you all,
I have a log file that is approximately 50 GIG. I backed up just the log and the file size of the .bak is 192 GIG . Why is this? Shouldn't it be closer to the 50 GIG.
Normally I wouldn't let log grow this much. But we are in process of getting new server up and running and don't have backups going yet. They are working on getting that up and running this week.
So I did a log backup to give me back some log space for now but was concerned when I saw the size of the .bak file.
When I view media contents of the backup device it shows one tranaction log back up and size of 192 GIG.
What is up with this. I know in SQL 2000 the log backup files where never this big. they were about the size of the log itself.
Any ideas?
Stacy
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
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
Hi,
Im in the process of setting up logshipping on sqlserver 2005 enterprise edition.
My scenario is like this:
My Avg size of my tlog is 500MB and im planning to set the log shipping at 30mins interval(ie backup job schedule,Copy,restore job schedule).But at some part of the day the Tlog suddenly increases up to 1.5GB - 2 GB .So i wanted to know, wht if that 1.5GB-2GB tlog file is unable to get backed up,copy and restore at 30mins interval?.How to deal with this kind of issues where the size of tlogs are increased suddenly.I cannot do it at15mins interval due to some network restrictions at my office.
i have One more doubt about the setting on Logshipping screen:
Now let us suppose my settting on log shipping screen 'Alert if no restore occurs within' is set to '180mins', then does this setting mean that the restore job will keep on looking for the copied file in the folder on secondary for next 90mins and if its not able to find any, it will generate an alert after 90mins ??? or it will generate an error if its nt able to find any copied file after the first restore job execution.???
in the same way,
Thnx in advance for any help.
Regards
Arvind L
I have a database whose log file size is 4 time greater then data file size, and its continuously growing day by day. Recently face limited disk related issue.
Is there any way to truncate log file???
What is impact on db if i truncate log file???
Is there any way to prevent this file continuously growing???
is there limitation for size of file to store in db by filestream in sql server 2008?or it accept all sizes?
View 1 Replies View RelatedIs this Possible, If database is in Simple recovery Mode and the ldf size gets increased?? .
mdf size : Â 159 GB (171,383,717,888 bytes)
ldf size :Â 6.46 GB (6,945,505,280 bytes).
My question is if the recovery model is in Simple Mode then why the log gets generated high.
dbcc sqlperf(logspace) --output
DATABASE Â Logsize(MB) Â Â Â Log space used(%) Â Â status
mam      6623.742
    0.4305579
       0
Is there any issue or it is Normal.
Hi,
i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns
dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB.
i try to write this a got stuck.
select sysdb.database_id,sysdb.name,sysdb.compatibility_level,
sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster
where sysdb.database_id = sysmaster.database_id
can anyone help me with this script?
THX
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View 1 Replies View RelatedHi,I'm new to sql but have a website running it on a hosted site. I want to have a local copy of the site running here for testing. I have everything set on my local machine to run it including MSDE and Enterprise Mgr.I've created a blank db with the same name,username and password on my local machine. From my host I received a dbname.bak file which they said I should just restore to my local machine. This sounded simple but I have no idea how to do this. When I right click on the db file within Ent.Mgr I see the Restore option but I can figure out how to designate the dbname.bak file as the source. I also see an option to import but that isn't any better.Does anyone have some simple directions which will show me the proper steps and where I should be to do this? I'd really appreciate it. I assume it's simple but not having ever done it it has me stumped.Thanks!
View 11 Replies View Related"Restore Now" button is greyed out after choosing to "Add File". I know I've done this before, can't figure out the problem.
View 1 Replies View RelatedI have a .dat file from a SQL Server 6.5 database and am trying to restore it on my server. Any help would be greatly appreciated. If you need more information, please let me know. If it`s not obvious, I`m new to SQL Server so try not to gloss too much over the details.
Thanks,
Nelson
Tried restoring a database from .bak file through Enterprise Manager and also by usng the following code :
RESTORE DATABASE DBB
FROM DISK = 'c:DBA.BAK'
WITH
REPLACE,
RECOVERY,
MOVE 'ap0data' TO 'c:mssqldataapm_data.mdf',
MOVE 'ap0Log' TO 'c:mssqldataapm_log.ldf'
But, I get an error :
Server: Msg 3156, Level 16, State 2, Line 1
The file 'd:mssql7dataapm.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
I have got the two filenames i.e."ap0data" and "ap0log" by using the command "restore fileslistonly from disk = c:dba.bak".
Can anyone help me to do the things rigtly ?
I have tried unsuccessfully to attach a database that doesnt have its corresponding log file.
Is this possible in SQL 2005?
Please help.
I have a MSSQL7 data file mydata_data.mdf that I would like to load into my MSSQL7.
When I try to restore to my database, I got an error message saying MSSQl does not recognize the file as a valid backup file.
Your help is greatly appreciated.
I am using MS SQL 2005 Express. I have a database with two filegroups, the PRIMARY default filegroup, and then filegroup X. I have a backup of the PRIMARY filegroup only, and am trying to restore it to my database. When I select the .bak file to restore from, I am NOT given any 'backup sets to restore' - I therefore cannot select the filegroup to restore it. Can anyone advise? (NOTE - I have backed up the transaction log on the database I am restoring to already)
Thanks
MArco
Here is the problem in details :
It’s a classical ASP ( not .net ) website and it has a sql server 2000 backend database. I’m moving the website to a new windows 2003 server with sql server 2005 standard edition.
The hosting company ( Godaddy ) of the website refused to give me the mdf & ldf files for security reasons so they only allow to give .bak files.
I restored the database to sql server 2005 using the .bak file , the sql server has SQL server authentication, and everything seemed to be nice.
The problem is :
The database has a user name and he is the owner of the database schema. When I try to login with it the sql server says invalid login.
How to change the PASSWORD of this user after the backup?
I tried other scenarios to solve the problem but with no hope…. Some of them were:
•I added a new user to the SERVER security node, and I mapped it to the database schema, it worked fine BUT : all SQL statements in the code are like “ SELECT * from table� not “SELECT * FROM scehma.table�… so I don’t think the user was mapped correctly?
•I tried to delete the old user, but sql server said that he is the owner of the schema.
The only solution I have now is to change all sql statements in the code to be like “SELECT * FROM schema.table “… which will be a GREAT PAIN.
Hello.Can anyone tell me if a SQL Server 2000 database can berecovered only from the MDF and log files ?I have no backups.Thanks in advance
View 1 Replies View Relatedhow 2 restore database in MS SQL 2005 using .mdf & .ldf file
View 4 Replies View Related
Hello
I am trying to restore a file from a file/filegroup backup from our live server to a test machine but keep getting the following error:
The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.
The location of the files on the live server are different to where they will be on the test machine but from my understanding of the restore t-sql i thought it the move would locate them to where they should be? here is my restore command i am running:
RESTORE DATABASE TestDB
FILE = 'File1'
,file ='File2'
,file ='File3'
,file ='File4'
,file ='File5'
,file ='File6'
FROM disk = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupFileGroup.bak'
WITH norecovery,
MOVE 'File1' TO 'C:sqlarchivesFile1.ndf' ,
MOVE 'File2' TO 'C:sqlarchivesFile2.ndf' ,
MOVE 'File3' TO 'C:sqlarchivesFile3.ndf' ,
MOVE 'File4' TO 'C:sqlarchivesFile4.ndf' ,
MOVE 'File5' TO 'C:sqlarchivesFile5.ndf' ,
MOVE 'File6' TO 'C:sqlarchivesFile6.ndf',
I can not find any info about this error i am getting, can anyone point me in the direction of where to try and troubleshoot this message please?
Thanks
Hello everybody!
Does anyone know how to restore a database from a .dmp file? I'm using the SQL Server Enterprise Manager to do that.
I've searched within the program, but i didn't find anything.
Thanks.
I got a .bak file from my friendI want to restore the data base in my system(presently I don’t have any database in my system).But while I try to restore itI am getting an error“System.Data.SqlClient.SqlError: Directory lookup for the file "D:Microsoft SQL Server 2005mydatabase.mdf" failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)�I have installed Microsoft sql server in a different directory not in D: where he is installedI can successfully restore the back up files of data base on my own systemBut not the backup files created from others systems
View 2 Replies View RelatedHi all,
My question is:
If I need to restore a database when all I have is the backup file, do I need to recreate all the tables, or is it possible to restore into a new database ?
Is it possible to restore a sql server 2000 database into a sql server 2005 database ?
Thanks in advance.
Hi all,I have installed sql server express edition and using this with asp.net website application. It was working fine,I am using .mdf file as database in app_code folder of asp.net website application, but now there is one issue which is with a particular table records.In this table some of the columns has got the same value for all records in the table. so, now i want my previous(original) data as backup, but unable to do so. I am using third party tools for recovery. Can anyone please guide me to resolve this issue.Thanks in advance
View 2 Replies View RelatedHi Everybody,
Our branch office in Europe has sent full 'backup' file of capacity 25GB.
I have to restore this backup to our database. Their database is 'Candidate'
and our database is 'client'. Both these databases have different logical
names and physical names.
The following Batch script only I hv executed,
RESTORE DATABASE Client
FROM DISK = 'd:dumppaw01dump.bak'
WITH MOVE 'candidatedata' TO 'e:mssqldataclient_data.mdf',
MOVE 'candidatelog' TO 'd:mssqllogclient_log.ldf',
REPLACE
Can anybody tell me, what I have executed above is correct or not. Now
restoration is going on for more than an hr. Still it is going on. Is there
any other way to restore this backup set very fastly.
Our server has got very good hardware setup. Good amount of hard disk space.
4GB RAM Memory. Running 4 processors. No other service is running apart from
SQL Server. Right now nobody is accessing the server.
How long it will take to restore this backupset?. This is urgent please.
Any help would be appreciated.
tks in advance,
Sri
I have created the file group for my database.First i took backup of individual file group(mdf and ndf) then I tried to restore only secondary(ndf) file group.I got error like
Restore failed for Server 'pcnameSQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
File 'regSQL_dat' was not backed up in file 1 on device 'D:vtndf.bak'. The file cannot be restored from this backup set.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3144)
When i tried to restore only primary file group i got the same error.
Can i restore individual file group? I
For the purpose of data archiveng,i have taken back up of ndf file (it contains very old data) & i have removed this file from database.Now my customer asking these file data.Now i have to again attach/restore this ndf file.how to attach/restore.
Been asked to restore an orphaned MDF file leftover after a botched uninstall - no .bak file. Tried to reattach, but got an error, I don't think it had been detached. My initial answer was, "No, very likely can't be done".
Am I right? Or is there a way of attaching it that doesn't require it to have been detached?
I am trying to restore a database called BCC_DB (I'm actually just needing one table opened to find some data in one column) so I created a blank database called BCCrestore.
I have a .bak file called BCC_DB_backup_201505020017.bak.
I placed it in my local drive C:BCC_DB_backup_201505020017.bak and tried the SQL:
RESTORE DATABASE BCCrestore FROM DISK = 'C:BCC_DB_backup_201505020017.bak'
GO
I get an error:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:BCC_DB_backup_201505020017.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Hello,
I have just installed sql server 2005 in a windows server 2003 server.
i tried to create a new database from a backup file. after selecting the backup to restore i got the net message.
Restore failed for Server '<ServerName>'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Directory lookup for the file'<D:file name.mdf>'. failled with Operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)
i found some information about and it says that it has something to do with the sql writer service i tryed stopping the service but i could not restore the db.
any recomendations to restore this database.
thank's
Michael