May 26, 2009 at 11:53 am
Instead of an identity column, you may need a "sequence generator" instead to provide a unique ID value for inserts. In SQL Server this could be a single row/column table with an identity column. You may want to do a search of SSC for more info. I seem to recall Jeff Moden posting something somewhere regarding this but I can't remember what/where.
May 26, 2009 at 2:18 pm
The solution that I was thinking of before I posted this was to first insert the rows into a copy of the table with the identity column turned on, then do my insert into the production table from the copy.
I was trying to see if there was a way to not do it that way since it adds a layer of complexity to all future projects that have similar specs.
May 26, 2009 at 3:02 pm
If your problem is getting backups to fit on DVDs, you can backup to multiple backup files. SQL Server will split the data across multiple backup files that will be about equal in size. For example, a 60 GB database could be backed up to 2 files about 30 GB in size. I'm not sure if there is a limit, but I have done a test backup to 50 files.
If you are using a backup compression utility, like LiteSpeed, you can do the same there.
It's a bit more work to backup to multiple files, but I think it's a lot less than what you are doing to partition the data.
Also, have you considered going to a tape backup solution to get higher media capacity for backups?
Sample command to backup to multiple files:
backup database[MyDatabase]
to
disk = 'X:\MSSQL\Backup\MyDatabase_db_200905261656_001.BAK' ,
disk = 'X:\MSSQL\Backup\MyDatabase_db_200905261656_002.BAK' ,
disk = 'X:\MSSQL\Backup\MyDatabase_db_200905261656_003.BAK' ,
disk = 'X:\MSSQL\Backup\MyDatabase_db_200905261656_004.BAK' ,
disk = 'X:\MSSQL\Backup\MyDatabase_db_200905261656_005.BAK'
with
init,
stats = 5
May 28, 2009 at 7:50 am
Hmm. I will play around with the backup spanning option. This may help me out a bit. I would have to backup to multiple files and then restore it on another server to feel confident about it first obviously.
May 28, 2009 at 4:13 pm
Will Summers (5/28/2009)
Hmm. I will play around with the backup spanning option. This may help me out a bit. I would have to backup to multiple files and then restore it on another server to feel confident about it first obviously.
I have tested the restore from multiple backups enough that I am confident in it.
Of course you should do it yourself to make sure you're confident in it, and to make sure there is no question in your mind about how to do the restore. Doing the first time under fire is not the kind of learning experience you want.
Don't know if it's important to you, but you can also do backups to multiple files with differential and transaction log backups.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply