1

I have this database I need to move to a test server. The only way I can do this according to my searches is to create a script. The database is on SQL Server 2014, I am using 2012). I can get the script to generate (1.3 gigs), I am using sqlcmd to run it, but I have run into issues.

The first issue was that it was complaining about the database not existing, so I created a database with the same name and hoped I got the settings right. This solved that issue, but now it is complaining about the users not existing or me not having permission (I ran the script as the 'sa' user), and not all stored procedures and assemblies get created.

I just need a copy of the database to test against so I can find an issue without breaking the live database. Could someone help me with this, please?

I am unable to upgrade the database. The only thing I have permission to do is copy/backup the database, so long as it does not disrupt it. On the test-server side, it is a virtual machine that I restored from backup with Windows Server 2012 and SQL Server 2012 already installed on it.

I seem to have misunderstood part of what the script does. It does not re-create the database, but sets it up again. I was able to make it work when I create a new database, set up the database so that everything would be able to be recreated (set unsafe assemblies on and such). The users do not get recreated, and must be created, given permissions, etc. manually, as the script will try to log into the windows users with the old server name. All the script does is set the tables, stored procedures, assemblies, etc. and fill them with data. I wish they would have been more clear on that.

  • What do you mean when you say you are using scripts to move the database? Are you using any scripts that will take the backups? – karun_r Jun 11 '16 at 1:09
  • @karun10: I am using SSMS. Left-click on the database, under tasks, using the script wizard that pops up when you click "Generate Scripts..." – dabuda Jun 13 '16 at 13:34
  • 1
    1.3 gigs of scripts is crazy. You are scripting database objects and data. Why not right-click the database and select "tasks --> backup..." to create a full database backup. Then copy the file and restore it to the new server? – datagod Jun 14 '16 at 16:33
1

You simply need to create a full database backup and restore that to your new server.

How to backup a database using SSMS

How to restore a database using SSMS

  • Unfortunately, backing up the server like this makes it impossible to be restored on an earlier version of SQL server. – dabuda Jun 14 '16 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.