How Do I Make A Backup Device File Smaller ?
Apr 9, 2008Hi
I have a backup device file ... ".bck" whick has grown pretty large.
Is there any way I can reduce its size ?
Thanks
David Greenberg
ADVERTISEMENT
Mdf File Become Smaller And Smaller After Shrinking Db
Jul 20, 2005Hi all:I restored one backup database (7.9 GB mdf) on two diffrent servers. Ishrunk them by clicking "Move pages to beginning of file beforeshrinking".After shrinking, one mdf file is 6.7 GB, and the other is 4.2GB. Ishunk again and again:1. the 6.7GB become 5.9GB, 5.2GB, 4.7GB and 4.2Gb (four times)2. the 4.2Gb become 4.0GB (just one more time)It is wired, I am wondering the mdf will be smaller and smaller if Icontinue to shrink them? What is the reason?ThanksWJ
View 1 Replies View RelatedHow To Make Database / Log Size Smaller
Apr 16, 2002Hi all,
I found a database file and a log file over 2G on mssql 2000 server. Actually, they only need around 200M. I try to backup, truncate the database in order make the size smaller. But the size cannot be smaller. How can I do it?
Simon
Difference Between Backing Up To A File And A Logical Backup Device?
Jun 16, 2007What's is the between backing up a database to a file and a logical backup device?
If I point the logical backup device to a file on the filesystem, it's same as backing up to a file? isn't?
Thanks
SQL Server 2008 :: Backup To Device / UNC File / Generates OS Error
Aug 11, 2009It's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...
The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (ServerBSQL_backupsdvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date2009/08/08 06:43:30 AM
LogJob History (Backup_AssetData_2009-08-08)
Step ID3
ServerSQL01
Job NameBackup_AssetData_2009-08-08
Step NameBackup Database - Second Attempt
Duration00:05:33
Sql Severity16
Sql Message ID3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: COMPANYSERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData(
iscsrv-dcm04RISCSRV-SQL01_Backupsdvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The file is generated, the same size as a successful backup, but the job terminates unsuccessful.I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.
I have a few databases that backup to the UNC described above (different files) that don't fail..My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.
My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.
##20090812## UPDATE
I tried another way to do the backup; with the same results:
Backup database Infovest to disk = '
iscsrv-dcm04
iscsrv-sql01_backupsdvc_Infovest_Z.bak' with INIT,STATS=1
[code]....
Differential Backup Not Smaller Than The Full Backup
Jun 6, 2007Hi,
Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.
We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.
We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)
When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).
Is there anything we are missing, why are the differential backup that big?
Thanks for any advice.
How To Backup A Database Into A Number Of Smaller Files ?
Jul 23, 2005To all,How to backup a database into a number of smaller files ?For example, can I can fully backup a DB of 10 MB into 10 files (each 1MB)???The problem I've met is that the DB backup file is too large, over 4GB, and even Winzip can't compress it (after compressing, around 80 %of compression rate is possible)Thanks![color=blue]>From Jason (Kusanagihk)[/color]
View 11 Replies View RelatedSQL Restore Into A Smaller File
Sep 4, 2007Hello there....
I have a scenario where I am trying to set up multiple database instances for multiple test/development environment(s) for each group where the test/dev environment will contain a copy of what was in the production environment.. The test/dev environment can be refreshed on demand based on the prior night's full backup of the production environment.. This is good for our web developers and for training purposes, as the test environment(s) can be played around with, and will retain data for as long as the developers/testers/trainers need it, and then can be refreshed to the most current data when everyone in the group decides they want it refreshed...
Normally, this works out well...
However I am having a file size issue...
The production database was pre-allocated (a long time ago) to a large file size (probably to reduce external fragmentation).... So even though the backup file is only 5GB, the production database file itself is something like 40GB... I believe the production database has a maintainance plan on it already that rebuilds the indicies each weekend, etc...
Anyhow, the problem is that when I restore the 5GB database back into a newly created database file, the file expands all the way up to 40GB again, even though the backup file is 5GB...
Normally this would be fine, the problem is that I am trying to create multiple environments, and I do not have the disk space on my test/dev server for 40GB (plus another 15GB or so for the transaction log) multiplied by each of my test/dev environments... It would be much nicer if I could get this down to 5GB (or heck, even 10GB), since I know for sure that the total amount of data in the database doesnt exceed 5GB, and I have plenty of space on my disk for 5 (or 10) GB multiplied by each of the environments I want to create...
I have tried DBCC SHRINKDB and I have tried DBCC SHRINKFILE with the truncate after the restore, which seems to work but doesn't....
I have also tried to go into the database properties and change the "initial size" but that doesnt do anything etiher
Is there any way to get this file back down to a manageable size after the restore??
Or better yet, is there a special method to restore the database so it wont 'expand' back out to 40GB in the first place??? Perhaps some option to tell the restore process that even though the source database had a 40GB pre-allocation, that the database I am restoring into doesn't need to be pre-allocated??
What Is The Best Way To Restore Production Db In Dev With A Smaller Log File?
Aug 20, 2004I have a Database A in production with 12GB as data file and 8 GB as log file. How do I restore this db in Development with a smaller log file, say 1GB?
I can't shrink the log file or anything in production. What is the best way to restore in Dev with a smaller log file?
Thanks.
Newbie Question Regarding Backup Device And Backup Foile
May 10, 2001This is probably a simple question but I have to ask it anyway. When backing up I can backup to a file on my local drive but I also can create a device to the same location on my local drive. Is this doing the same thing. If I so desire to backup to the local drive(bear with me) what is the difference between creating a device and a file called mybackup or just choosing to backup to a file called mybackup? Should I always create a device? I know these are dumb question but....
Jason
How To Get The Backup History Details From A Backup Device?URGENT!!
Dec 7, 2001How do we know the list of all the backups that are present in a particualr backup device like file# and time of backup taken etc for the purose of restore?
When I run :
RESTORE HEADERONLY FROM BackupDeviceName
it doesn't give that info. Can anyone tell me the exact command to list all the files of a backup device so that I can restore the right one??
Thanks.
Shalini.
Can MsSQL Data File Be Archived And Made Smaller ?
Dec 6, 2006Hi... We have application that connected to MSSQL 2000 database. The database file is getting bigger and bigger over the years. Recently the performance of the database and application is getting slower and slower, my senior oracle DBA told me that I should archive the MSSQL 2000 data file and export the old record to the archive DB. So that the number of record will be less and it will be faster.
I would like to know whether MSSQL 2000 support archiving of the database file ? If yes, what is the way to do it ? I could not find it in Enterprise manager option at all.
BACKUP To Backup Device Or Disk
Sep 25, 2000Is there any benefit in creating a backup device and using that in the backup statement over just using a disk file in the backup statement. It seems like extra work to create the backup device with sp_addumpdevice with the file location. Whereas I could just specify the file location right in the BACKUP statement by specifying DISK as the backup device.
View 1 Replies View RelatedDelete Backup From Backup Device?
Apr 16, 2004I dump my databases to backup devices each night.
However would like to purge old backups - say older than a week - from the device.
Is it a case that I have to drop and re-create the devices every 7 days or can SQL do this for me?
I have seen the RETAINDAYS claus but it applies to tape backups only?
Thanks
Why Use A Backup Device Vs. Backup To Disk?
Jul 20, 2005In SQL 2000, is there an advantage to defining and using a backupdevice versus just backing up to disk?Currently, I've got a SQL Backup job set to run once per day, withtransaction log backups running every hour. The db and backups arewritten directly to folders on an external disk array. I've neverconfigured an actual Backup Device within SQL. Does creating a BackupDevice offer any advantages not available with my current backupmethod?Thank you for any help.Jason
View 1 Replies View RelatedBackup Device??
Feb 23, 2004Hi all,
I want to store bakup file on the computer on netword, so make a backup device on the other computer, it ok. after that i make an full backup, there is an error : "Can not open the bakup device " .
please show me,
best regard.
Backup Device
Mar 15, 2004Hi
is there a stored procedure which allows me to query if there is an existing backup device? or which gives me a listing of existing backup devices?
Thanks!
SQL Backup Device
Mar 26, 2004Hi,
request you all to clear mine one doubt that,
Can creating a backup device for sql backup reduce the backup time comparing to giving full backup path.
Cheers!!
Deleting From A Backup Device
Sep 21, 2000I have a transaction log which is backed up 5 times a day to a device.
The Transaction log is growing quite quickly in between each backup. The backup device is also growing at a much quicker rate.
realistically i would like to only keep 2 weeks worth of transaction log backups in the device.
for the transaction log backup i have tried the following transact-Sql
'Backup Log [imsv23] to [imsv23_TL_2] with RetainDays = 7, Init, NoUnload, Name = N'imsv23 TL V2', NoSkip,Stats=10,Noformat'
but due to all of the contents in the backup set not having expired the backup fails.
i need to reduce the size of the backup device. all of the backup sets have expiry dates but none of them are being deleted from device.
so my problem is 1 i need to remove the excessive backups from the device
2 i need to set up the automatic backups so that these datasets are removed when the expiry date is reached.
thanks in advance
Can SQL 2000 Use A CD-RW As A Backup Device?
Jul 27, 2001If it can, is there some special way to set it up? I can only be successful setting up a tape drive.
Thanks,
Brian
Tape Backup Device Not Seen In EM
Jan 13, 2005Hi Folks,
I have a SQL Server 7.0 with and exteranl SCSI tape device.
When I attempt to backup a database in EM the tape device is being greyed out.
I have add the backup device using sp_dumpdevice and made sure that it is in sysdevices table. It will not allow me to add this backup device from the drop down.
1. Why does EM not see this device ?
2. If I try to back this up using T-SQL will I be able to use the registared tape device.
Thanks Folks
Sql Backup Device Using UNC Path
May 21, 2007I'm trying to set a backup job to back up my databases to a server other than the server where Sql server is installed. The only options I can see are the local drives on my sql server when I try to set a location or backup device. If I want my backups to go to another server , How do I do that. Currently I'm trying to use a UNC path like \ServerNameBackups. When I try to use a UNC path I get an invalid directory error.
I'm fairly new to SQL Server as you can tell!
Can anyone help me? (I'm using sql server 2000)
Thanks
GEM
Backup Device Locations Help
Mar 23, 2004I am a newbie in SQL Server Administration. I decided to make the move within my company that no one else wanted.
I have SQL Server 2000 running at this time; users are connecting and using it. My question I guess pertains to the backups.
I have two drives setup for data and transactions logs (D: E:)
My backups are running but backups are being sent to the d:systems datamssqlackup folder. once backups are comlete I normally copy the backups to a network share that is being backed up nightly to tape.
My question is this:
How do i create a device location under backup properties to point to a network share? i would like for the backups to go straight to a network drive so i do not have to copy the backups every morning when i come in.
Restore Last Backup From A Device
Mar 29, 2004I want to be able to take a backup device to another server and then restore the last backup file within the device.
My script creates the backup device using the filename of the file from the other server but when I do the restore it only restores the first file in the device.
How can I get it to restore the last file? In EA I can do a manual resotre and look inside the device and choose say backup number 2.
How can I do this in a script?
Thanks
Trying To Create A Backup Device
May 16, 2008Hi,
I was wondering if someone could help me. I'm having problems using backup in Enterprise Manager, the backup continually fails and points to a hardware problem. However, I have been able to backup files using different software to tape which rules out hardware.
I'm trying to create a new backup device incase the problem lies there but I receive the error 'error15061: Add device request denied. A physical device named '%s' already exists'
Any ideas?
Cheers
Backup Device For Tape Not Possible
May 17, 2007I want to create a backup device for the tape drive.
So I go in "Server Objects"=>"Backup Devices" and create a new device. Problem is: In "Destination" I can't select "Tape:".
The ntbackup.exe program recognizes the tape drive, but SQL2005 not.
I have this:
-SQL2005 Enterprise edition 64bit.
-Tape drive HP StorageWorks Ultrium 960.
Someone knows how I can create a backupdevice for the tape?
Thanks
IF Debugging = removing bugs from program THEN programming := putting bugs in program;
Append New Backup On A Device
Mar 16, 2008hi all!
appending new backup on a same device - yes or no?
if somebody can tell me, is ok to constantly create new backus on a same device? i'm asking that, because in that case, defice file it will grow and grow.
is there any limit or suggestions how many appending is recomended, cause of file growth?
tnx in adv!
Backups To A Backup Device
Oct 17, 2007I an trying to figure out a workable backup plan. I'd like to use a backup device, but I don't see how to keep a set number of days backups in it. Backing up to a backup device adds the backup in. How do I purge the expired backups so there is only, lets say, 3 days worth of backups in the backup device?
Tape Not Available As Backup Device
Nov 7, 2006I've got a new HP ML370 G4 Server 2003 SP1 with a fresh install of SQL 2005 SP1. I'm trying to create a backup device for the internal HP DAT 40 and tape is greyed out as an option. I have the newest HP software drivers and firmware. The tape drive is seen by Server 2003 device manager and by NTBACKUP. I can backup using that program. Why doesn't SQL 2005 see it? I tried a new HP Ultrium tape drive with the same results. Anyone else see this and can recommend a fix? TIA
View 11 Replies View RelatedTape Backup Device Not Available?
Aug 29, 2007Hello,
I am trying to configure the backup device on SQL Server 2005.
I cannot get the Tape device list.
The Tape Device is attached physicaly to the server ! (IBM Ultrium-TD3 scsi). I can 'see' the device using Windows Backup; but I cannot even get the list of tapes on SQL Server.
Can you please give me some help on this one ?!
Thank you all.
Youss
Cannot Open Backup Device
Dec 17, 2007DEAR ALL, I RUN THIS CODE FROM SQL QUERY ANALYSER FOR SQL EXPRESS
USE CPHONE
GO
BACKUP DATABASE CPHONE
TO DISK = 'C:CPHONE.Bak'
WITH FORMAT,
NAME = 'Full Backup of CPHONE'
GO
-- Optionally, create a logical backup device,
-- AdventureWorks_Backup, for this backup file:
USE master
EXEC sp_addumpdevice 'disk', 'CPHONE_Backup',
'C:CPHONE.Bak'
I RUN EITHER LOCALLY OR FROM ANALYSER OF OTHER PC
AND THEN I TOOK THIS ERROR
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:CPHONE.Bak'. Operating system error 5(error not found).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
ANY IDEAS PLS ??
Tape Not Available As Backup Device
Jan 19, 2007Hello,
TSQL command works fine,
C:SQLCMD
1> BACKUP DATABASE TestDB TO TAPE = '\.Tape0'
2> GO
but how to append more than one databases( SQL 2005) to the same tape.
Having said this, what is the solution for " I've got a new HP ML370 G4 Server 2003 SP1 with a fresh install of SQL 2005 SP1. I'm trying to create a backup device for the internal HP DAT 40 and tape is greyed out as an option .........." Pls advice me.
Restoring Log From Backup Device-Urgent
Jun 28, 2000Hi All,
I am trying to restore transaction log using T_SQL:
restore log transfer from transfer_log with file = 2 ,norecovery
and also I was Trying--
restore log transfer from disk = 'c:mssql7ackupransfer_log.dat' with file = 2,norecovery
I am getting this error Message-
Server: Msg 4306, Level 16, State 2, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Any Ideas,Please Let me Know ASAP
Thank You ALL--