Database Backups
Mar 16, 2001Why is my database backup file so big? It's about 10 X bigger than the actual data files
View 1 Replies
ADVERTISEMENT
DB Engine :: Will Transaction Log Backups Not Free Up Log During Full Backups
Nov 15, 2015The 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 RelatedDatabase Backups
Nov 27, 2000I 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.
View 1 Replies View RelatedDatabase Backups
Mar 16, 2006I 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
Any help or suggestions will be much appreciated.
Dyanamic Database Backups In 6.5
Nov 12, 2000Hello,
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
Anything else I may be missing?
Regards,
Hitesh
SQL Server Database Backups
Jul 28, 1998I 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.
Database Backups && Compression
Jun 19, 2007Hi All
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 :-)
Types Of Database Backups
Jun 4, 2008HI Gurus,
Can anyone of you please post the link related to types of database backups which i can use for my reference purpose.
Thanks,
ServerTeam
Auto-restore Of Database Backups
Feb 26, 2004Our 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?
Is there a better way to do this?
Cluster Database Backups Question
Sep 13, 2002In 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.
Thanks very much for the help!!
solart
SQL 2012 :: User Database Backups
Jun 2, 2014I 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 ?
How To Check For Failed Database Backups
May 8, 2007Hello 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?
Thank you!
Mario
Database Backups Growing Exponentially
Jul 23, 2005Hi 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!
View 2 Replies View RelatedLog Backups On Heavy And Busy Database
Apr 30, 2008
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.
what are risks and suggestions.
Thanks,
Database Backups And I / O Frozen Events
Jun 5, 2015I 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
[Code] ...
Named Backups Using Database Maint Wizard
Aug 20, 1999Has anyone been able to find a way to name the files within your backup using the Database Maintenance Wizard in SQL 7.0?
Thanks for your input?
Copied Database Maintains Original Name In Backups
Mar 27, 2007I 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,
View 2 Replies View RelatedVeritas Not Truncating Log Files After Database Backups
Jan 8, 2007Michael 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 RelatedError Restoring Database With Diff & Inc Backups
Sep 10, 2007I 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.
Load SQL 7 Database And Transaction Log Backups To SQL 2000
Jul 20, 2005Is 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
View 4 Replies View RelatedIs It OK To Copy Database Backups To Tape Using Compression?
Dec 27, 2006Hi, 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?
Thanks
SQL Server 2008 :: How To Check If The Database Itself Is Encrypted Or Only The Backups
Jul 9, 2015I queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.
Does that mean that all those 4 DBs are encrypted ?
How to check if the database itself is encrypted or only the backups ?
Note :- I can see backups of key in a particular folder.
Verify Multiple Database Backups Using Single Statement
Oct 25, 2007Hi all,
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:-)
Thanks in advance,
What Procedures Need To Be Done On A SQl Sever 2005 Database Before Commandline Backups Can Be Successfull
Jun 12, 2007I 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.
SQL Tools :: Lost All Objects Suddenly Only Remains Name Of Database No Backups Available
Nov 8, 2015I recently installed a trial version of Sql12k on my laptop. Since 1st of November I've been creating tables without problems (15) and different schemas.I would like to emphasize that both mdf and ldf files are allocated in my USB3 portable disk. is that a problems for Sql12K? SSMS had stuck just for 20 sec or so. Then, system itself kill the task. A huge surprise when I restarted again... all the databases including the last one.. without objects. I mean, user databases...
Where are my logins? Where are my tables and schemas? Jesus christ.Sql logs inform the following (in all the moment my USB hardisk was properly connected)
11/08/2015 16:00:30,spid19s,Unknown,The operating system returned error 21(El dispositivo no está listo.) to SQL Server during a read at offset 0x000000 00140000 in file 'f:enriccopia_de_bbbb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency
[code]....
Restoring Database With Backups Files(Data And Log) Having Different Creation Times....!
Nov 19, 2007Hi,
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,
Pros And Cons Of Using Transaction Replication Doing Initialization From Database Backups
Jun 27, 2007
I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?
SQL Server Admin 2014 :: Database Backups And AlwaysOn Availability Groups
Oct 16, 2014This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.
View 1 Replies View RelatedBackups
Jan 14, 2007
Hi,
I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?
Any suggestions?
Thanks,
Dale
SQL 7 Backups
Mar 15, 2002What backups options do you have if you do not have a tape drive connected to your SQL 7.0 Server ? Can you run a SQL Maintenance Plan and backup the databases to a remote server. If not, can ARCServe 2000 backup the SQL backups to another server with a tape drive ?
View 1 Replies View RelatedBackups
Jun 27, 2001I'm having trouble backing up two databases to tape. Should I be using a backup_device?
have a job defined with the following in it:
backup database test1
TO TAPE = '.Tape0'
with ...
go
backup database test2
TO TAPE = '.Tape0'
with ...
go
where should the init go?
Backups
Aug 28, 2001I've set up several backups in the past using the backup screen. I've also set up a Maintenance Plan to back up my database using MyDbName* where the * is the familiar suffix automatically generated by the system when the backup runs every night.
The trouble is that, even though I've deleted old maintenance plans and scheduled backups, they're not going away. Every morning, I see new nightly backups of old backups etc., in the backup folder. Furthermore, the maintenance plan I want to backup my database isn't creating a backup file.
How can I make the old backups stop running, and make sure the new backup runs? I've gone through everything and can't seem to find any residue of these old scheduled backups--yet they still run! Can anyone help?
Backups
Nov 13, 2000We are having problems with our tape drives so we are attempting to back up our databases to disk. I have a whole server on the network I could use for this. When I use the backup that comes with SQL 7.0 it won't allow me to choose a network drive. It looks like it must be a local drive. Is there a way around this so I could back it up to this other server. I jave 32 gig available on the server which would be plenty. We don't have enough disk space on the local drives to back it up. We have been having problems with our current tape drive company Exabyte. Does anyone have any recommendations. We are thinking of switching to another company.
View 3 Replies View Related