Move or backup? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Move or backup?

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-01-11 : 04:09:57

I am setting up a new server and we need to migrate the sql server 2000 databases to the new server with the most up to date information on them as possible i.e. almost live!

Do you suggest we do a move or a backup/restore procedure?

Your opinion would be very useful

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-11 : 04:15:55

Backup / restore.
Otherwise you might find you lose both systems.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-11 : 09:27:49

You should do something like the following:
1. Make sure the old database is in full recovery mode and you are running transaction log backups about every 15 minutes.
2. Setup all the logons on the new server that are used by the application on the old server.
2. Make a full backup of the old database, and restore it onto the new server WITH NORECOVERY.
3. Restore the transaction logs from the old database to the new database in order WITH NORECOVERY.
4. Set the old database to DBO ONLY, and get all of the users out of the database.
5. After you are sure there is no one in the old database and all work is complete, make one last transaction log backup and then set the old database to READ ONLY.
6. Restore the last transaction log backup to the new database WITH RECOVERY.
9. Setup a backup of the new database, and run it.
10. Schedule transaction log dumps on the new database and start them running.
11. Turn off the DBO ONLY on the new database.
12. Point the applications at the new database.

This should get all your data there with no data loss and minimal downtime.

quote:
Originally posted by jparker

I am setting up a new server and we need to migrate the sql server 2000 databases to the new server with the most up to date information on them as possible i.e. almost live!

Do you suggest we do a move or a backup/restore procedure?

Your opinion would be very useful

Codo Ergo Sum

Kristen
Test

22859 Posts

Posted - 2005-01-11 : 13:34:44

I think you need to do 10 before 9 don't you? (otherwise there is a gap between Full Backup and TLogs)

I did something similar recently, and the steps I noted were:

1) Preparation

1a) Old Server: Generate script to disable jobs


SELECT	CASE WHEN enabled = 1
		THEN 'EXEC msdb..sp_update_jobschedule @job_id = ''' 
			+ CONVERT(varchar(50), sjv.job_id) + ''', @enabled = 0'
		ELSE ''
		END,
	'--',
	dbname = SUBSTRING(sjst.database_name, 1, 20),
	enabled,
	[name],
	[description]
FROM	msdb.dbo.sysjobs_view sjv
	LEFT OUTER JOIN msdb.dbo.sysjobsteps sjst
		ON sjv.job_id = sjst.job_id
		AND sjst.step_id = 1
WHERE	dbname in ('MyDB1', 'MyDB2')
ORDER BY dbname, enabled, sjv.name

1b) Script setting the old databases to DBO/ReadOnly


SELECT	'PRINT ''' + name + '''',
	CHAR(13)+CHAR(10)
		+ 'ALTER DATABASE ' + name + ' SET SINGLE_USER,'
		+ ' READ_ONLY WITH ROLLBACK IMMEDIATE'
		+ CHAR(13)+CHAR(10)+'GO',
	CHAR(13)+CHAR(10)+'exec sp_dboption ''' + name + ''', ''dbo use only'', ''true''',
	CHAR(13)+CHAR(10)+'exec sp_dboption ''' + name + ''', ''read only'', ''true''',
	CHAR(13)+CHAR(10)+'---------------------------------------------'
FROM	master.dbo.sysdatabases D
WHERE	name in ('MyDB1', 'MyDB2')
ORDER BY name

1c) Restore an "old" copy of databases onto the New Server and test. Check restoration of Logins - script the process of reinstating the logins.

1d) Web server: Prepare a Holding Page for the web site

2) New Server: check that the Login being used for the transfer work does NOT use any database being restored as the default (oetherwise you won't be able to connect to the server whilst the database is partially restored)

3) Old Server: FULL (or DIFF if recent full backup available) backup of databas(es) to xfer

(You can also do a DIFF backup and Transaction Log backup/restore to go live but a FULL now and a DIFF to activate the New Server is easier)

4) Old Server: MOVE backup files to shared folder [on Old machine]

5) New Server: COPY backup files from OldMachine-Share to New machine

