Migration of Databases to New SQL Server - HELP

Question

Post reply

Migration of Databases to New SQL Server - HELP

Mark Vermeulen

SSC Veteran

Points: 260
More actions
March 3, 2006 at 2:06 pm

#111866
I have been trying to put together the process of moving all of my databases from an old SQL 2000 server to a newer server. Based on some of the threads that I have read, I still have several questions but here is my general idea, please help me fill in the gaps if you can. I wish to truncate all of the logs files prior to the move, shrink the actual log files and then run another full backup of all databases. (Approximately 150 databases in a non production environment).
1. Run Backup of all databases (Just in case)
2. Backup Logs with truncate only
3. Perform DBCC SHRINKFILE on all databases
4. Run another backup of all databases
5. Script Security and Configuration Info (Not sure how to do this?)
6. Copy backup files to new server
7. Shutdown Old Server
8. Rename new server and set IP address to match old server
9. Restore databases to new server with same file structure
10. Apply security and configuration to new SQL server.
Does this sound like a resonable plan? I can work through the process of scripting the backup and restore process but definitely need some help on how to perform or approach the security. Please poke holes in this idea as I have not had to do anything to this scale.
Thanks very much,
Mark

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Rob v Dijk SSC Enthusiast Points: 163 More actions · Answer 1

Have you looked at the RedGate tools??

http://www.red-gate.com/

I use them all the time and make things a lot easier.

Good luck.

Cheers,

Rob

Mark Vermeulen SSC Veteran Points: 260 More actions · Answer 2

Red Gate has some terrific tools but in this case, I do not think this applies.

lholman SSCrazy Points: 2810 More actions · Answer 3

Mark,

This MS article explains a lot of what you are asking, in particular your security questions are discussed in Step #2.

ll

oh yes, sorry about that, got distracted adding it to del.icio.us: http://support.microsoft.com/?id=314546

Mark Vermeulen SSC Veteran Points: 260 More actions · Answer 4

I am assuming you intended to include a link. Please update and thanks...

Richard L. Dawson Ten Centuries Points: 1062 More actions · Answer 5

Hi Folks,

I have a list of articles that I send to customers that are moving databases or going through a disaster recovery. It covers a lot of ground, but seems to fit here. These articles are updated regularly and new ones written to replace outdated info. Hope this helps.

To restore data that has been inadvertently deleted.

SQL Server Books Online Topics:

"RESTORE"

"How to restore files to a new location (Transact-SQL)"

"How to restore to a point in time"

"Designing a Backup and Restore Strategy"

"Backing Up and Restoring Databases"

"Backup/Restore Architecture"

Disaster Recovery Articles:

822400 Disaster recovery options for Microsoft SQL Server

http://support.microsoft.com/?id=822400

224071 Moving SQL Server databases to a new location with Detach/Attach

http://support.microsoft.com/?id=224071

314546 HOW TO: Move Databases Between Computers That Are Running SQL Server

http://support.microsoft.com/?id=314546

240867 How to move, copy, and back up full-text catalog folders and files

http://support.microsoft.com/?id=240867

253817 HOW TO: Backup the Last Transaction Log When the Master and the Database

http://support.microsoft.com/?id=253817

246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server

http://support.microsoft.com/?id=246133

286280 INF: Effect of Database File Additions or Deletions on Database Recovery

http://support.microsoft.com/?id=286280

240872 HOW TO: Resolve Permission Issues When You Move a Database Between

http://support.microsoft.com/?id=240872

168001 PRB: User Logon and/or Permission Errors After Restoring Dump

http://support.microsoft.com/?id=168001

240867 How to move, copy, and back up full-text catalog folders and files

http://support.microsoft.com/?id=240867

842425 How to move a Reporting Services database from a computer that is

http://support.microsoft.com/?id=842425

303774 BUG: "Renaming A Server" Topic in SQL Server Books Online is Incomplete

http://support.microsoft.com/?id=303774

SQL Server 2000 – Technical Reference

http://www.microsoft.com/technet/prodtechnol/sql/2000/techref.mspx

SQL Server 2000 Operations Guide

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx

Sql Server Best Practices Analyzer

http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

If you have questions for me about any of this please reply to me. Unfortunately, I don't read the posts every day.

Richard L. Dawson
Microsoft Sql Server DBA/Data Architect

I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

Mark Vermeulen SSC Veteran Points: 260 More actions · Answer 6

I will say thank you now for posting the information because it may take a little time to go through it all! I will make an effort to post a summary of what we end up doing (provided I don't have more questions!! )

Thanks again....