| Author |
Topic |
|
brandonjelinek
Starting Member
6 Posts |
 Posted - 2007-07-28 : 20:51:43
|
| How do i make a new database file and then copy all the tables and data out of an old SQL file into it? I just want to see if rebuilding the file helps with a corruption issue i have been having with the log file.brandon jelinekwww.ccgspokane.com |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-28 : 22:20:03
|
| You can use dts/ssis to copy db objects. |
 |
|
|
brandonjelinek
Starting Member
6 Posts |
Posted - 2007-07-30 : 10:41:03
|
| if i do this.. will all the autonumber fields reset or will they keep the numbers they have? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-30 : 11:08:43
|
| There is an option in DTS to turn "Identity Insert ON" which will preserve the Identity values.I suggest:Script the databaseRun the script on a new, empty, database. Do NOT run any Foreign Key Create statements (yet)Transfer the data with DTS / BCP / SSISRun the remainder of the script to create the Foreign Keys"I just want to see if rebuilding the file helps with a corruption issue i have been having with the log file"If you are getting errors shown by DBCC CHECKDB then its hardware errors, or somesuch, that is causing the problem - not SQL Server misbehaving!(Or some prat deliberately inserting binary data into the middle of the file, or somesuch).In which case worth looking in the SQL Log to see when the errors started, and then in the Event log around that time to see if any hardware issues show up.Kristen |
|
|
|
brandonjelinek
Starting Member
6 Posts |
Posted - 2007-07-30 : 12:33:23
|
| Ya the problem with the log file is it is now over 80gigs in size. It is so large it could be any number of issues keeping me from being able to shrink it. I did do the system of trying to backup log with truncate only and then shrink, then list it, and do that several times but that actually creates more locked files instead of less and the log file grows.The bigger issue is that it is part of a web server and every time i even try to shrink the log file. It corrupts it. Since it takes about an hour and ahalf to back it up and an hour and a half to restore it. It is really annoying to work on since i can only do it at night.I am hoping if i get a clear cut way to rebuild the database.. i can premake the script and save it.. then go to the server one night and run it really fast.brandon |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-30 : 15:14:48
|
I'm surprised you can't shrink it.I'm also very surprised that Attempting to Shrink it is causing corruptions.This is what I use to shrink a Log file. You will have to know the logical name of the log file, and it would be best to set the database to DBO Only to prevent access from the Application whilst you do thisUSE MyDatabaseGOBACKUP LOG MyDatabase WITH TRUNCATE_ONLYGODBCC SHRINKFILE (MyDatabase_log, 1)GO You may also need to create additional transactions so that new transactions are started at the front of he LDF file, and checkpointed, such that the transactions at the end of the file can be released.There was an article about this by Andrew Zanevsky, but I don't have a link any more - you might be able to find it with GoogleKristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-30 : 15:18:23
|
| you say the logfile gets corrupted, what errors do you get?have you run DBCC CHECKDB on the database?-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-30 : 15:21:16
|
"have you run DBCC CHECKDB on the database?"... and if there is a corruption on ONE database [which has a probability of >99.999%  of being caused by a hardware failure] its unlikely to be the only one, so I suggest running DBCC CHECKDB on ALL databases - just-in-case.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-30 : 15:45:36
|
quote:
Originally posted by X002548
why don't you just dump and restore?
do you trust these backups?-ec |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Need to copy all tables and data to new database