6) Old Server: MOVE backups back to original folder (so backup history complete)

7) New Server: Mark scheduled jobs as DISABLED. Keep a note of which ones were changed to reinstate later

8) New Server: Restore FULL Backup file (+ DIFFs if any) WITHOUT using RECOVERY

8a) USE MASTER (don't sit in actual DB whilst restoring!)

8b) ALTER DATABASE MyDB1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

8c) Script the RESTORE - this will make it easier to adapt for the restore of the final TLOG later on

9) Web Server: Open notepad to edit connection-string INCLUDE file; Make the change to the use the new server. Do NOT save yet

10) Web Server: Activate the Holding Page on the web site(s)

10a) Open a browser on the site, login / put items in shopping cart / etc.
10b) Mount holding page
10c) Click on link in browser and check holding page displays

11) Old Server: Disable all scheduled tasks (using script from step (1))

12) Old Server: Set databases to DBO/ReadOnly (using script from step (1))

13) Old Server: Make final backup (DIFF if a FULL was done before, otherwise TLog)

14) Old Server: MOVE Final backup to Shared Folder

15) New Server: Copy from oldServer share to New Server

16) New Server: Restore final backup file - using RECOVERY

17) New Server: Set databases to "available"

17a) ALTER DATABASE MyDB1 SET MULTI_USER, READ_WRITE WITH ROLLBACK IMMEDIATE
17b) Reinstate users / roles etc.

18) Web Server: Save the Connection String (using open Notepad session from Step 9)

19) Browser - If possible open a new browser and test the site (i.e. outside the scope of the holding page)

20) Web Server: Remove the holding page

Site is now live again

21) Check if the browser (from Step 10c ) can continue using the site without loss of session etc.

~~22) New Server: Make transaction log backup~~

23) New Server: Make FULL backup (the initiation of Backups + TLogs)

24) New Server: Reenable jobs

25) Old Server: MOVE Final backup from Shared Folder back to Backup folder (to maintain complete backup history)

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-11 : 17:17:41

No, you want to run the full backup firxt, because any Tlog backup made before the start of the Full backup is of no use since you would have no full backup to start from to roll Tlogs forward. The first Tlog backup made after the start of the full backup will contain everything needed to roll forward from that point.

quote:
Originally posted by Kristen

I think you need to do 10 before 9 don't you? (otherwise there is a gap between Full Backup and TLogs)

Kristen

Codo Ergo Sum

Kristen
Test

22859 Posts

Posted - 2005-01-12 : 10:15:49

I'm not convinced!

I do a FULL BACKUP

Something does a transaction or two

I do a TRANSACTION BACKUP

Can I now restore the Full followed by the TLog? I don't think so 'coz there's a gap in the LSNs - but I aint tried it, so I don't know for sure.

Actually I'm sure I can on a brand new database, I was thinking of a RESTORE over an existing database:

FULL BACKUP(1)
TLogsBackup(1)
TLogsBackup(2)
...
TLogsBackup(98)
RESTORE full backup from some other server
FULL BACKUP(2)
... a few transactions here ...
TLogsBackup(99)

