Monday, June 6, 2011

Installing A SQL Server Database From A BAK File

Question: I've just been given a SQL Server 2005 *.bak file to use as a consolidated database in a MobiLink synchronization setup... what do I do with this file?

Answer: That file was most likely created by the SQL Server 2005 BACKUP statement and it's easy to restore into a brand-new database.

But first, you need to install SQL Server. This blog is about SQL Anywhere, not SQL Server, so this article makes no assumptions about where you're starting from... like the man said on TV "nobody needs a clue":

If you want, you can start with SQL Server 2008 instead of 2005 and still use the *.bak file you've been given; you can download Microsoft SQL Server 2008 Express Edition Service Pack 1 here.

Step 2: Start the SQL Server interactive SQL utility osql.exe on the master database (which always exists on SQL Server); here's a command line for Windows:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe" -S Envy -U sa -P j68Fje9#fyu489 -d master



Step 3: Use the CREATE DATABASE statement in osql to prepare a database to receive the data in the *.bak file; the DROP DATABASE statement is included in case you have to start over again later:

USE master
GO

DROP DATABASE mydb
GO

CREATE DATABASE mydb
ON
( NAME = mydb,
FILENAME = 'C:\temp\mydb.mdf',
SIZE = 300MB )
LOG ON
( NAME = mydb_log,
FILENAME = 'C:\temp\mydb.ldf',
SIZE = 100MB )
GO

Step 4: Find out what you have in the *.bak file with the RESTORE FILELISTONLY statement:

RESTORE FILELISTONLY
FROM DISK = 'C:\temp\mydb.bak'
GO

Here are the results; only the two "LogicalName" values are important: myXXX for the database file and myXXX_log for the transaction log:

LogicalName
PhysicalName
Type
FileGroupName
Size MaxSize FileId
CreateLSN DropLSN
UniqueId ReadOnlyLSN
ReadWriteLSN BackupSizeInBytes SourceBlockSize
FileGroupId LogGroupGUID
DifferentialBaseLSN DifferentialBaseGUID
IsReadOnly IsPresent TDEThumbprint
myXXX
D:\SQLServer\Data\myXXX.mdf
D
PRIMARY
209715200 35184372080640 1
0 0
9DF4176F-4C1F-4198-A32C-3E5E91F8EED8 0
0 146210816 512
1 NULL
3619000000786000140 062CF42D-5C45-47F4-8ADB-84EF794C2F82
0 1 NULL
myXXX_log
E:\SQLServer\Logfiles\myXXX_log.ldf
L
NULL
214433792 2199023255552 2
0 0
47EE2F14-8CB0-49FA-B525-CD79D38FAE91 0
0 0 512
0 NULL
0 00000000-0000-0000-0000-000000000000
0 1 NULL
(2 rows affected)

Step 5: Use the RESTORE DATABASE statement to copy the data from the *.bak file into your database. The WITH MOVE options tell SQL Server to put the data into files that are in different locations from the original, and the WITH REPLACE option tells SQL Server accept the fact you're changing the database name to mydb. The only place you have to use the old names is in the two MOVE clauses: MOVE 'myXXX' and MOVE 'myXXX_log' refer to the LogicalName values you found in Step 4.

RESTORE DATABASE mydb
FROM DISK = 'C:\temp\mydb.bak'
WITH MOVE 'myXXX' TO 'C:\temp\mydb.mdf',
MOVE 'myXXX_log' TO 'C:\temp\mydb.ldf',
REPLACE
GO

The RESTORE DATABASE statement is remarkably robust when it comes to upgrading data on the fly; between SQL Server 2005 and 2008 there were apparently quite a few version changes:

Processed 17848 pages for database 'mydb', file 'myXXX' on file 1.
Processed 2 pages for database 'mydb', file 'myXXX_log' on file 1.
Converting database 'mydb' from version 611 to the current version 655.
Database 'mydb' running the upgrade step from version 611 to version 621.
Database 'mydb' running the upgrade step from version 621 to version 622.
Database 'mydb' running the upgrade step from version 622 to version 625.
Database 'mydb' running the upgrade step from version 625 to version 626.
Database 'mydb' running the upgrade step from version 626 to version 627.
Database 'mydb' running the upgrade step from version 627 to version 628.
Database 'mydb' running the upgrade step from version 628 to version 629.
Database 'mydb' running the upgrade step from version 629 to version 630.
Database 'mydb' running the upgrade step from version 630 to version 631.
Database 'mydb' running the upgrade step from version 631 to version 632.
Database 'mydb' running the upgrade step from version 632 to version 633.
Database 'mydb' running the upgrade step from version 633 to version 634.
Database 'mydb' running the upgrade step from version 634 to version 635.
Database 'mydb' running the upgrade step from version 635 to version 636.
Database 'mydb' running the upgrade step from version 636 to version 637.
Database 'mydb' running the upgrade step from version 637 to version 638.
Database 'mydb' running the upgrade step from version 638 to version 639.
Database 'mydb' running the upgrade step from version 639 to version 640.
Database 'mydb' running the upgrade step from version 640 to version 641.
Database 'mydb' running the upgrade step from version 641 to version 642.
Database 'mydb' running the upgrade step from version 642 to version 643.
Database 'mydb' running the upgrade step from version 643 to version 644.
Database 'mydb' running the upgrade step from version 644 to version 645.
Database 'mydb' running the upgrade step from version 645 to version 646.
Database 'mydb' running the upgrade step from version 646 to version 647.
Database 'mydb' running the upgrade step from version 647 to version 648.
Database 'mydb' running the upgrade step from version 648 to version 649.
Database 'mydb' running the upgrade step from version 649 to version 650.
Database 'mydb' running the upgrade step from version 650 to version 651.
Database 'mydb' running the upgrade step from version 651 to version 652.
Database 'mydb' running the upgrade step from version 652 to version 653.
Database 'mydb' running the upgrade step from version 653 to version 654.
Database 'mydb' running the upgrade step from version 654 to version 655.
RESTORE DATABASE successfully processed 17850 pages in 9.746 seconds (14.308
MB/sec).

Now you can start using your new database; here's an osql command line:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe" -S Envy -U sa -P j68Fje9#fyu489 -d mydb



No comments: