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_CURSORAnd 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.
