Restoring Data without messing up other SQL server settings

[NuJoizey]

Hello SQL Server 2005 Pros:

I am migrating an SQL Server 2005 database to some new servers, and I have gone through a testing process where I have performed a full backup from the old production server and restored it to the new server. I went thru the excercise of moving all jobs, alerts, operators, linked servers, saved ssis (dts) packages, users, re-set up SQL Server Mail on the new server, and the like.

Essentially I successfully carried out the process outlined at

http://support.microsoft.com/kb/314546/en-us.

Because of my relative inexperience in SQL server migration, the whole process took me a couple of days. During that time, naturally, the DB on the old production server has been updated. Now I have observed that if I just do another backup and restore it over top of the existing database in my new server, if I understand correctly, I would have to re-set up all my
jobs, alerts, operators, linked servers, ssis (dts) packages and the like.

I'm imagining a better scenario where I could somehow grab just the transactions that have occurred since a couple of days ago, and somehow add them to the database on new server which is "frozen in time" from just a few days ago.

I've been reading about things like differential backups, the transaction log, snapshots, replication, or potentially using integration services to move all table structures and data from instance to the other, but I still don't know whether what I'm trying to accomplish is even possible, and if so - is it worth the trouble? - i.e. is this a standard administrative tactic?

If not, what is the preferred method?

Or should I just tell my users not to use the db while after 5pm I perform another full backup, overwrite the old database, and reconfigure everything else manually as I have just done after business hours and have it ready for the next morning? (I think it wouldn't take me too long on the second go around)

I think just need a little insight and practical advice from you all, and then hopefully I can carry on.

 

[mrdenny]

You can backup and restore your database without having to change any of the jobs, alerts, operators, linked servers, SSIS packages, logins, SQL Mail, etc. All that stuff is stored within the master and msdb databases. As long as you don't restore those databases you've got no problems.

You will need to realign the logins to the users using the sp_change_users_login procedure.

When you are going to do the cut over you will want to stop all your users from using the application otherwise data will be lost and they will have to re-enter the data after to bring the new server into production.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[NuJoizey]

thanks mr denny,

odd thing I noticed that when I did the initial test backup and restore, I lost all of those things and had to recreate using scripts - and also the ms support article I referenced above seems to indicate in steps 4 and 5 and under "more information" that these need to be recreated by design - what am i misinterpreting?

 

[mrdenny]

I think that you are misinterpreting the KB article.

You'll notice that step 3 is the only one which has a warning like this:

Warning If you restore or attach the database again, the database users may be re-orphaned and you have to repeat step 3.

As long as you don't restore your master or msdb databases you'll be fine.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[NuJoizey]

hmm, ok, but i didn't seem to experience any trouble with orphaned users, and so i didn't have to go thru all that stuff.

The only things I seemed to lose were jobs, alerts, operators, linked servers, and ssis (dts) packages.

a point of clarification, what I am calling "backup and restore" is by right clicking the db in ssms, choosing "tasks", and than "backup". I backed it up to disk, and then copied the resulting .bak file from the source server to the target server.

In the target server's ssms, i right clicked "databases" in and chose "retore database", pointing to the .bak file i had just copied.

that IS a usual way to do these things, right?

 

[mrdenny]

That is one of the methods, and will work just fine.

The other option would be to use the BACKUP DATABASE and RESTORE DATABASE commands manually.

When you use the SSMS to backup or restore the database it simply uses the BACKUP DATABASE and RESTORE Database commands.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[NuJoizey]

ok- thanks for the help! much appreciated.

 

[NuJoizey]

what is the difference between doing it the way talked about above and using what i believe is called the copy database wizard, i.e. in ssms you right-click the database > Tasks > Copy Database...

 

[mrdenny]

The copy database wizard creates the tables one at a time, then copies in all the data and creates the needed indexes. On databases with very large tables the Copy Database Wizard can take a very long time, and it often throws errors.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog