I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.
Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?
Corruption was occurring in the backup process and was not related to SQL Server itself.
I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.
Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?
Corruption was occurring in the backup process and was not related to SQL Server itself.
quote:
> Backing up a large '78 GB' database to a compressed folder
> gives this error: BackupMedium::ReportIoError: write
> failure on backup device '*replaced file name here*.BAK'.
> Operating system error 33(The process cannot access the
> file because another process has locked a portion of the
> file.). The BAK file size should be about 66 GB. This
> backup works if the target is an uncompressed folder on
> the same drive, and it did work the first time it was used
> to the compressed drive. The system reports over 100 GB
> free space on this drive.
Will backup get completed quickly (the time taken for backing up large databases) on backup device or using backup command? If backup device, why?
You are talking about 2 different things here. You use the backup command to backup the DB and the backup device to store the backed up files.
Unless I dont understand your question.
|||Hi Dinakar,I need to know which one completes backup faster, backup device or by using backup command. I heard that backup device completes backup quicker than using backup command.
Thanks|||
It's the same procedure, whether you commit it via a GUI (window menu->next->next...) or command prompt (BACKUP command) it makes no difference.
In both cases you use a backup device to backup your database. If you are a learner you'll probably use the user friendly windows menu. If you are advanced you'll probably use the SQL command: BACKUP
|||Have you checked the same on your environment?
IMHO I don't see much difference in between and it makes a lot of difference betwen normal disks & RAID based ones.
BTW do you have any issues in your environment for such behaviour, if so let us know.
|||Hi Satya,Im using RAID disks, when backing up 200GB of database, backup device completes faster than backup command. A 30 min difference..
Thanks|||If SQL Server stripes physical devices that have different input/output (I/O) throughputs, SQL Server optimizes for speed. This means that faster devices receive more backup data that is written to disk than the slower device in the same period of time. In SQL Server 2000, regardless of the I/O throughput differences, SQL Server tries to distribute the backup data evenly to the devices. In this case, the slower disk may become a backup bottleneck in terms of performance. If increasing performance is your primary goal, you must avoid using the slow disk in striping and use disks with comparable throughputs instead.
I have couple of questions about backing up a database
1. How to create a backup file (using script) which only contain the data of a specific time range?
2. How to restore the database with serval backup files; for example, I got backup for june and july, how can I restore them into one mdf file which contains the data of both june and july.
3. how can I backup a database to a remote computer?
Thanks for your concern on my Qestions
hi,
Frankie wrote:
I have couple of questions about backing up a database
1. How to create a backup file (using script) which only contain the data of a specific time range?
2. How to restore the database with serval backup files; for example, I got backup for june and july, how can I restore them into one mdf file which contains the data of both june and july.
I do strongly suggest you to start reading about backup functionality in SQL Server starting from this overview, as it seems you really did not understand what a backup is
3. how can I backup a database to a remote computer?
this can be easely done, even if not directly recommended (at least not by me).. usually you perform a "local" backup and, later, you push or pull that backup remotely via scheduled tasks, or scripts or the like, in order not to load to much the SQL Server service with tasks not directly related to it's main activity.. anyway, you have to grant the account running SQL Server service enougth NTFS permissions on the remote share you are dealing with... in our case, SQLExpress usually runs under Local System, Local Service or Network Service builtin accounts, and you can not add remote permissions to these ones.. you have to define a domain account to use for the service and grant this one appropriate permissions (AD and NTFS) to complete the task on the remote share..
regards|||In addition to Andrea, the transfer to normal UNC paths are not reliable, leading to the problem that the transfer could stop somewhere in the middle of the process, unless you use something like a SAN or NAS storage which has a much more reliable way for tranferring. Use the local copy to copy the files (e.g. usign Xcopy with a restartable copy process) to make sure the file was really copied).
Jens K. Suessmeyer
http://www.sqlserver2005.de
I've problems with backing up the certificate (on the Witness Server). On principal and mirror, it works fine (Win 2003 Server, US English), but on my local PC (XP Prof, German) what i use as witness Server following backup command raises an error:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'something$Strong123';
CREATE CERTIFICATE HOST_W_cert WITH SUBJECT = 'HOST_W certificate for database mirroring', START_DATE = '01/01/2006', EXPIRY_DATE = '12/31/2050';
BACKUP CERTIFICATE HOST_W_cert TO FILE = 'C:\HOST_W_cert.cer';
The error message is:
Msg 15240, Level 16, State 1, Line 1
Cannot write into file 'C:\HOST_W_cert.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
I connect to the database with a domain user what is a local admin on the witness server (my local user), so this couldn't be a permission reason. The file doesn't already exists, so that is not the problem.
What could it be?
Greetings, Torsten
Actually, yes, it is. The backup runs under the security context of the account that the SQL Server service is running under. If this account doesn't have access to the root of C, then the backup will fail.|||I was not able to change the services' account, but i think you are right. I used another Win2003/ SQL2005 Instance as Witness and it works fine.
Thanks a lot,
Torsten
I need to start backing up my databases. They are almost past debug stage and about to be filled with data I will value. So far I have been copying the whole directory to an external HDD (USB connection) but I never restored anything from the backup. My databases are simple: No web connections, there is only one client: my C# application and most likely this will remain so for a long time. The databases may grow in size considerably, however.
I looked into BACKUP command. I have the following questions:
(1) What is wrong with simply doing it the way I've done it so far: copying a directory?
(2) I found a post by Glen WIlson at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=487447&SiteID=1 where he offers a C# code for a database backup. My next question is:
I could not find the class he is using: BackupDeviceItem in MSDN help for VS2005. How can I try the code if the class is nowhere to be found?
Thanks.
Hallo Alex,
BackupDeviceItem is in the Namespace Microsoft.SqlServer.Management.Smo
Maybe you must add a reference to Microsoft.SqlServer.Smo
For documatation about this class, have a look at MSDN2
Servus
Klaus
Thank you very much, Klaus. It worked, however, I still cannot get the BackupDeviceItem class in my VS2006 management studio. It is just not there. I mean, it is now in the IntelliSense all right but not among the help items. According to Intellisense the class appears to be overloaded but naturally a single signature is given in the dropdown menu.
The link you've provided is helpful. I also found more info in MSDN by following the page references. Still cannot find a decent description of the class. There should be methods, I reckon. It is not a major problem, however, at this point.
I still want to get an answer to this question:
What is wrong with just copying a "Microsoft SQL Server" directory wholesale as a backup method?
Thanks.
|||OK, I have found all methods. That was a false alarm. Still I want to get an answer to my major question: can I just copy the SQL Server directory?|||SQL Server keeps a lock on it's files while they're attached, you may get an error trying to copy them.
Additionally, if you copy the file while a transaction is in process, you'll get the file with an incomplete transaction. Take the cannonical example of moving $100 from Savings to Checking; the transaction consists of two parts, a withdrawl from Savings and a deposit to Checking. What happens if you make a copy of the file after the withdrawl but before the deposit? You database corrupts, so you put the copy back, but it only represents the withdrawl, so you just lost $100.
The whole point of a transactional database is to ensure that this doesn't happen. Blind file copy excludes this mechanism.
Mike
|||"SQL Server keeps a lock on it's files while they're attached, you may get an error trying to copy them"
Thank you Mike. It has been taken care of. Before I copy I go to Task Manager and kill two exec files that will give me errors otherwise: sqlwriter.exe and sqlservr.exe. Then I copy directory with no problem.
My world is much too small for a corruption to occur while the files are being copied, I think. It is a quick process and I monitor it. USB-2 is a fast channel.
But in the future I will do classical backup. Now it is just too hectic.
Thanks.
|||
Hey Alex,
As long as you're releasing the lock, which you are, you should be find.
As alternative to killing the services arbitrarily, you could also just run a script to detach the database before you copy the files. This will close out any transactions before detaching and releases the locks. Then you can reattach. This would be the way to go if you're trying to automate things since it's scriptable.
Mike
