Hello Group,
I have a SQL Server 2000 database that has never been backed up. The SQL Server database can't be logged into due to an SSL Security Error, thus I can't get to the backup utilities within Enterprise Manager.
What data files do I need to backup manually and what steps do I have to take to backup these files to a tape and rebuild the server?
Thanks
Kevin Kraus
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 RelatedHi,
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,
i have a maintenance plan running on my database, in which I told the wizard, on creation, to "remove files older than 4 week" and yet it doesn't seem to be doing so, as on checking this morning, diskspace was getting low, due to over 300gb of backups and trn' dating back to september.
Anyone have ny problems with maintenance plans not cleaning up when told?
a
My scenario -
-Dev server lost all its raid drives (which included user data/log files and ALL backups)
-Raid drives were removed from server, so we could try and recover the data (not sure why they had to remove them)
-Recreating the user db's isn't an issue, but I REALLY need to retrieve the jobs from the MSDB (developer was working on dts package for the last 2 months, but didn't check it in to VSS)
-Since the System DB's (model, master, msdb) were installed on the C drive, I was able to recover their mdf/ldf files.
So we build an new server and recreated the user db's.
How do I go about getting the jobs data from the msdb database?
I've tried the following -
Started SQL server with the -T3608 and was able to detach the MSDB database, but when I go to reattach using the mdf/ldf files from the other server I get the following error message.
Error 5172: The header for the file 'C:mssqldatamsdbdata.mdf' is not a valid database file header. The PageAudit property is incorrect
I've been doing some research and found a lot of great articles on how to RESTORE (from backup - dont have) or Rebuild (wipes out the data I'm looking for) the MSDB, but not how to replace it with only the ldf/mdf files....
Any idea's would be greatly appreciated.
Thanks
PS - All backups are now being copied to another server (lesson learned)
Hey guys,
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Thanks,
-Kilka
Michael writes "We are running SQL and Veritas to backup the databases. Supposedly the SQL agent in Veritas, after a full backup, truncates the log files but for some reason this isn't happening... any ideas?"
View 4 Replies View RelatedWe have a SQL 2K5 10GB database that, as part of the recovery plan, gets a differential backup every six hours. Log file backups occur every hour, and a full backup is done every 24 hours. Over the weekend, the differential backup produced a 55GB backup file which caused us a lot of issues besides disk space usage (log backups couldnt finish, mirroring broke, etc.). This is also the max growth size that the log file is set to. There are no errors in the ERRORLOG, or in the job history. It's as if the backup was successful, which I assume it was, but the file was sparse.
I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.
Has anyone experienced this issue before?
What causes it?
How do we resolve it?
Thank you in advance for your help,
Greg
where in sql server 2005 ( and 2000 for that matter) do i find the path to the location where backups are placed (.bak files)? is there sql i can use to find this out
View 6 Replies View RelatedUntil 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)?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedAll;
I have 14 errors in one of my tables and I know that all of you would recomend to restore form back-up. But i dont know when this table was corrupted and also loosing even days work is not an option.
Below is what i received from
DBCC CHECKDB ------
I dont mind loosing some data as far as i know what they are.
Any way to know what will be lost??
Or any other way to restore rather than form the backup....
===================================================================
DBCC results for 'Call_Events'.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:341561), slot 21, text ID 120642207744 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:341564), slot 53, text ID 120686379008 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:341565), slot 23, text ID 120684609536 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:341565), slot 27, text ID 120684806144 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:341565), slot 28, text ID 120684937216 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:341869), slot 0, text ID 120729305088 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:342598), slot 45, text ID 120850743296 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:342677), slot 58, text ID 120895897600 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2073058421. The text, ntext, or image node at page (1:359535), slot 26, text ID 125433413632 is not referenced.
There are 1108430 rows in 91674 pages for object 'Call_Events'.
CHECKDB found 0 allocation errors and 14 consistency errors in table 'Call_Events' (object ID 2073058421).
I am thinking about moving some backups from the local machine to a remote server. Right now, I am using Ola Hallengren script for backups. The script performs checksum on the backup while it's being written on the disk. I am going to move the files using Robocopy utility. How do I check the data integrity once the backups are transferred to the remote server?
View 5 Replies View RelatedHi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!
Recently I constructed a new backup process that I want to institute globally across all my SQL Server 2005 instances, Is there a way I can automate a file deletion process from within SQL Server?
View 2 Replies View RelatedHello all. Before my arrival at my current employer, our consultantsphysically set up our MSSQL 7 server as follows:drive c: contains the mssql enginedrive d: contains the transaction logdrive e: contains the data filesNo filegroups were set up and the data files consist of only 1 largephysical file. Currently, our data file is >10GB. When I was trained onthe physical aspects of sqlserver, I was told to never create physical files[color=blue]> 2048MB each. If I did, I could expect inefficient physical storage of[/color]data and slower performance (due to the OS).Our server has 2 RAID-5 arrays. Drive c: and e: are located on the firstarray and drive d: on the second. We're running Windows 4.0 NT Server SP6with NTFS.Can someone comment on the use of 1 single large data file vs. more smallerdata files?
View 2 Replies View RelatedI have an MVC asp.net application that stores many records in a table on sql server, in its own system. used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. no effect; the user makes their own login id but that does nt matter either.
i am really in need of help. i have a text file consiting of some data.i want to update my database from that text file periodically say 12 hours.the text file is being updated by another server program in every 12 hours can any one help me in this case? i am lost for this scenario?? help me please.....
View 1 Replies View RelatedHi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies View RelatedI have an sql 6.5 server that has had it OS (nt4.0) reinstalled on c:.
sql was installed on d: and all of the dat files are still there, i need to recover data from one of these, i tried install on a new machine and creating a device/db with same name and then over writing the file with the old one but enterprise manager shows it as suspect and will not let me work on it.
Any assistance would be appreciated.
Dominic
Hello
I have a database with two data files configured under primary filegroup. Both the files are set to grow automatically till the disk space is full and both the data files are in the same drive on same physical disk.
When the transactions are happening in the database,how the data is getting distributed on both data files?
Will the transactions is inserted in to the primary data file first and once that is full it will start occupying the secondary data file?. How this distribution is happening?
Thanks in advance
Regards
Arvind
Hello all,
I am installing SQL Server 2005 on our server and have researched with no success on how to redirect the Data Files and Log files to a different drive, e.g. D: drive. In SQL Server 2000 you specified where you wanted to place the LOG files and Data files upon Setup.
I have looked everywhere in SQL Server 2005 install and cannot find where I can tell it to place the Data and Log files. Please point me in the right direction.
Your help is greatly appreciated,
Dan
hi
just wondering if any expert out there can answer my question.
i got a database separate into 3 datafiles in 3 different drives.
i will name it A, B, C.
A datafile size 30G, B datafile size 15G, C datafile size 15G.
and the drive for datafile A is about full. so is there anyway i can more some of data from A datafile to other data file? or since A+B+C =60G can i make it all 20G for each one of them by any command? thanks!!! :)
Hello,
My first post, I don't know much about MS SQL.
I have the .mdf and .ldf files from an MS SQL 2000 server. I don't have MS SQL server running on my home machine. I'd like to be able to extract the data from the .mdf file to something I could use on my home machine. XML would be good, CSV would be ok too, maybe even some way to import into MySQL. Any help would be appreciated, also if I've some how missed the forum faq, please point me to it.
I'm kind of new to sql server (but experienced in Oracle) and I've got a couple of questions I wanted to bounce off you guys.
I'm implementing a SQL server cluster right now (2 node on Win2K3, shared EMC DASD for databases). We're at the very preliminary phase of this. I did an install and had my resource group set up with all of my disks on it. When prompted for the data file drive, I gave it one, but it put all the tlogs for the 'out of the box' database on that same drive as the data files (i.e. master, model, tempdb, etc.). The doc is a little vague in some of these areas (i.e. it says separate logs and data files on different disks, but then never actually tells you how to do that).
Now, I know how to specify the default paths for data and transaction logs for any NEW database I create and that's not a problem. However, my question is, how do I 'move' the tlogs from the databases created during the install? I've tried a detach, move tlog to separate physical drive and then reattach the db, but whenever I do this, SQL server wants to create a new tlog for the db on the same old drive as the datafile. I also can't delete the original tlog from a particular database even after I've created an additional tlog on another disk.
Any help is much appreciated. I'm more or less looking for the strategy any of you might take to set up this initial phase.
We have a large Database (91 GB) that is currently in one large data file. Now that we have muliple disk arrays I can split that up on I would like to have a couple data files. My question is, what is the best way to split this up? Should I keep one primary file group and just create another file, or should I create a file group for indexes and put those on that? This database is used for reporting only so it doesn't really have any writes being done on it.
Thanks much.
We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.
How can I combine two data files (i.e .mdf and .ndf). We had two data file in our old server due to disk limitation. In the new server we don't have such limitation so we like to consolidate the two file in one. What is the best way to accomplish that?
Thanks
Hi,
How do you defragement SQL database data files? Running the DBCC DBREINDEX , DBCC INDEXDEFRAG, does that minimize fragmentations of the tables and the indexes files or is it just the index files?
Thanks in advance
a
Hi all
I'm a newbie with SQl Server But somewhere i read that SQL server Data can be Recovered through *.LDF files
now i want to know if i open a table in Enterprise-Manager and delete some records through DELETE statement or
manually, is it possible to recover those deleted records or not? if so how?
Thanks in advance.
Regards.
Hi All
i am bit confused about how data and log files grow in databases. suppose i turn off the auto grow and restrict the maxsize upto some limit but size of data/log file is less then maxsize at some stage because what i understand is size of data/log file keep changing depends upon the activities going on the database. in future if data/log file need to grow can it grow upto the maxsize without turing on the auto grow.
regards
Can someone help me out in telling me what a data cube is and if you have any examples I can look at?
We are in the process of using data cubes through SQL. What we are looking for is to be able to have summary of data, but if we wanted to click on a grouping we can go to the details of the data.
I just wanted to know if there was any infomation/websites I can go and look at.
Thanks,
Wendy
Hi guys,I have been trying to write data from a database to XML files andalthough i have been successful in doing that, the file(s) that are madeare not of the form that I would want.I am using:SQLDataSet.WriteXml(strFileName, XmlWriteMode.WriteSchema);to generate the files where strFileName is the name of the file. Thefiles when viewed with XML Editor are seen as lines of data instead ofthe regularly generated XML where each row is one below the other. Whenviewed with WordPad/Notepad i see elements />< betweeen the dataand somehow I think this has to do something with the problem.Can someone help me with this?? Any help or suggestion would be greatlyappreciated.Thanks.*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View Related