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.