February 13, 2014 at 12:00 pm
We have a recurring need to move/copy databases between servers, such as (1) moving from a testing server to production, and (2) migrating from SQL 2000 to a new server running SQL 2008.
Task (1) is mostly 2000 -> 2000 currently, but as we phase in upgrades, it will change to 2000 -> 2008 and finally 2008 -> 2008. (The SQL 2000's are on Win Server 2003, and the SQL 2008's are on Win Server 2008 R2.)
For years it has always appeared to me that the fastest/easiest way to migrate is a backup/restore across the network, and I can do this manually with the GUI. But some other people just don't seem to understand it (especially since SQL Server can be picky about backup/restore to/from a network share), and besides it's repetitive enough we need an easy 1-step procedure.
It seems the following kind of T-SQL sequence should work, but I just can't seem to get it right. I've tried a number of variations and get different errors.
EXEC sp_dropdevice 'tmpbackuppath';
EXEC sp_addumpdevice 'disk', 'tmpbackuppath', '\\server\path\SqlTmpBackup.bak';
BACKUP DATABASE Oldserver.DBname TO DISK = tmpbackuppath WITH INIT;
RESTORE DATABASE Newserver.DBname FROM tmpbackuppath;
(I'm also probably going to add the COPY_ONLY option to the backup, to avoid disrupting the regular backup sequence.)
(We have several -- often dozens or hundreds -- databases on each server. In usage scenario (2), the backup/restore sequence would be repeated for each. This is another reason the GUI method is problematic -- it's much too slow for mass moves.)
Anyway, this should be simple, and yet I just can't find the right syntax. I've tried \Server\DBname, Server.DBname, Server.dbo.DBname, etc. and nothing works. (In this example I'm not specifying an instance name because these are using the default instance, but there is one case where we need an instance name also.) There don't seem to be any all-in-1 examples of how to do this, anywhere in the docs or the Web. So what is the right syntax?
February 13, 2014 at 2:50 pm
You can use UNC paths in the BACKUP DATABASE command. Just backup directly to the destination server, then have a script on the other end to do a restore from backup.
BACKUP DATABASE Prod TO DISK = '\\SomeServer\BACKUPS\Full\Prod.bak' WITH COMPRESSION, STATS, INIT, NAME = 'Prod - Full'
February 13, 2014 at 3:19 pm
Gary Gwyn (2/13/2014)
You can use UNC paths in the BACKUP DATABASE command.
Yes, I know, but the problems are the same.
Just backup directly to the destination server
That's not always possible, due to permissions and firewalling. And it would mean the script would have the know the default backup paths of all the different servers involved (they do vary somewhat, mostly for historical reasons). I want to use a common location. But wherever the location, the problems are the same.
then have a script on the other end to do a restore from backup.
I'm talking about doing it all in the SAME script. The whole idea is to make it simple and quick. Besides, if you are migrating 200 databases (mostly in scenario 2), it's very inefficient to be switching back and forth all the time, while your 24/7 users are tapping their fingers at 3 AM and you are losing more sleep.
February 13, 2014 at 3:55 pm
The script will be tricky then if permissions and uncommon file paths are an issue.
If you have a linked server set up between OldServer and NewServer, then you could create a stored procedure for the restore portion on the new server and then call that stored procedure after the backup command on the old server.
I've done this before during a migration.
Again, the syntax will depend on your environment. As long as the SQL service account has full rights to the folder/drive/share to which are you backing up your data, then the backup to UNC path should work.
February 14, 2014 at 4:32 am
I've done this using both T-SQL and PowerShell, but the trick is, you have to be able to set up access to a common location. If you can't do that, then you can't have a single, universal, script to get the job done. There's no magic that I'm aware of.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2014 at 5:17 am
wodom (2/13/2014)
For years it has always appeared to me that the fastest/easiest way to migrate is a backup/restore across the network,
To a certain point, if the database is not required to be online at the source, either a
detach\copy files\attach
or an
offline\copy files\attach
May be faster and easier. It all depends what the required state is for the source database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 14, 2014 at 10:43 am
OK, I've now had something of a breakthrough. After my last post, I went back to the problem and managed to alter my thinking. Key #1 is, I guess a T-SQL procedure has to run in the context of a single DB -- you can't just change the context on-the-fly to run under another DB. So I started thinking about backing out one level, to a Windows procedure (command line) that connects to each DB in turn. Turns out you can do this as follows (this time I'm specifying the explicit path always):
Sqlcmd -S Oldserver
BACKUP DATABASE DBname TO DISK = '\\server\path\SqlTmpBackup.bak' WITH INIT;
GO
Exit
Sqlcmd -S Newserver
RESTORE DATABASE DBname FROM DISK = '\\server\path\SqlTmpBackup.bak';
GO
Exit
After getting this concept clear, I also realized I could try the method of linked servers. Some of our servers are linked already. If Newserver is linked in Oldserver, AND the procedure is run in the context of Oldserver, it turns out it will work if you (and this is Key #2) get rid of any prefixes from the source DB, and insert the normal linked-server prefix to the target DB name, like this:
BACKUP DATABASE DBname TO DISK = '\\server\path\SqlTmpBackup.bak' WITH INIT;
RESTORE DATABASE NewserverLink.DBname FROM DISK = '\\server\path\SqlTmpBackup.bak';
After getting this far, I ran into the next problem, which is that it wants to restore the files to the exact paths they started from. In my test case, the paths are different -- the destination directory doesn't even exist, which generates an error. I was hoping it would restore into the default data directory of Newserver, but no such luck. So this requires the restore to be WITH MOVE. I'll either need to make tell the routine which path to use for every different server and instance, or get the default path on-the-fly from the server. The latter appears to require different code for SQL2K and 2008, so that would be getting rather complex. Maybe I'll choose the former method ... until we get everything migrated to 2008, during which I'll be trying to standardize the data paths to the extent feasible.
There are also issues of what to do if the DB already exists (which it might in some cases), making sure logins work, etc., but in any case, using Keys #1 and #2 above, I've gotten past the original backup/restore syntax roadblock.
Thanks to all who answered.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply