I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location.
My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file.
Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site.
Any suggestions? Thanks in advance guys n gals :-)
Hi, I back up SQL Server 2000 and SQL server 2005 databases to hard disk using the SQL Server Backup Wizard and maintenance plans. Then, I copy the resulting backups to tape using third party tape backup software and compression by the backup software and hardware. I do not use the SQL Server Agent available for the third party backup software. Is this acceptable, or does the compression performed by the third party backup system introduce opportunities for database corruption or other negative effects?
I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.
I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.
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?
I know that I have read not to backup a database over a netwrok. So I am curious as to what others are doing out there. BAckup to your local hard drive on the server and then move the backup files to a repository some where on the network? Do others have a file structure out on another server that stores all of the backups from all of the different servers that have SQL 7.0 on them? We are a small company and are just starting to migrate data to SQL Server 7.0.
I have to perform a backup for disaster recovery purposes before an application upgrade. The upgrade will alter the database and stored procedures. My cuurent backup takes a backup of master and msdb weekly. The user database uses the Full Recovery model and is backed up daily at 21:00 and the logs daily at 13:00. Assuming the databse is modified between the last backup and the upgrade starting at 9:00am what should my backup stratergy be for roll back purposes. 1) backup master, msdb and the user Database to a different location than the normal backups. Use these to restore if required 2) backup the master, msdb and user databases using the same jobs and therefore overwriting the original evening backups 3) do nothing and just restore master and msdb from a backup and replay the logs to a given point in time for thr user database should the upgrade fail
Can anyone tell me what the impact of dynamic database backups in sql 6.06.5 will have on users using the database?
Will their user processes be blocked? Will their queries run slower than normal (how slower)? Will there be a lot of locking activity as the SQl tries to backup? Will the serverdatabase run slower
I am looking for the best method to backup SQL Server databases. Currently we are running a dump database statement to disk and backing up the files to tape through Arcserve.
One problem that I am having is the statement to dump the database. I would like to retain the dump for at least three days and be able to restore the database from any one of those three days. My current statement is: "DUMP DATABASE CHOISDAT TO DISK=`D:BACKUPCHOIS.BAK` WITH NOUNLOAD , STATS = 10, INIT , RETAINDAYS = 3, NOSKIP"
but, every other day I receive the message from SQL executive: "Can`t open dump device `D:BACKUPCHOIS.BAK`, device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)"
What am I doing wrong? Any suggestions?
P.S.
Is there anyway to tell the Maintenance Wizard to delete the backups. I tried using the wizard but the backup files still remain on the disk and I have to delete them every week.
Our DBs are set up to do a full backup once a day (late at night) and then transaction log backups during the day at shorter intervals.
I want to setup a dev database on the same server. I want this database to be an automatically restored copy of the live database. So every night, after the full backup of the live DB, I want to restore the live DB to this dev DB.
Can this be automated? Can the restore automatically stop the dev database in case some open connections exist?
In a non-clustered environment, I am under the impression that backups must be to a local disk or local tape device.
My plan is to have a separate disk in a clustered environment on a shared array for holding my backups, until they can be transferred somewhere else.
My question is, will SQL Server 2K support backing up to the disk in the shared array since it is (I believe) not considered a local disk? What key points may I need to know.
I have inherited a new SQL Server 2008 database server and can not figure out how my user databases are being backed up. This database server is running under a VM.
All the user databases are being backed up nightly per the SQL server log. The backups are written to a virtual disk and is kicked off by the NT AUTHORITYSYSTEM user. I can not see the virtual disk. A restore task does not provide any information about the last backup. I have created a new database, and it is automatically included in the next set of backups.
I have looked at the windows event viewer with out any luck. There are no SQL Server Maintenance Plans or Agent jobs that call a backup. I have also checked the Windows Task Scheduler and can not find any task that does a backup.Could the backups be called from another server ?
Hello everyone! Looking for some insight here on database backups that fail.
We have many SQL servers that we maintain by storing Job/Maintenence Plan history on a central server, which then emails out daily reports to let us know what backed up last night and what didn't
This was easy to do in SQL 2000, not so much in SQL 2005. I have put together a query that gathers the info I need for the successes:
SELECT DISTINCT '00000000-0000-0000-0000-000000000001' AS Plan_ID, mpld.line1 as "Plan Name", bud.database_name as "Database", mpld.server_name, 'Backup Database' as Activity, mpld.succeeded, bs.backup_finish_date, DATEDIFF (MS,bs.backup_start_date,bs.backup_finish_date) as Duration, bs.backup_start_date, mpld.error_number, mpld.error_messageFROM msdb.dbo.sysmaintplan_logdetail mpld INNER JOIN msdb.dbo.backupset bs on (select convert(char(12),mpld.start_time,109))=(select convert(char(12),bs.backup_start_date,109))-- on bs.database_name=bud.database_name inner join msdb.dbo.bu_dbs bud on bs.database_name = bud.database_name WHERE mpld.succeeded = 1 and mpld.line2 like 'Backup%' and bs.type='d' and bs.backup_start_date > ( SELECT CONVERT(char(12), (GETDATE()-1), 109) ) ORDER BY bud.database_name DESC
But I am having trouble using a query to determine the databases the FAILED during backup. MSDB.BackupSet and MSDB.SYSMaintPlan_LogDetail really have nothing,because often times, even if a step in a Maint. Plan fails, the plan finishes reporting success.
Does anyone know of a good way to gather info about failed database backups?
Hi All.I'm currently maintaining 4 servers - 1 for public/customers and 3for backups, development, etc...I regularly backup the entire SQL database for our public server andrestore it on each of the other servers. Lately, however, the databasebackups have grown (in size) incredibly fast - they've gone from about200MB to 2+ GB in 2 months. (I wasn't entirely surprised by this atfirst since our client traffic has drastically increased as well.) Theweird thing, though, is that (on two of the backup servers) when Irestore the backup then use those servers to create a new completebackup, the new backup is only about 200-300 MB in size.My assumption is that there's some kind of setting buried deep insidethe sql configuration allowing it to compress or otherwise alterbackups. Does anyone have any ideas/thoughts as to what may be causingthis issue?We're using SQL Server 7 on Windows 2000 servers.Thanks in advance.GreggJoin Bytes!
What will be best procedure for the following situation.
Heavy traffic database on daily basis. G growth every day. so Full backups every nights are needed. Vendor recommends not taking Log backups but copy just log files over other location. will this help avoid degrading the performance during business hours.
if i don't take log backups, i am not able to recover Point in time if needed. also log files can then grow faster and then i will have to shrink it more often.
I am trying to find out where is the maintenance plan which is backing up SQL Dbs on its own at 12 am daily where as we didn't scheduled maintenance plans at all. We see I/o frozen and resume events every day in event l/o
Log Name: Application Source: MSSQL$MSSQLSERVER2K8 Date: 5/4/2015 12:00:23 AM Event ID: 3198 Task Category: Server Level: Information Keywords: Classic
I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?Here is the BACKUP script:BACKUPdatabase vgs_prod TODISK='\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK' with COPY_ONLYHere is the messages I received from this BACKUP:Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec). I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above. Thanks,
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?"
I backup a database at the begining of each month with a full and then do nightly diffs on it.
For the same database I run daily fulls and 10 minute log backups.
these two backups create / append to two different backup files.
The problem im having is that I cant restore the Differential backup set. SQL seems to restore the full just fine but alwasy throws an error when its about to start to retore the last diff. now forgive me but I clicked OK on the message and I cant find any record of the error in the logs but its something like:
"SQL cannot restore the database as the database has not been restored to the previous correct state"
is my 10 min TS log backups screwing up the DIff chain somehow?
this is really doing my head in. any help appreciated.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
Is it possible to load both the SQL 7 database and transaction logbackups to SQL 2000 ? I assume it will perform the upgrade during theload.Thanks,James
I have been wanting to compress my database. I am not really sure how this is done. I was looking on Enterprise Mangr. and if you right click on the db and go to all tasks, there is an option to shrink database. Is this the way you would compress your database, or are there other ways of doing this?
I have taken more than one database backups and i have an idea that we can verify single backup using "restore verifyonly from disk='<path>'" statement. Now my question is Could we verify all(More than one backup) backups with single statement? Any suggestion would be helpful to me:-)
Can anyone tell me whether there is any data compression in SQL6.5. Have concerns with network traffic, and was wondering if data compression was a function SQL6.5, or if the data compression have to be coded into the actual database?
My application send/retrieve large data from and to the database over the internet. Is there anyway or method that i can compress the query before submit to database server? really appreciate any advice and comments.
We have run some tests on our application. Average message is about 2.5 MB. Messages are send once every 30 minutes. This is 3.5 Gb per month for one site. Now we already have three sites that will be sending this messages. This will be VERY high load on the WAN channel, and will cost us a LOT of money .
Does SQL Server replication impliment any kind of compression? It seems to me that this would be very helpful for congested WAN links and costly merge replication.
Hi, I was looking for a column compression functionality in SQL Server Compact and it seems that it doesn't exist (maybe I'm wrong?). I wonder if the SQL Server Team plan to implement column compression and if yes, when can we expect it?Thank you for your help!
I have a sql sever 2005 express edition database but when i run my backup command from the command prompt, i get this error message . The .Net SqlClient Data Provider has received a severity 16, state 11 error number 911 on line 1 of procedure on server KSS15SQLEXPRESS: Could not locate entry in sysdatabases for database 'DATABASE1'. No entry found with that name. Make sure that the name is entered correctly. The .Net SqlClient Data Provider has received a severity 16, state 1 error number 3013 on line 1 of procedure on server KSS15SQLEXPRESS: BACKUP DATABASE is terminating abnormally. DATABASE one is the name of the database i want to backup. Now it looks there so many databases some of which i have no idea where they are. It looks like there some system stored procedures i have to run first before my database file can be located by my commands. It would be wonderfull if some one pointed me to an article that covers this initial part thoroughly.
