January 24, 2003 at 12:51 pm
Hi all,
We need to confine the backup files to less than 2Gig. Does MS maintenance plan support backing up into multple files?
Thanks in advance,
-Yuan
January 24, 2003 at 1:24 pm
To my knowledge, you can't do what you are trying. I believe the backups are all or nothing.
-SQLBill
January 24, 2003 at 1:32 pm
You could write your own script to backup database to multiple files which can be controled to be less than 2GB each. You may end up with many backup files if you have large database.
Why do you want to do that?
January 24, 2003 at 1:37 pm
Well,
Hi there,
We need to transfer the backup files to another server. From time to time we have trouble with files of larger than 2Gig in size. Also Winzip can't accept file larger than 4Gig.
Thanks for the info,
-Yuan
January 24, 2003 at 1:58 pm
Take a look at :
You May Not Be Able to Copy Large Files on Computers That Are Running Windows NT 4.0 or Windows 2000http://support.microsoft.com/default.aspx?scid=KB;EN-US;q259837&
Also for zip, I have written a VB dll to wrap the info zip libraries from :
http://www.info-zip.org/pub/infozip/ They have one you can download from them, source included. I was not fond of the design of it, so I wrote a different version.
If the service pack does not work, you can probably write some code to break the file up yourself to copy over. Take a look at CopyFileEx.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 24, 2003 at 2:10 pm
Zip is still limited to 4GB for any version I have seen for an uncompressed file. However the SQL thrid party backups SQLZip and SQL Litespeed both support compression without a size limit I have seen. Both are really good items.
Now as for maintainence you cannot set multiple names. But I started in the days when Maintainence would blow up more than work so I scripted all my backups myself.
This is how would do yours at a basic point (no expiring or anything specific).
DECLARE @dbname VARCHAR(255)
SET @dbname = 'mydbname'
EXEC ('BACKUP DATABASE [' + @dbname + ']
TO DISK = ''D:\Backups\' + @dbname + '_FULL_' + REPLACE(CONVERT(char,GETDATE(),101),'/','')+'.bak''
WITH
INIT,
NAME = ''' + @dbname + ' Full Backup''')
This will make an easy daily backup with date based name.
January 24, 2003 at 2:13 pm
Thanks very much. I will try the script.
-Yuan
January 28, 2003 at 10:22 am
I found another alternative from the Windows 2000 Resource Kit. Read below excerpt from the RK chm. This has some interesting possibilites.
quote:
Fcopy.exe: File Copy Utility for Microsoft Message Queuing--------------------------------------------------------------------------------
FCopy is a multi-server file copy tool that compresses files and folders to facilitate copying across LANs and WANs. Because it uses Message Queuing, this tool continues to copy files even if there are network problems. It includes update options which copy files only if they have been changed.
FCopy divides files into 32 kilobyte segments, compresses each segment and sends it as a Message Queuing message (in express mode). It does not need to restart when a network breakdown occurs since Message Queuing guarantees that the messages will be delivered.
FCopy's file compression uses a fast algorithm. It works well on any computer, but is most useful for slow lines; on regular Ethernet, it doesn't affect copy speed very much unless the computer has a fast CPU. FCopy has been tested on files of more than five gigabytes, but can theoretically copy much larger files.
FCopy works with Message Queuing for Windows 2000, which is part of Microsoft® Windows® 2000, and MSMQ 1.0 for Microsoft® Windows NT® version 4.0, which is found on the Windows NT version 4.0 Option Pack CD-ROM.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
February 20, 2003 at 8:55 am
Could someone tell me why I get errors when I run the following script from the command line and I get 'Syntax error or access violation':
backup_custdevhc2.bat looks like this:
osql -S server_name -U username -P password -h-1 -i {some path}\backup_custdevhc2.sql -o {some path}\backup_custdevhc2.log -n
backup_custdevhc2.sql looks like this:
BACKUP DATABASE [custdevhc2] TO DISK = N'{some path}\custdevhc2_db_200303200852_1_01.BAK', DISK = N'{some path}\custdevhc2_db_200303200852_02.BAK'
WITH NOINIT , NOUNLOAD , NAME = N'custdevhc2 backup', NOSKIP , STATS = 10, NOFORMAT
The user/password is valid. Also it runs sucessfully when I copy the backup_custdevhc2.sql in the SQL ANALYZER.
How do I run it from the command line. My goal is to schedule it run as a task.
Thanks for help,
-Yuan
February 20, 2003 at 8:55 am
Could someone tell me why I get errors when I run the following script from the command line and I get 'Syntax error or access violation':
backup_custdevhc2.bat looks like this:
osql -S server_name -U username -P password -h-1 -i {some path}\backup_custdevhc2.sql -o {some path}\backup_custdevhc2.log -n
backup_custdevhc2.sql looks like this:
BACKUP DATABASE [custdevhc2] TO DISK = N'{some path}\custdevhc2_db_200303200852_1_01.BAK', DISK = N'{some path}\custdevhc2_db_200303200852_02.BAK'
WITH NOINIT , NOUNLOAD , NAME = N'custdevhc2 backup', NOSKIP , STATS = 10, NOFORMAT
The user/password is valid. Also it runs sucessfully when I copy the backup_custdevhc2.sql in the SQL ANALYZER.
How do I run it from the command line. My goal is to schedule it run as a task.
Thanks for help,
-Yuan
February 21, 2003 at 9:40 am
never mind.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply