.







Cenk Yurtseven
 
English Deutsch Türkçe
Home SQL Server 2008 SQL Server 2008
 3.  von insgesamt  7  Ergebnissen Anzeige: 


 
Copy SQL Database Copy SQL Database Sprache:  T-SQL 
Copy SQL Server Database

Copy SQL Database
 

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.1\MSSQL\Data\Adminproduktion.dat'

-- the new database name
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.1\MSSQL\Data\Adminproduktion'

 
-- ********************************************

-- no change below this line

-- ********************************************

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

-- This always returned 0 for me but the
-- RESTORE call returned the number
-- of rows associated with the backup. Strange...
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


 3.  von insgesamt  7  Ergebnissen Anzeige: 


« Vorige  1  2  3  4  5  6  7  Nächste »
Member Login  |  Kontakt