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.

Tags: 2005, backup, express, migration, restore, script, sql

Posted in IT | No Comments »