Restoring a .bak file locally - 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 2005 Forums

SQL Server Administration (2005)

Restoring a .bak file locally

Author

Topic

joliver310
Starting Member

3 Posts

Posted - 2009-11-17 : 15:52:37

Our hosted server doesn't have SSIS and therefore I can't use SSMS to import/export data. I'm totally new and unfamiliar with SQL, so bear with me. They suggested that I can download a backup of our database, open it locally, apply the imports/exports, then push it back up to the server.

I've downloaded the .bak file, and have Mgmt Studio open, but can't figure out how to open this file. I'm told that I need to create a database, then restore the .bak file to it. How do I do this??

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 15:57:33

First, you need to have SQL Server running locally

Do you have that going on?

If so, then you just need to do a RESTORE


-- Check the Back up

RESTORE FILELISTONLY FROM DISK = '\\<file path>\<file name>'
GO

-- If the DB Exists, Kick everyone out

ALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- RESTORE The database...this will overlay an existing one, OR Create a new one if not in existence
-- Get the Logical file names from filelistonly


RESTORE DATABASE <db_name>
   FROM DISK = '\\<file path>\<file name>'
   WITH   MOVE '<logical data file name>_data' TO '\\<file path>\<file name>.MDF'
        , MOVE '<logical log file name>_log'  TO '\\<file path>\<file name>.LDF'
	, REPLACE
GO


ALTER DATABASE <db_name> SET READ_WRITE
ALTER DATABASE <db_name> SET MULTI_USER
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

joliver310
Starting Member

3 Posts

Posted - 2009-11-18 : 05:46:10

I'm pretty sure I don't have a local instance of SQL Server. Do I need to reinstall SQL Server 2005 and include server components?

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-18 : 09:03:59

Do you know what bcp is?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

joliver310
Starting Member

3 Posts

Posted - 2009-11-18 : 18:27:01

Just looked at it for the first time. Sadly, I'm in way over my head I can tell hah. I'd pay someone for an hour to webex to me, help set up the db and restore the file, & apply the imports. It will take a week of forum posts to teach me each step at a time and something tells me it would be easier and more worth your time to do it that way instead. Can that happen?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources