-1

I tried this with SQL Server 2012, no joy:

How can I copy a SQL Server 2008 instance to another server?

SQL would not start on the target server it first complained about not being able to write to it's error log files and now it's complaining that it has an internal error.

I think it's probably a ACL/rights thing but the security looks the same, all I did was overwrite the existing SQL install directory with the SQL directory from the source server.

The source server is Windows 2008 R2 the target server is Windows 2012 R2 if that makes any difference

Yes I know how to do it the "sp_help_revlogin" way but we have a lot of users and I don't want to have to run around to everyone and get their password

This is a SIMPLE operation with MSql why in blazes does MS have to stick all kinds of hidden rights and garbage into their filesystem? When the SQL server is shut down the database should be just another file IMHO.

Also if the SQL server ever takes a dump I'd really like to not have to restore all 600 GB from a backup image if I can just restore 80GB in the SQL database files, you know?

Ted Mittelstaedt
  • 101
  • 1
  • 1

3 Answers3

4

SQL DBA here. I think you are misunderstanding how sp_help_revlogin works: http://support.microsoft.com/kb/918992

It is a set of scripts you run on the source server, the output will be a big long script to re-create all logins, including (encrypted) passwords.

Take that script and run it on your destination server, it will re-create all logins, and your restored (or attached) databases should recognize them just fine.

5 minute process, tops, unless you want to go through the output list and include/exclude individual logins.

I don't know what you bogged up with copying the whole entire folder full of SQL files, you may have to rewind a step or two there, if you didn't just overwrite the files from the newly installed instance.

My preferred method would be:

  1. Get the new SQL instance running from a clean install
  2. Copying MASTER is possible, but very tricky. Frankly the only system db I would copy would be MSDB (to keep scheduled jobs), or just script them out from the old box and re-create them.
  3. For the user databases, either do a backup/restore from the old box, or copy just the data (mdf, ndf) and log files (ldf) to the new box and attach them.

Good luck. You might also find more detailed help over at dba.Stackexchange.com.

BradC
  • 2,200
  • 4
  • 25
  • 35
  • Hi Brad - No, I do understand what revlogin does (although until I actually see it produce the encrypted passwords, suck them into the new DB, and verify that they do indeed work I will not count on that feature working) but the problem is that you have to modify the source database - by installing revlogin as a stored procedure. Thus, if the customer has something completely unrelated blow chunks on the source server's database a week later, I cannot tell them "I didn't make any changes to the source database" However, if I just do a simple copy I CAN do that. – Ted Mittelstaedt Jul 23 '17 at 00:36
  • @TedMittelstaedt In case it helps, you can install revlogin on *any* database on the source server (master, or a special DBA database you make just for this purpose), then delete it afterwards. It doesn't have to be in the source *user* database. – BradC Jul 23 '17 at 02:43
2

I think it's probably a ACL/rights thing but the security looks the same, all I did was overwrite the existing SQL install directory with the SQL directory from the source server.

Why didn't you just backup and restore the databases?

why in blazes does MS have to stick all kinds of hidden rights and garbage into their filesystem? When the SQL server is shut down the database should be just another file IMHO.

Blaming Microsoft for your lack of understanding isn't cool. It's not their fault that you chose an unsupported method to transfer the databases. The databases ARE just files. You can easily backup and restore them or detach them, copy them to the new server, and attach them.

Also if the SQL server ever takes a dump I'd really like to not have to restore all 600 GB from a backup image if I can just restore 80GB in the SQL database files, you know?

Of course this can easily be done, but first you have to know what you're doing.

None of this is the fault of SQL Server or Microsoft.

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
joeqwerty
  • 108,377
  • 6
  • 80
  • 171
  • "Why didn't you just backup and restore the databases?" Because the logins are not stored in the databases. "Blaming Microsoft for your lack of understanding isn't cool." I think I made it clear in the initial question that I needed more than just the databases, or do you also have a lack of understanding that logins and IDs and such are not stored in the databases? – Ted Mittelstaedt Jul 18 '17 at 10:16
  • SQL Logins are stored in the master database. Database users are mapped to SQL Logins. There are well known and widely used methods for dealing with orphaned logins when a database is moved from one SQL Server to another. What you're doing isn't unique or new. People have been moving SQL databases from one server to another for as long as SQL Server has been around. – joeqwerty Jul 18 '17 at 11:18
  • My understanding is there is a dependency in the master database on the server name. So you cannot just take a backup of the master from the source server and overwrite the destination server without further manipulation if you are going to run the old and new server in parallel. There's also temp databases and in this situation there's more of them on the source server than the new destination server, I assume there's references in the master database to those which means those will have to be created in the target sql server. The official documentation on this is very lacking! – Ted Mittelstaedt Jul 23 '17 at 00:47
  • You don't need and shouldn't copy the master database to the destination server. The temp databases are just that, temporary. They're created and destroyed every time SQL Server starts and stops. Temp databases will be created automatically if and when needed on the destination server. They aren't relevant to the databases you need to move. – joeqwerty Jul 23 '17 at 00:51
1

Your databases ARE "just" files. If you right-click the database in SSMS and click on properties, you can find that information. There is usually the database file (.mdf) and the transaction log (.ldf).

I think where you went wrong is that you shouldn't copy the entire contents of the installation path over. Instead, you are only supposed to copy over the .mdf and .ldf files back to the exact same location as they were on the source server.

I usually just move individual database files that I need and re-add them manually in SQL. However, I believe if you copy the system databases you will transfer over the entire SQL configuration. The most important thing here is that all the databases are in the same path they were originally on the source server because that information will be contained in the system databases.

When it comes to backup and restore - YES, you can just restore the .mdf and .ldf files. But, the various methods of backup and restore of SQL is for another topic.

Ofcourse, with all of this, you need to make sure the SQL server service is stopped, or the database is offline.

Appleoddity
  • 3,290
  • 2
  • 10
  • 27
  • Thanks, appleoddity. I do understand that Microsoft is moving (glacially) away from the login mess in SQL server 2008 and earlier with the release of contained databases in mssql 2012, but they are pushing the work on the developers, instead of just doing it themselves. – Ted Mittelstaedt Jul 18 '17 at 10:31