can I now restore FULL(2) + TLogsBackup(99)? (I don't think so because there is a gap in the LSNs)

What about restore FULL(2) + TLogsBackup(98) [is the tail of that useable in this example?] + TLogsBackup(99)?

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-12 : 16:21:45

When the FULL BACKUP starts that fact is recorded in the transaction log. The next transaction log backup that runs will have everything in the transaction log since the prior transcation log backup, including the start of the full backup. That is how the transaction log knows the point to start rolling forward from.

I have done this many times, but if you don't believe me, you can run your own test to confirm it:
1. Restore a database with NO RECOVERY from a full backup.
2. Restore the LAST transaction log made BEFORE the start of the full backup with NO RECOVERY. It will give you an error that says there is nothing it can restore.
3. Restore the SECOND transaction log made AFTER the start of the full backup with NO RECOVERY. It will also give an error, because you haven't rolled the database forward with the first transaction log backup yet.
4. Restore the FIRST transaction log made AFTER the start of the full backup with NO RECOVERY. It wiil roll forward with no error.
4. Restore the SECOND transaction log made AFTER the start of the full backup with NO RECOVERY. It wiil roll forward with no error.

quote:
Originally posted by Kristen

I'm not convinced!

I do a FULL BACKUP

Something does a transaction or two

I do a TRANSACTION BACKUP

Can I now restore the Full followed by the TLog? I don't think so 'coz there's a gap in the LSNs - but I aint tried it, so I don't know for sure.

Actually I'm sure I can on a brand new database, I was thinking of a RESTORE over an existing database:

FULL BACKUP(1)
TLogsBackup(1)
TLogsBackup(2)
...
TLogsBackup(98)
RESTORE full backup from some other server
FULL BACKUP(2)
... a few transactions here ...
TLogsBackup(99)

can I now restore FULL(2) + TLogsBackup(99)? (I don't think so because there is a gap in the LSNs)

What about restore FULL(2) + TLogsBackup(98) [is the tail of that useable in this example?] + TLogsBackup(99)?

Kristen

Codo Ergo Sum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-12 : 16:28:08

quote:
Originally posted by Kristen

I'm not convinced!

I do a FULL BACKUP

Something does a transaction or two

I do a TRANSACTION BACKUP

Can I now restore the Full followed by the TLog? I don't think so 'coz there's a gap in the LSNs - but I aint tried it, so I don't know for sure.

Yes of course you can restore the full and the following tlog. Why don't you think you can?

quote:
Originally posted by Kristen

Actually I'm sure I can on a brand new database, I was thinking of a RESTORE over an existing database:

It being a brand new database or a restore of an existing one doesn't make a difference.

quote:
Originally posted by Kristen

FULL BACKUP(1)
TLogsBackup(1)
TLogsBackup(2)
...
TLogsBackup(98)
RESTORE full backup from some other server
FULL BACKUP(2)
... a few transactions here ...
TLogsBackup(99)

can I now restore FULL(2) + TLogsBackup(99)? (I don't think so because there is a gap in the LSNs)

What about restore FULL(2) + TLogsBackup(98) [is the tail of that useable in this example?] + TLogsBackup(99)?

Yes you can restore FULL(2) + TLogsBackup(99). There isn't a gap in the LSNs. I'm confused as to why you think there's a gap.

You can not do FULL(2) + TLogsBackup(98). 98 occurred before FULL(2), so you are out of sequence.

Tara

Kristen
Test

22859 Posts

Posted - 2005-01-13 : 00:37:03

Michael Valentine Jones: Makes sense now I've stopped over-thinking it!

Tara:

"Yes of course you can restore the full and the following tlog. Why don't you think you can?"

Mental aberation. I was thinking that Transaction Logging wouldn't start until the first TLog backup took place - if that was the case we wonuldn't get any questions here saying "My 5 MB database has a 20GB LDF file, why?"

"Yes you can restore FULL(2) + TLogsBackup(99). There isn't a gap in the LSNs. I'm confused as to why you think there's a gap."

I was thinking that the RESTORE mucked up the transaction log sequencing - e.g. reset it to whatever the LSN was in the database from which the backup was made; and thus another TLog backup was needed to get things back in sync.

So ignore all of my wibble, I've got the hang of it now.

Presumably I cannot restore FULL(1) and then TLog(1) through TLog(98) and THEN restore TLog(99) because of the RESTORE between (98) and (99).

I ought to make a TEST really <gg>

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-13 : 12:35:03

quote:
Originally posted by Kristen

Presumably I cannot restore FULL(1) and then TLog(1) through TLog(98) and THEN restore TLog(99) because of the RESTORE between (98) and (99).

Yes you can do that. The full backups do not affect the LSNs of the tlogs. You just need to start with one full backup and then all of the tlogs up to the point where you want to recover. As long as you have the tlog chain, then it doesn't matter if full backups occurred in between, you just need one to start with.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources