R
RSH
Im trying to create a stored procedure of the following code. I want to set
it so that I have to send the first 4 variables (@DB,
@BackUpFile,@TestDB,@RestoreFile). I am having trouble when i try to save
it...SQL Server wants me to decalre the variables. How would I go about
making this a stored procedure correctly?
Here is the code:
USE master
GO
-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'Blank'
-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'D111111111'
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
-- ****************************************************************
-- 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
-- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'
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
)
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
IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' +
QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' +
QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE
= ' + CONVERT(varchar, @File)
EXEC (@query)
END
GO
Here is what I tried for a stored procedure:
CREATE PROCEDURE sp_CreateNewCompanyDB
-- the original database (use 'SET @DB = NULL' to disable backup)
@DB varchar(200) =NULL,
--SET @DB = 'Blank'
-- the backup filename
@BackupFile varchar(2000)=NULL,
--SET @BackupFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
-- the new database name
@TestDB varchar(200)=NULL,
--SET @TestDB = 'D111111111'
-- the new database files without .mdf/.ldf
@RestoreFile varchar(2000)=NULL
--SET @RestoreFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
As
GO
-- ****************************************************************
-- 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
-- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'
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
)
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
IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' +
QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' +
QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE
= ' + CONVERT(varchar, @File)
EXEC (@query)
END
GO
it so that I have to send the first 4 variables (@DB,
@BackUpFile,@TestDB,@RestoreFile). I am having trouble when i try to save
it...SQL Server wants me to decalre the variables. How would I go about
making this a stored procedure correctly?
Here is the code:
USE master
GO
-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'Blank'
-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'D111111111'
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
-- ****************************************************************
-- 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
-- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'
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
)
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
IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' +
QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' +
QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE
= ' + CONVERT(varchar, @File)
EXEC (@query)
END
GO
Here is what I tried for a stored procedure:
CREATE PROCEDURE sp_CreateNewCompanyDB
-- the original database (use 'SET @DB = NULL' to disable backup)
@DB varchar(200) =NULL,
--SET @DB = 'Blank'
-- the backup filename
@BackupFile varchar(2000)=NULL,
--SET @BackupFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
-- the new database name
@TestDB varchar(200)=NULL,
--SET @TestDB = 'D111111111'
-- the new database files without .mdf/.ldf
@RestoreFile varchar(2000)=NULL
--SET @RestoreFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\backup4'
As
GO
-- ****************************************************************
-- 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
-- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'
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
)
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
IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' +
QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' +
QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE
= ' + CONVERT(varchar, @File)
EXEC (@query)
END
GO