|
|
|
CREATE PROC [dbo].[BackupDatabase] (@IN_Database VARCHAR(255), @IN_File VARCHAR(1000))
AS
SET NOCOUNT ON
EXEC ('BACKUP DATABASE '[ + @IN_Database + '] TO DISK= ''' + @IN_File + '''')
CREATE PROC [dbo].[RestoreDatabase] (@IN_DATABASE VARCHAR(255), @IN_FILENAME VARCHAR(1000))
AS
SET NOCOUNT ON
-- WICHTIG: Diese SP muss von einer anderen Datenbank, als der Zieldatenbank ausgefuehrt werden:
USE master
-- Alle Prozesse auf der Zieldatenbank killen (ein DROP DATABASE waere auch moeglich):
DECLARE @pid INT
DECLARE procs CURSOR FAST_FORWARD FOR
SELECT p.spid AS [Process ID]
FROM master.dbo.sysprocesses p INNER JOIN master.dbo.sysdatabases d ON d.dbid = p.dbid
WHERE (d.name = @IN_Database)
OPEN procs
FETCH NEXT FROM procs INTO @pid
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC (' USE [' + @IN_DATABASE + '] ' + ' KILL ' + @pid)
FETCH NEXT FROM procs INTO @pid
END
CLOSE procs
DEALLOCATE procs
-- und jetzt die Datenbank wieder einspielen:
RESTORE DATABASE @IN_DATABASE FROM DISK=@IN_FILENAME
|
|
|
|
|
|
-- =======================================================================================================================================
-- SQL Script for creating a database - named "MyDatabase" in this example - from a BAK file.
-- Please note that you need to replace some text items in the script with names and paths of your "real world" solution :-).
-- =======================================================================================================================================
-- =======================================================================================================================================
-- Drop the database first if it exists
-- "MyDatabase" has to be replaced with the name of the database to be dropped.
-- =======================================================================================================================================
USE master
GO
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = 'MyDatabase'
)
DROP DATABASE MyDatabase
GO
-- =======================================================================================================================================
-- Now create the database
-- "MyDatabase" has to be replaced with the name of the database to be created.
-- =======================================================================================================================================
CREATE DATABASE MyDatabase
-- =======================================================================================================================================
-- Restore Database from BAK file
-- PathOfMyBAKFile has to be replaced by the path of the BAK file to be used as source for restoring the DB,
-- e.g. "c:\Database\Backups\MyDatabaseBackupFile.bak"
-- =======================================================================================================================================
USE master
GO
RESTORE FILELISTONLY
FROM DISK=N'PathOfMyBAKFile'
GO
-- =======================================================================================================================================
-- Make Database to single user Mode
-- "MyDatabase" has to be replaced with the name of the database for which to set the user mode.
-- =======================================================================================================================================
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
-- =======================================================================================================================================
-- Restore Database from BAK file
-- "PathOfMyBAKFile" has to be replaced by the path of the BAK file to be used as source for restoring the DB,
-- e.g. "c:\Database\Backups\MyDatabaseBackupFile.bak"
-- "BackupSourceDatabase" has to be replaced with the name of the source database from which the BAK file has been created.
-- "LocalMsSQLServerPath" has to be replaced with the path to your MS SQL Server installation folder,
-- e.g. "C:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL" in a German version of Windows or
-- "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL" in an English version of Windows.
-- What are the MOVE commands good for ?
-- If you want to restore from a BAK file which has been created for a database named differently than the database you want to
-- use as target, you have to use the MOVE commands to be able to restore to your new named database.
-- e.g. you have created your BAK file from a database named "A" and you want to use the BAK file to restore into a new database named "B"
-- you have to use the MOVE command
-- "MOVE 'A' TO N'<LocalMsSQLServerPath>\Data\B_Data.mdf'" for the data and
-- "MOVE 'A' TO N'<LocalMsSQLServerPath>\Data\B_Log.mdf'" for the logs.
-- =======================================================================================================================================
RESTORE DATABASE MyDatabase FROM DISK=N'PathOfMyBAKFile'
WITH
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10,
MOVE 'BackupSourceDatabase' TO N'LocalMsSQLServerPath\Data\MyDatabase_Data.mdf',
MOVE 'BackupSourceDatabase_Log' TO N'LocalMsSQLServerPath\Data\MyDatabase_Log.ldf'
GO
InitializeTestDataBasewird die Test Datenbank initialisiert - und zwar durch Ausführen des SQL-Scripts aus Teil 1. Da das Script auf der Master Datenbank ausgeführt wird, verbinde ich mich mit der "master" DB. Die zu erstellende DB ist ja ohnehin noch nicht existent, also kann man sich logischerweise auch nicht damit verbinden :-).
ApplyChangesToDbScriptrufe ich auf, um einige Texte des Scripts dynamisch durch andere Dinge zu ersetzen - Da wir sowohl englisch, als auch deutsche Windows-Versionen einsetzen, gäbe es beim Zurücksichern der Datenbank einen Fehler, wenn wir fix "C:\Programme\Microsoft SQL Server\MSSQL.1" oder "C:\Program Files\Microsoft SQL Server\MSSQL.1" im Script verwenden würden.
private void InitializeTestDataBase()
{
string sqlConnectionString = @"Server=.\SQLEXPRESS; Database=master; Integrated Security=true; Connection Timeout=30";
FileInfo file = new FileInfo(DbScriptFileName);
if (file.Exists)
{
string script = file.OpenText().ReadToEnd();
// Optional: Apply changes to db script if needed - such as replacing file paths if your solution is executed from a different working directory, e.g. when running in a unit test.
script = ApplyChangesToDbScript(script);
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
conn.Close();
}
}
private static string ApplyChangesToDbScript(string scriptFileContent)
{
const string germanOsSqlServerPath = @"C:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL";
const string englishOsSqlServerPath = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL";
scriptFileContent = scriptFileContent.Replace("PathOfMyBAKFile", BackupFilePath);
if (Directory.Exists(germanOsSqlServerPath))
{
scriptFileContent = scriptFileContent.Replace("LocalMsSQLServerPath", germanOsSqlServerPath);
}
else if (Directory.Exists(englishOsSqlServerPath))
{
scriptFileContent = scriptFileContent.Replace("LocalMsSQLServerPath", englishOsSqlServerPath);
}
return scriptFileContent;
}
|
|
|
