April 7, 2009 at 2:49 pm
I have a relatively small database to maintain, and i'm very new to this programming enviroment (former mainframer). So on MS SQL Server, i have a database i need to perform some maintenance on in both test and production. One of my tables in the database is so large my users are experiencing timeout issues, so we decided to archive off some records of previous years, however, on that same table we have discovered some duplicate records. So in this case, I'm wondering what the difference is between backing up a database or copying a database is, and also what would the best practice be for this scenario. To Copy the database and perform the maintenance on the copy and then implementing the refreshed version of the database, or backing up the database and performing the maintenance on the database?
April 7, 2009 at 4:29 pm
MSSQL is no different to mainframe databases, its always good to test your code first and ensure you get the results you want, so I would backup the database, copy this backup to a test server, restore it there and do your testing.
Once you are happy, backup the production database just before the update as a recovery point.
Of course you have regular backups of your database in place already, yes?
---------------------------------------------------------------------
April 7, 2009 at 4:40 pm
Actually, this will be my first time performing a database backup for this database, and I don't know what the backup plan is for this database but I hope to establish one with this fix I'm working on. Luckily, my test database has the same errors as our production database, so anything i perform in test I should be able to duplicate in production.
So I guess my plan would be:
Perform Database Backup.
Perform Maintenance Tasks.
thanks for your input 🙂
April 7, 2009 at 4:49 pm
that would do it, as long as you have a backout strategy.
If you are archiving a LOT of data or there a lot of duplicates to delete you could hit problems with transaction log growth, so keep an eye on that when you test.
---------------------------------------------------------------------
April 7, 2009 at 4:53 pm
Good point, i know the size of the transaction log has caused space issues on other servers, Thank you!
April 7, 2009 at 5:16 pm
The typical "copy" process on SQL Server is a backup and restore, so I'd recommend that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply