Wednesday, December 6, 2006

SQL Server: Restoring from someone else's backup device

This morning I received a SQL Server backup file from someone else. I was to restore the backup file to a new database. Before restoring it, I decided to verify it to make sure everything was okay:

1> restore filelistonly
2> from disk='C:\datanewdb.bak'
3> go
Msg 3201, Level 16, State 2, Server SQLDB, Line 1
Cannot open backup device 'C:\datanewdb.bak'. Device error or device off-line. See the SQL Server error
log for more details.
Msg 3013, Level 16, State 1, Server SQLDB, Line 1
RESTORE FILELIST is terminating abnormally.

That was frustrating. But I noticed that the error said the device was off-line. That's a different error from what you get when the file is just corrupt.

I figured out that I had been sent the file from a backup device, not a straight backup file. I've thought for a long time now that the naming convention should be different for backup devices-- maybe .BKD instead of .BAK.

In any case, what I needed to do was to create a new backup device from the backup device file, then restore to a new database. Here are the steps I took which are suggested as a program of recovery (nudge nudge wink wink):

1. I created the backup device using the third-party's file:

1> exec sp_addumpdevice @devtype='disk',
2> @logicalname='newdb',
3> @physicalname='c:\datanewdb.bak'
4> go
(1 row affected)
'Disk' device added.

2. I verified the backup device:

1> restore verifyonly
2> from newdb
3> go
The backup set is valid.

3. I got the names of the files in the database backup set (I'll need these later when I restore):

1> restore filelistonly
2> from newdb

olddbD:\Microsoft Sql Server\MSSQL\Data\olddb.mdfDPRIMARY208882892815728640000
olddb_logD:\Microsoft Sql Server\MSSQL\Data\olddb_log.ldfLNULL14220656645242880000

4. I restored the database:

1> restore database newdb
2> from newdb
3> with move 'olddb' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newdb.mdf',
4> move 'olddb_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newdb_log.ldf'

Processed 167088 pages for database 'newdb', file 'olddb' on file 1.
Processed 1 pages for database 'newdb', file 'olddb_log' on file 1.
RESTORE DATABASE successfully processed 167089 pages in 339.970 seconds (4.026 MB/sec).

Note that when I restore the database, I have to specify some MOVE clauses to move the physical data and log files from where the third party stored them on their old system to where I want them on my system.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.