locked
Error when exporting a database to a new SQL database RRS feed

  • Question

  • User-2060727494 posted

    We run SQL 2012 on our dedicated windows server at our hosting company.  I use SQL Server management Studio from my PC access the SQL server.  So I want to create a new database on this server and export data from an existing db to the new db.  I am using SQL Server authentication.  So I go into SSMS and right-click on the db I want to export.  I choose the SQL source server and SQL option.  I then choose the destination server and SQL source option.  I then click the New button when it asks for the database to export to.  I enter a db name.  Then I get this error:

    TITLE: SQL Server Import and Export Wizard
    ------------------------------

    The database could not be created.

    ------------------------------
    ADDITIONAL INFORMATION:

    Create failed for Database 'SBarker_hitechnic_db'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.4100.1+((SQL14_PCU_main).150420-1653)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    I'm more of a developer than a SQL admin or dba.  I'm not real swift on permissions and such.  So it seems I have some kind of permissions issue.  But I have no idea what I'm doing wrong.  Can someone list the steps I need to perform that will allow me to create my new database?

    Friday, May 6, 2016 12:51 PM

Answers

  • User77042963 posted

    You need to connect to your SQL Server instance as admin role ( I don't know how you set that up: Window Authentication or Windows and SQL Server Authentication). The connected user should have the admin right to assign other user Sysadmin role (change security).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2016 7:39 PM

All replies

  • User77042963 posted

    There are many ways to move a database to a new server. An easy way is to table a backup of old and restore the backup in the new server and you may need to clean up and add new login in your the database at the new location.

    Friday, May 6, 2016 2:21 PM
  • User-2060727494 posted

    Just to be clear, the new database will be on the same server as the database I'm copying.  I just want to make a copy of one database and give the new database a different name.  I have a backup of my database.  But how do I restore this with a new database name?  Like I said, I'm pretty much clueless in this area.  Our hosting company presently handles all our backups.  I'm terrified of restoring my backup over my existing database.

    Friday, May 6, 2016 2:57 PM
  • User77042963 posted

    Here is the script you need:

    --- First: get your database files' logicalName 
     RESTORE FILELISTONLY
    FROM DISK = 'c:\backup\yourDb_backupfile.bak'
    GO
    
    ---Second:
    
    RESTORE DATABASE yourNewDataBase
    FROM DISK = 'c:\backup\yourDb_backupfile.bak'
    --put your old database logical name after WITH MOVE 
    
    WITH MOVE 'LogicalNameofYourOldDatabase' TO 'c:\backup\NEW_DataYourMDFFile.mdf', --data file
    MOVE 'LogicalNameofYourOldDatabase_log' TO 'c:\backup\New_DataYourLDFFile.ldf' --log file
    
    ---You can restore side by side with your old dataabse and have a copy of your old database with a new name.

    You can find detailed information from MSDN: https://msdn.microsoft.com/en-us/library/ms186858.aspx?f=255&MSPPError=-2147217396

    Let me know if you need more assistance.

    Friday, May 6, 2016 3:36 PM
  • User-2060727494 posted

    Thanks for your reply.  I'm just scared to death doing this stuff.  Just so I'm clear, this will not restore this backup to my present database right?  It will just restore this backup to a new database, correct?

    Friday, May 6, 2016 4:42 PM
  • User77042963 posted

    Yes. The restore process will create a new database with a new name and leave your old database untouched. They can live side by side.

    You can test out this script on your workstation (or non production server) to make yourself confident if you want to.

    Friday, May 6, 2016 6:21 PM
  • User-2060727494 posted

    Ok, just double-checking.  Our SQL dba / web admin guy suddenly left and he was the one who usually handled this type of thing.  I'll give it a go.

    Friday, May 6, 2016 6:48 PM
  • User-2060727494 posted

    Limno,

    Ok, I tried step 1.  Here is what I used:

    RESTORE FILELISTONLY
    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLNOPCOMMERCE\MSSQL\Backup\eCommerce.bak'
    GO

    But I got this error when I clicked execute:

    Msg 262, Level 14, State 1, Line 1
    CREATE DATABASE permission denied in database 'master'.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    Any idea what's wrong?

    Friday, May 6, 2016 6:53 PM
  • User77042963 posted

    I think you need to add yourself to Sysadmin role in your database server to allow you do the restore. Even you are a admin user of your windows machine, you still need to give the permission in your database server. You can add role and change permissions from SSMS.

    Friday, May 6, 2016 6:59 PM
  • User-2060727494 posted

    I have SSMS set-up on my server.

    Ok, so I clicked on Security and then Logins.  I then right-clicked on the login I use for SQL.  I then click Server Roles.  Next I click the checkbox for sysadmin.  I then get this error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Add member failed for ServerRole 'sysadmin'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+ServerRole&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=15151&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Friday, May 6, 2016 7:13 PM
  • User77042963 posted

    You need to connect to your SQL Server instance as admin role ( I don't know how you set that up: Window Authentication or Windows and SQL Server Authentication). The connected user should have the admin right to assign other user Sysadmin role (change security).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 6, 2016 7:39 PM
  • User-2060727494 posted

    That is the problem though.  I don't know how to do this either.  I was hoping someone could give me the steps to do this.

    Monday, May 9, 2016 10:44 AM