June 28, 2011 at 9:59 am
7Zip works fine but RedGate is the best
Thank you.
Aatish Patel
June 28, 2011 at 6:28 pm
aatishpatel (6/28/2011)
7Zip works fine but RedGate is the best
Maybe, but with limited resources (money) you do what you have to with what you can afford.
With SQL Server 2005 I used HyperBac, before it became a RedGate product. I was quite happen with it.
June 28, 2011 at 10:13 pm
Hi Steve,
i said i don't find any much use in giving the file name's manually. it has to be automatically taken with some increment type for the successive file names.
is there any way indirectly in SQL 2005 to achieve this?
thanks in advance.
Regards
Durai Nagarajan
June 29, 2011 at 12:27 am
I would suggest you take backup in more than one stripe. In this way you will not have one large backup file but many (as you specify) and would be easy for you to port them in more than one DVD.
June 29, 2011 at 2:27 am
durai nagarajan (6/28/2011)
Hi Steve,i said i don't find any much use in giving the file name's manually. it has to be automatically taken with some increment type for the successive file names.
is there any way indirectly in SQL 2005 to achieve this?
No, there is not (how many times has that been stated?)
You can stripe a backup over multiple files, but you need to specify all the file names in the backup command.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2011 at 4:13 am
I have some suggestions for you:
File backups
Have you thought about file backups in your database?
You will have to do a bit of work but as it appears that it is quite small, this shouldn't take you too long and this solution is quite scaleable.
As your database grows you will have to add additional files to the filegroup and you will have to maintain your database with rebuilds.
The idea is to add multiple files of 4GB to your database's filegroup. You can decide how many yourself. You know your database size. You can make them smaller if you wish but limit their growth to 4GB.
And if you do get the BluRay burner you can adjust the max sizes to suit BluRay.
Here is a basic CREATE DATABASE statement that makes a database with four 4GB files.
CREATE DATABASE FourG ON PRIMARY
( NAME = N'FourG', FILENAME = N'D:\DBDATA\FourG.mdf' , SIZE = 4GB , MAXSIZE = 4GB)
,( NAME = N'FourG1', FILENAME = N'D:\DBDATA\FourG1.mdf' , SIZE = 4GB , MAXSIZE = 4GB)
,( NAME = N'FourG2', FILENAME = N'D:\DBDATA\FourG2.mdf' , SIZE = 4GB , MAXSIZE = 4GB)
,( NAME = N'FourG3', FILENAME = N'D:\DBDATA\FourG3.mdf' , SIZE = 4GB , MAXSIZE = 4GB)
LOG ON
( NAME = N'ITC_sys_log', FILENAME = N'D:\DBLOG\FourG_log.LDF' , SIZE = 1GB , MAXSIZE = 4GB)
GO
You will have to do some index rebuilding, file shrinking and other messing around to get the other files utilised but essentially you are trying to get your data into the other files and get your first primary reduced to 4GB.
Once all this is done you can now take file level backups:
BACKUP DATABASE FourG File = 'FourG' TO DISK = 'D:\DBBackup\FourG.bak'
BACKUP DATABASE FourG File = 'FourG1' TO DISK = 'D:\DBBackup\FourG1.bak'
BACKUP DATABASE FourG File = 'FourG2' TO DISK = 'D:\DBBackup\FourG2.bak'
BACKUP DATABASE FourG File = 'FourG3' TO DISK = 'D:\DBBackup\FourG3.bak'
There are some reasons why this could not work and your database will need to use the FULL recovery model. Please test it well before implementing it and don't forget to perform transaction log backups too.
Disable indices
If you can get away with your database not having any indices for a bit you can disable all the non-clustered indices before you perform your backup.
This will reduce the amount of space needed and might just get it down to your 4GB limit.
ALTER INDEX NonClusteredIndexName ON TableName DISABLE
You can do this for all indices on tables that are not clustered and the tables are not ms-shipped.
Here is a sample bit of code to generate the ALTER INDEX statements:
SELECT 'ALTER INDEX ' + i.name + ' ON ' + t.name + ' DISABLE'
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
WHERE i.index_id > 1
AND t.is_ms_shipped = 0
Of course, you will have to rebuild those indices again:
SELECT 'ALTER INDEX ' + name + ' ON ' + OBJECT_NAME(object_id) + ' REBUILD'
FROM sys.indexes
WHERE is_disabled = 1
Just give him the data he needs
There are many ETL tools freely available to you which you can use to simply extract the data your boss needs and you can easily have it import into his machine.
From simple, old fashioned and reliable BCP (you can control start and end row numbers to help you with the seperation) to SSIS (you can still use DTS if you really really have to) to blast it over the network.
Use the LAN
Why bother with a DVD anyway? You could backup the database directly to his hard drive over the office LAN. Then you can ignore every one of my other suggestions.
June 29, 2011 at 1:17 pm
June 29, 2011 at 2:41 pm
You run this query in your database to calculate the number of striped backup files to create so that all can fit within the 4 GB limit. Then use that number to dynamically create a backup command with the number of files you need.
select
Backup_Files_Needed = convert(int,ceiling(c.GB_Used/4.00)),
c.*
from
(
select
GB_Used=
sum(fileproperty(aa.name,'SpaceUsed')/131072.00)
from
dbo.sysfiles aa
left join
dbo.sysfilegroups bb
on aa.groupid = bb.groupid
) c
Results:
Backup_Files_Needed GB_Used
------------------- ----------------------------------------
10 39.720115661
July 11, 2011 at 3:18 am
removed original text, couldn't delete post.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply