Is There A MS SQL Backup Tool That Will Zip Or Compress The Backup Files?
Feb 27, 2007
Currently we use a SQL maintenance plan to do a full backup of all our databases daily (about 40 databases on our production server). As you can imagine, this eats up disk space quickly so currently we manually zip the backup files and/or move them to an archive drive. I considered writing an application to walk through the backup folder structure and zip any .bak file it finds, but I know there are some third party tools out there that will backup/restore a MS SQL database.
I was wondering if any of these also zip the backups once they are created. Any recommendations or suggestions are welcome.
SQL 7.0 SP1. We do SQL Server backup and also third part backup (with SQL agent active) of SQL Server databases. Sometimes happen this situation:
01.00 SQL Server backup of DB1 01.00 third part backup of DB1 02.00 SQL Server T-LOG backup of DB1 03.00 SQL Server T-LOG backup of DB1 03.30 SQL Server differnatial DB backup of DB1 04.00 SQL Server T-LOG backup of DB1
or this situation:
01.00 SQL Server backup of DB1 02.00 SQL Server T-LOG backup of DB1 03.00 SQL Server T-LOG backup of DB1 03.10 third part backup of DB1 03.30 SQL Server differnatial DB backup of DB1 04.00 SQL Server T-LOG backup of DB1
My questions now are: Are my SQL Server backups valid (DB,Differenatial and T-LOG)? In case of a restore operation, can I use my SQL Server backups without problem? In other words, can we leave the third part backup tool active or it should be taken away?
Can anybody clarify this for me? Thank you in advance. Franco
I have a SQL 2012 enterprise server, and I'm using Commvault as my backups. So commvault can restore a .bak file to my server, but it cannot use sql compression on the file apparently. So what would be a 150GB .bak backup file is now 600GB. I have to manually upload these files to an auditing firm on an sftp server and the transfer times are now huge.
Is there a way to use something in sql to compress this already existing .bak file down?
Is there any improvments in SQL 2008 backup methods such as spliting backup files in manageable size(s), compress the backups and/or improving the speed of backup?
Though there are "commercial" tools available, it would be nicer if Microsoft SQL team can incorporate some core needed features in these areas for Small/Medium size businesses.
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin] GO IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL DROP PROC usp_DeleteBackup GO
I scheduled automatic backup process but its only showing backup of the only one .sql file in the backup folder. Other created .sql files are not backed up. Why is it so?
I want to do sql db backup.But how can I backup db to split backup files? The reason I want to split the backup file is becasue single file size is too big and I want to write to dvd.
Hi, I know SQLLiteSpeed is one best tool for MSSQL server Backup Recovery. And also I wanted know that the top 5 best tools for MSSQL server Backup/Recovery.
I really need a tool that doesn't make a backup of all content of my database, and I don't want a script with just a schema of it. I want a backup of just some columns and the data that has in them. I know that It has a tool, but I never heard about it, and It's too important, so much that now I'm needing.
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
I am trying to find a pure web based backup restore tool that can be used on MS SQL 2000.
I have found a few that claim to be web based, but then it tells you to log into your MS SQL server and make some adjustments.
I know that microsoft Enterprise manager comes with a backup / restore tool. but 1) i dont want to have to install a complete copy of 2005 server, and 2) i dont hav ethe physical space to do it right now anyway. all i need to do is move 2 databases from one account to another.
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
hello! can you take a look at my question? We use ARCServe to backup exchange server file (priv1.stm and priv1.edb) every night. When I select both of the files and start to back up using ARCServe. It showed me that Backup Operation Successful.
When I check the files, it didn’t backup both of the files. I don’t know why. Do you know how to backup these two files?
Hi,We are about to install MSSQL Server 2000, on a Windows XP HomeMachine. However, we have servers we could set routine backups of filesto be done to. What what be the best way of doing this?Is there functionality in SQL Server 2000, where we can say dump alldata definitions, accounts, and data to files on this drive at regularintervals?What other suggestions do you have apart from obviously the usual RAID,and Tape Drive stuff?ThanksDavid
I am going to move to a different host. Can I get back up files of my SQLServer database (from the host) and use those to set up on the new host? Andif so, is that what I should be asking for - backup files? Someone told methere should be 2 files.Thanks
Hi All,I searched the archives but I could not find any useful stuff for me.I can backup a database on SQLExpress like USE masterEXEC sp_addumpdevice 'disk', 'Store_1', 'c:Store_1.dat'BACKUP DATABASE Store TO Store_1Here is my problem: If I use logins,roles etc. in an application automatically a mdf file is created. Or I can create a new sql database. SQLExpress dynamically use the mdf file when connection string defined. This mdf file is not defined as a database on SQLExpress. Because SQLExpress has not a gui, I cannot see the registered database. How can I backup any sqlexpress mdf file without deattaching or attaching?Thanks
HiWhat is the easiest option for Exporting ALL of my SQL Server tables into a CSV file (either separate CSV files for each table or one big file with all the table columns and data) ???I just want to Backup my SQL Server database like we backup MySQL database using phpMyAdmin. Unfortunately, my SQL hosting company does not allow backups for free.Thanks for help
Hi, I am developing a tool that takes snapshot of a particular database. By snapshot I mean, attaching the .LDF & .MDF files. Would you please let me know how to access these files & take a backup of the same. And also how to restore them back. (using c# programming) Thanks, Archana A.A.
I need to know how big SQL Server 7 backup files are in comparison to the database size. For example if I have a database that is 300 Mb and I do a complete backup to disk with SQL Server 7 will the backup file be about 300 Mb?
I hv worked in 6.5. There we take backup through the command 'Dump Database'(e.g---> "Dump database master to masdump WITH INIT." ). As for as I know, in 7.0 we are taking backup using the command "Backup Database". Is it possible to use "Dump Database" command in 7.0.
Hello everybody. I have SQL2000 sp3 standard with 50 db's
All db set for full recovery and autoshrink Backup done with Tivoli
full backup once a week log backup done every 12 hrs
Problem .. shrinking logs
every 20 min I run job DBCC SHRINKFILE (My_db_logFile) for every db
70% of the time I am getting message similar to ------------- Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use. -------------- 1. I checked with sp_who2 The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."
Why i getting "Cannot shrink ..." ?
even if job runs right after backup of the log files ,I still have messages.
i want to delete the backup files older than 15 days. how can i do this? Actually i backup the db and log using the TSQL, and the backup files are in d:ackup folder. I tried sqlmaint,xp_sqlmaint but its not running .
I have a large (200+ GB) database with many (100+) files. Please don't ask me why I did it this way; I inherited this database -- really, it wasn't my idea.
My predecessor also seemd to think that backups were unnecessary; there have been no backups of this database -- ever.
While we cast about for a good long term solution, I am trying various short-term options. One I want to explore is to back the database up in chunks -- ie, by backing up individual files. I created a test database with five files (there is only one filegroup on the production server). Here is the DDL:
-- ============================================= -- Create database on mulitple file groups -- ============================================= IF EXISTS (SELECT * FROM master..sysdatabases WHERE name = N'MultiFile') DROP DATABASE MultiFile GO
LOG ON ( NAME = MultiFile_Log, FILENAME = N'e:MSSQLDataMultiFile_Log.ldf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10%) GO
I have tried the following backup script:
BACKUP DATABASE MultiFile FILE = 'MultiFile', FILE = 'MultiFile2' TO Backup01 WITH INIT
BACKUP DATABASE MultiFile FILE = 'MultiFile3', FILE = 'MultiFile4' TO Backup02 WITH INIT
BACKUP DATABASE MultiFile FILE = 'MultiFile5' TO Backup03 WITH INIT
And here is the restore script:
RESTORE DATABASE MultiFile2 FILE = 'MultiFile', FILE = 'MultiFile2', FILE = 'MultiFile3', FILE = 'MultiFile4', FILE = 'MultiFile5' FROM Backup01, Backup02, Backup03 WITH MOVE 'MultiFile' TO 'E:MSSQLDataaMultfile.mdf', MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile2.mdf', MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile3.mdf', MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile4.mdf', MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile5.mdf', MOVE 'MultiFile_log' TO 'E:MSSQLaMultFile_Log.ldf'
However, running the Restore script generates the following error:
Server: Msg 3259, Level 16, State 1, Line 1 The volume on device 'Backup02' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
I'm not sure what to make of this. What do I need to alter in either the backup script or the restore script to make this work?
I am trying this because my objectives are to: 1. Limit the amount of work that the server is performing during any one given backup session. The idea that I have is to backup the database in chunks using a rolling 3-5 day window. 2. The database must be up and operational 7x24x365 (except for one 4 hour window each month) 3. This is not the long-term solution; but I need something to tide us over until we can purchase additional storage capacity.
I appreciate any thoughts and or guidance you can provide.
I have had trouble with maintenance plans not deleting expired backups, & this filling the hadrdisk. i have now done an agent job using tsql backup with init/skip, so it just overwrites. problem now is that it fails saying the backup device isnt available (cant delet the file from OS either) - so this was obviously the prob with the maintence plans too - sql/os doesnt seem to be releasing the file after it is completed and a server restart is needed to release it... anyone know how to sort this? thanks des
When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.
Looking at the documentation, it would suggest that as well as data files, when a backup file's created it will also be zeroed out unless the service a/c's been given Perform Volume Maintenance.
We take our backups to dedicated backup servers, meaning backup performance should improve significantly if instant file initialization's given to the Service account logins for the source boxes if I'm right.
I have sent a mssql backup file in .bak format to my host admin and asked them restore it. but they told me there are 3 files with 3 different dates in that backup what does it mean? should I ask them to restore all of them or just the last one? Thanks very much in addvance (context: MS SQL 2005 express)
I have to migrate SQL 2000 DTS-Packages to SQL 2005. My Problem is that I didn't saved my DTS-Packages as files and the SQL 2000 Server exist no longer.
Actually I have only the old hard disk with the SQL 2000 databases. Do you have any ideas what can I do?
