Create/restore database from backup SQL Server Express

Question

I don't have SQL Server Management Studio on my machine.

I have a database backup (SQL Server 2008 R2). There is SQL Server Express that installed with Visual studio 2010 ultimate installed on my machine.

How can I restore this back up on a database and attaching it to SQL Server Express?
Is there any solution wihout using SQL Managment Studio Express ?

Constrictive

Answer 1

Even with SQL Server Management Studio Express, you won't be able to restore this backup. The Express edition being installed with Visual Studio 2010 is version 2008 of SQL Server - your backup is one from a SQL Server 2008 R2 release - those backups cannot be restore onto a 2008 version.

You will need to download the SQL Server 2008 R2 Express version, and while you're at it - get the version with the Management Studio! Install this, and then you'll be able to restore your database backup.

If you really really want to restore your database without using Mgmt Studio - you can of course use a RESTORE statement in T-SQL and run this using the sqlcmd command line tool:

RESTORE DATABASE YourDatabaseName
FROM DISK = N'(path to your BAK file)'
WITH FILE = 1,  
MOVE N'(your DB name)' TO N'(your SQL path)database.MDF',  
MOVE N'(your DB name)_LOG' TO N'(your SQL path)database_LOG.LDF',  
NOUNLOAD,  
REPLACE,  
STATS = 10
GO

(and of course, there's also a BACKUP command which you can use in a similar fashion - the MSDN Books Online are your friend for details about the syntax!!).

Chrissy

Does R2 released after Visual studio 2010 ultimate ? – Constrictive

@shaahin: Yes - it was released after VS 2010 was packaged up – Chrissy

Actually you can just copy and paste the code in a VS 2010 query window, and execute it. No Management Studio required and you do not need to enter the command line. One thing people need to concern is that it is better drop the fulltext catalogs before backup (for production db you can create a clone of the orginal db and drop the catalogs in the clone) because it seems (I am not quite sure) that SQL express does not support full text indexing. – Gateshead

Answer 2

Late one but hopefully useful to others who are viewing this….

If the problem is restoring without management studio this can be easily solved by using sqlcmd as marc_s already pointed out.

However if the real problem is restoring 2008 R2 backup on SQL 2008 without using SSMS then only solution is using third party tools that can read backup and generate scripts for creating schema and inserting data.

Idea is to create empty database on SQL 2008 and use a third party tool to compare it to backup generated with 2008 R2.

I’ve used ApexSQL Diff and ApexSQL Data Diff with good success in my previous job but there are also good tools from Red Gate (SQL Compare) and Idera (Comparison toolset). If you look up online you’ll probably find a lot more….

Disclaimer: I’m not affiliated with any of the companies mentioned above…

Dag

Answer 3

Why don't you get SQL Managment Studio Express? It is free, and should allow you to administer local sql express instances.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796

BACKUP DATABASE [AdventureWorks] TO  
    DISK = N'\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10

RESTORE DATABASE [AdventureWorksNew] 
    FROM  DISK = N'\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak' 
    WITH  FILE = 1,  
    MOVE N'AdventureWorks_Data' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Data.mdf',  
    MOVE N'AdventureWorks_Log' TO N'C:\Data\MSSQL.1\MSSQL\Data\AdventureWorksNew_Log.ldf',  
    NOUNLOAD,  STATS = 10

Pierpont

Its good way but is there any solution wihout SQL Managment Studio Express ? – Constrictive

Answer 4

Following link provides similar solution as marc_s, and it goes even deeper. I was able to successfully create a new DB from Backup file with the solution provided in the link below.

http://codeonaboat.wordpress.com/2012/02/16/sql-server-2008-creating-a-database-from-a-bak-file/

The solution below is copied from above link:

1. In SSMS, open a query window in the master database of the database server. That’s where you will run the following queries.

2. See what the “LogicalName” of the database that has been backed up in the .bak file is

   RESTORE FILELISTONLY FROM DISK = 'c:\DatabaseBackups\dev2012021601.bak'

This will give you the logical name of the database and its associated log file. Lets assume that the names are “dbname” and “dbname_log”

1. Now run the following restore command. Make sure that a database with the name you are trying to create doesn’t already exist (in the code sample below, dbForSocialMigration doesn’t exist).

RESTORE DATABASE dbForSocialMigration
FROM DISK = 'c:\DatabaseBackups\dev20120307.bak'
WITH
MOVE 'dbname' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\dbForSocialMigration.mdf',
MOVE 'dbname_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\dbForSocialMigration_log.mdf'

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA is the directory where SQL Express usually keeps its data files. You can find the directory your database server is using by selecting a database from it, right clicking and opening the properties dialog and selecting the “Files” option from the left.

That should work, and at that point you should be able to access the database “dbForSocialMigration” populated with all the tables and data from the .bak file.

Exiguous