Solved

SQL 2012 Express, sqlcmd,  Restore Database, user tables not restored.

Posted on 2014-03-02
12
672 Views
Last Modified: 2014-03-04
I have a script running sqlcmd to restore a database. It does not restore user tables, but does not give any errors. If I restore from the Management Studio, it works. Any idea what I am doing wrong?

Below is the syntax and the message:

RESTORE DATABASE PGMS
   FROM DISK = '\\SVR04\SVR04C\Backups\SVR04Apps\SQLSVR04.bak'
   WITH
     MOVE 'master'  TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PGMS.mdf',
     MOVE 'mastlog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PGMS.LDF',
     REPLACE,
     RECOVERY

Processed 424 pages for database 'PGMS', file 'master' on file 1.
Processed 3 pages for database 'PGMS', file 'mastlog' on file 1.
RESTORE DATABASE successfully processed 427 pages in 0.168 seconds (19.816 MB/sec).
0
Comment
Question by:MikeBroderick
  • 7
  • 4
12 Comments
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39898910
you are using Windows Authentication or SQL Server Authentication.

if Windows Authentication, try "Run as administrator"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39899336
Unfortunately. you cannot restore the master database that way.  Why don't you tell us what you are really trying to do?
0
 

Author Comment

by:MikeBroderick
ID: 39899337
I am using Windows Authentication. I tried "Run as Admin", same error.
0
 

Author Comment

by:MikeBroderick
ID: 39899338
Sorry, I'm not trying to restore the master database. I am trying to restore a production database called PGMS. Apparently its logical file names are master and mastlog.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 39899346
Apparently its logical file names are master and mastlog.
Are you sure?
Can you confirm by doing the following:

SELECT  name,
        physical_name
FROM    sys.master_files
WHERE   DB_NAME(database_id) = 'PGMS'

Open in new window

0
 

Author Comment

by:MikeBroderick
ID: 39899432
The logical file names before the sqlcmd restore were A and A_LOG. After the restore they are master and mastlog.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:MikeBroderick
ID: 39899470
I should add that I did not originally have the MOVE TO clauses, I added them while trying to make it work. I get the same error without the MOVE TO clauses.  I just tried changing the logical file names in the MOVE TO clauses to 'A' and 'A_LOG'. I get the following:

The file "master" is selected. At the time of backup it was known by the name "A". RESTORE will continue operating upon the renamed file.
The file "mastlog" is selected. At the time of backup it was known by the name "A_log". RESTORE will continue operating upon the renamed file.
Processed 424 pages for database 'PGMS', file 'master' on file 1.
Processed 3 pages for database 'PGMS', file 'mastlog' on file 1.
RESTORE DATABASE successfully processed 427 pages in 0.208 seconds (16.005 MB/sec).

But the same problem, no user tables.

The backup file has the databases master, model, and msdb on it also. It appears the restore is trying to restore the model database's files into the PGMS database. In the old days you had to display the backup file and locate the file numbers within the file for the database you want, and specify the "file numbers" within the file on the restore script. I don't see any parms to do that now. Am I missing something?
0
 

Author Comment

by:MikeBroderick
ID: 39899474
I should add I am backing up from system 3 and restoring to system 4. Also note that the number of pages restored matches the number of pages saved on system 3.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39899501
I am totally lost as to what you are doing.  May I suggest that since you state that "If I restore from the Management Studio, it works.", that you grab the SQL Script from there, without running the restore.
0
 

Author Comment

by:MikeBroderick
ID: 39900375
I am setting up a "poor man's" replication, where each night I backup the database PGMS off of the production system SVR04 into a file on the hard disk, and restore PGMS to another system SVR03 using the file on the hard disk. I am having a problem with the restore script (see above). The problem is seeming to boil down to this: The sqlcmd RESTORE DATABASE doesn't work, or I am doing something wrong. I mention that using Management Studio works verify the backup file is not corrupt.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39902194
I mention that using Management Studio works verify the backup file is not corrupt.
I understand.  Perhaps you are not familiar with the way you can get the SQL script that SSMS is using?  If so, do the Restore from SSMS but don't click on OK, instead click on the Script icon in the top left corner.
0
 

Author Closing Comment

by:MikeBroderick
ID: 39903437
Your questioning me on the logical file names made me suspect the first saved database in the file was being used for the restore. When I clicked the script icon, the script showed with a FILE = 4 clause. I didn't know you could do that. When I added it to my script, it worked!

Thank you.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now