Having only limited DBA skills (99.9% of what I do with SQL is reporting), I ran in to some issues when I had to migrate a couple of small DBs from a win 2k3 box to a Win 7 (64-bit). I spent a few minutes trying to remember how to do this (haven’t done it in about 5 years), and settled for a simple backup/restore.
So first I ran the backup script that I use nightly, then just copied to the new box (on which I had already installed SQL 2005 Express and the management tools). From here I tried to simply do a “restore database”, but when I did I received the following error:
System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
I did some digging and figured out that the simplest way to resolve this was to just create a new file location for the backup destination. I also coiped my backup script and changed the NOFORMAT option to FORMAT
DECLARE @DBName varchar(255) DECLARE @DATABASES_Fetch int DECLARE DATABASES_CURSOR CURSOR FOR select DATABASE_NAME = db_name(s_mf.database_id) from sys.master_files s_mf where -- ONLINE s_mf.state = 0 -- Only look at databases to which we have access and has_dbaccess(db_name(s_mf.database_id)) = 1 -- Not master, tempdb or model and db_name(s_mf.database_id) not in ('Master','tempdb','model') group by s_mf.database_id order by 1 OPEN DATABASES_CURSOR FETCH NEXT FROM DATABASES_CURSOR INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN declare @DBFileName varchar(256) set @DBFileName = datename(dw, getdate()) + ' - ' + replace(replace(@DBName,':','_'),'','_') exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''e:migration' + @DBFileName + ''' WITH FORMAT, INIT, NAME = N''' + @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100') FETCH NEXT FROM DATABASES_CURSOR INTO @DBName END CLOSE DATABASES_CURSOR DEALLOCATE DATABASES_CURSOR
And the batch file that I use to schedule the script:
sqlcmd -S .sql_db_name -i Backup_tables.sql pause
Next I reran the script, and did a restore from the new files (remembering to change the options to update filepaths if necessary!), and this time it worked perfectly.