Copy SQL Server 2012 database to Local DB

Hi I have a SQL 2012 database backed up and would like to restore it to a C# application and use as a local database

I have this

ALTER DATABASE cmas
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE Christmas
FROM DISK = 'C:\Holiday\Christmas.bak'
WITH MOVE 'Christmas.mdf' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas.mdf',
MOVE 'Christmas.Log' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas.log',
REPLACE

ALTER DATABASE cmas SET MULTI_USER
GO


There is a source  backup file at  C:\Holiday\Christmas.bak    and I want to move it to  C:\ApplicationData\trunk\Holiday\App_Data\Cmas.mdf

for use in my app I have complete control and can use whatever names needed

Thank You
Charles BaldoSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You're altering a different db than the one you're restoring.  You need to restore based on the name  you want, not on the name of the original db that was backed up.  Otherwise it looks OK.

ALTER DATABASE cmas ...

RESTORE DATABASE cmas ...

ALTER DATABASE cmas ...
0
Charles BaldoSoftware DeveloperAuthor Commented:
I am getting close Now I have this

ALTER DATABASE cmas
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE cmas
FROM DISK = 'C:\Holiday\Christmas.bak'
WITH MOVE 'Christmas.mdf' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas.mdf',
MOVE 'Christmas.Log' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas.log',
REPLACE

ALTER DATABASE cmas  SET MULTI_USER
GO

getting this

Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'cmas', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3234, Level 16, State 2, Line 5
Logical file 'Christmas.mdf' is not part of database 'cmas'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
Msg 5011, Level 14, State 5, Line 11
User does not have permission to alter database 'cmas', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 11
ALTER DATABASE statement failed.
0
Scott PletcherSenior DBACommented:
Once you set "cmas" to SINGLE_USER, and another use gets a connection to it, you won't be able to.  

You'll have to find that session id and cancel it while immediately USEing that db yourself -- which most often works but not always.

Typically you'll want to make sure you are in the db before you issue the "SINGLE_USER" command to make sure that you are the user that has the single connection, not anyone else.

You'll also have to make sure the logical file names in the MOVE statement exactly match the logical file names in the original db.  You can use the command:
EXEC Christmas.sys.sp_helpfile
to get the logical file names for that db.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Charles BaldoSoftware DeveloperAuthor Commented:
Scott Thank you,

I am the only one in the database it is a SQL Express on my machine
I tried creating a service database called cmas with visual studio and not having it,  I run VS as administrator

I now have this

ALTER DATABASE cmas
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

RESTORE DATABASE cmas
FROM DISK = 'C:\Holiday\Christmas.bak'
WITH MOVE 'Christmas.mdf' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas.mdf',
MOVE 'Christmas_Log.ldf' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas_log.ldf',
REPLACE

ALTER DATABASE cmas  SET MULTI_USER
GO

EXEC Christmas.sys.sp_helpfile   gives me:

c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Christmas.mdf
c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Christmas_log.ldf

The file is a backup

Is it a security issue?   I get this now

Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'cmas', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3234, Level 16, State 2, Line 5
Logical file 'Christmas.mdf' is not part of database 'cmas'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
Msg 5011, Level 14, State 5, Line 11
User does not have permission to alter database 'cmas', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 11
ALTER DATABASE statement failed.
0
Scott PletcherSenior DBACommented:
>> EXEC Christmas.sys.sp_helpfile   gives me:

c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Christmas.mdf
c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Christmas_log.ldf
<<

That's not right.  That's the physical file name -- there should be a logical file name before that in the output.
0
Charles BaldoSoftware DeveloperAuthor Commented:
Christmas      1      c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Christmas.mdf      PRIMARY      61504 KB      Unlimited      1024 KB      data only

Christmas_log      2      c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Christmas_log.ldf      NULL      18560 KB      2147483648 KB      10%      log only
0
Scott PletcherSenior DBACommented:
RESTORE DATABASE cmas
FROM DISK = 'C:\Holiday\Christmas.bak'
WITH MOVE 'Christmas' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas.mdf',
          MOVE 'Christmas_Log.ldf' TO 'C:\ApplicationData\trunk\Holiday\App_Data\Cmas_log.ldf',
          REPLACE --only used if a db name of "cmas" already exists
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Charles BaldoSoftware DeveloperAuthor Commented:
Scott

Thanks I still have not got it. But learned a lot and know I will get it.  Does not seem right I keep coming back to you
0
Scott PletcherSenior DBACommented:
That's why there's a q forum :-).
0
Scott PletcherSenior DBACommented:
Did the RESTORE itself not work?  Or was the issue after that?

Btw, make sure after the RESTORE that you change the database owner to be the same as other dbs on the server.  SQL Server tends now to change the db owner whenever you restore a db, and the wrong owner can sometimes cause problems accessing the db later.
1
Charles BaldoSoftware DeveloperAuthor Commented:
Scott,

Not sure if you get this,  it does seem to be ownership.  I finally ended up copying the mdf and ldf to the app_data folder and using the server manager in visual studio to generate the connections.  Not sure if it was the right way or the best but it works so I am happy ,  thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.