MSSQL Backup und Restore mit T-SQL

Mit dem Untenstehenden Script hat man alle Befehle zur Hand um eine Datenbank von SQL 2000 auf SQL 2005 zu migrieren.

/*****************************************************************************
* SQL Migration Script
* 30.07.2008 Andres Bohren - www.icewolf.ch
*****************************************************************************/

/*Create DB*/
CREATE DATABASE mydatabase

/*Backup*/
BACKUP DATABASE mydatabase TO DISK = 'C:\backup\mydatabase.bak" WITH INI'

/*Restore*/
RESTORE DATABASE mydatabase FROM DISK = '' WITH RECOVERY,
MOVE mydatabase TO 'D:\MSSQL.1\Data\mydatabase.mdf'
MOVE mydatabase_log TO 'D:\MSSQL.1\Data\mydatabase_log.ldf'

/*Create SQL Users*/
CREATE LOGIN myuser WITH PASSWORD = 'secret', CHECK_POLICY = OFF, DEFAULT DATABASE = mydatabase

/*Create Windows Users*/
CREATE LOGIN [CORP\a.bohren] FROM WINDOWS DEFAULT DATABASE = mydatabase

/*Security*/
USE mydatabase
EXEC sp_changedbowner 'sa'
DROP USER [corp\m.bohren]
CREATE  USER [corp\m.bohren]
EXEC sp_droprolemember 'db_datareader', [CORP\m.bohren]
EXEC sp_addrolemember 'db_datareader', [CORP\m.bohren]
EXEC sp_helpuser

Grüsse
Andres Bohren