Siehe auch:
Dieses SQL Script kopiert eine SQL Server Datenbank
Bitte kontaktieren Sie uns, falls Sie spezielle praktische Scripts brauchen, um Ihre Arbeit zu erleichtern.
USE master
GO
DECLARE @DB varchar(200)
SET @DB = 'CopyOfAdminproduktion'
-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Adminproduktion.dat'
DECLARE @TestDB varchar(200)
SET @TestDB = 'Adminproduktion'
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Adminproduktion'
DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'
DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '.ldf'
IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END
IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB
EXEC (@query)
END
RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT
DECLARE @Data varchar(500)
DECLARE @Log varchar(500)
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
Type varchar(10),
FilegroupName varchar(200),
Size int,
MaxSize bigint,
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueId uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
)
INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
PRINT @Data
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = 1, RECOVERY'
EXEC (@query)
GO
GO
DECLARE @DB varchar(200)
SET @DB = 'CopyOfAdminproduktion'
-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Adminproduktion.dat'
DECLARE @TestDB varchar(200)
SET @TestDB = 'Adminproduktion'
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Adminproduktion'
DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'
DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '.ldf'
IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END
IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB
EXEC (@query)
END
RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT
DECLARE @Data varchar(500)
DECLARE @Log varchar(500)
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
Type varchar(10),
FilegroupName varchar(200),
Size int,
MaxSize bigint,
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueId uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
)
INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
PRINT @Data
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = 1, RECOVERY'
EXEC (@query)
GO
