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,@Te stDB,@RestoreFi le). 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\BA CKUP\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\BA CKUP\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(@Back upFile, '''')
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(@Back upFile ,
'''')
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(@Back upFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data , '''') + ' TO ' +
QUOTENAME(@Data File, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogF ile, '''') + ', FILE
= ' + CONVERT(varchar , @File)
EXEC (@query)
END
GO
Here is what I tried for a stored procedure:
CREATE PROCEDURE sp_CreateNewCom panyDB
-- the original database (use 'SET @DB = NULL' to disable backup)
@DB varchar(200) =NULL,
--SET @DB = 'Blank'
-- the backup filename
@BackupFile varchar(2000)=N ULL,
--SET @BackupFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BA CKUP\backup4'
-- the new database name
@TestDB varchar(200)=NU LL,
--SET @TestDB = 'D111111111'
-- the new database files without .mdf/.ldf
@RestoreFile varchar(2000)=N ULL
--SET @RestoreFile = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BA CKUP\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(@Back upFile, '''')
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(@Back upFile ,
'''')
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(@Back upFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data , '''') + ' TO ' +
QUOTENAME(@Data File, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogF ile, '''') + ', FILE
= ' + CONVERT(varchar , @File)
EXEC (@query)
END
GO 1 1926
You should use the SQL Server programming forum for this type of question.
They will want you to post table structure (DDL), sample data, and desired
results also.
"RSH" <wa************ *@yahoo.com> wrote in message
news:uK******** ******@tk2msftn gp13.phx.gbl... 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,@Te stDB,@RestoreFi le). 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\BA CKUP\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\BA CKUP\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(@Back upFile, '''') 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(@Back upFile , '''')
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(@Back upFile, '''') + ' WITH MOVE ' + QUOTENAME(@Data , '''') + ' TO ' + QUOTENAME(@Data File, '''') + ', MOVE ' + QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogF ile, '''') + ', FILE = ' + CONVERT(varchar , @File) EXEC (@query) END GO
Here is what I tried for a stored procedure:
CREATE PROCEDURE sp_CreateNewCom panyDB
-- the original database (use 'SET @DB = NULL' to disable backup) @DB varchar(200) =NULL, --SET @DB = 'Blank'
-- the backup filename @BackupFile varchar(2000)=N ULL, --SET @BackupFile = 'C:\Program Files\Microsoft SQL Server\MSSQL\BA CKUP\backup4'
-- the new database name @TestDB varchar(200)=NU LL, --SET @TestDB = 'D111111111'
-- the new database files without .mdf/.ldf @RestoreFile varchar(2000)=N ULL --SET @RestoreFile = 'C:\Program Files\Microsoft SQL Server\MSSQL\BA CKUP\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(@Back upFile, '''') 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(@Back upFile , '''')
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(@Back upFile, '''') + ' WITH MOVE ' + QUOTENAME(@Data , '''') + ' TO ' + QUOTENAME(@Data File, '''') + ', MOVE ' + QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogF ile, '''') + ', FILE = ' + CONVERT(varchar , @File) EXEC (@query) END GO
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: berthelot samuel |
last post by:
Hi everyone,
I am currently trying to write a report based on a View of SQL Server.
Basically, I have 3 tables : Hardware, SoftwareInstalled and Software
with SoftwareInstalled that keeps track of all the software installed
on each piece of hardware by referencing the primary keys of each
table.
So now, I have a request that retrieve information from those 3 tables
giving a list of all the hardware with their details + the software...
|
by: Chandra Mohan |
last post by:
Hi,
We need help on following things,
1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.
2. Is it possible to call a .exe file in SQL server like following
code in ORACLE
create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
|
by: trialproduct2004 |
last post by:
Hi all,
i am having application which is using stored procedure written in sql
server.
I want to develop multithreaded applicaton.
The store procedure is returing list of rows of table.
what i want to ask is, if i tried to call same stored procedure in
different thread , is it possible or it will created problem in
multithreaded application.
|
by: Tarun Mistry |
last post by:
Hi all, I have posted this in both the c# and asp.net groups as it applies
to both (apologies if it breaks some group rules).
I am making a web app in asp.net using c#. This is the first fully OO
application I will be making, also my first .NET application, so im looking
for any help and guidance.
Ok, my problems are todo with object and database abstraction, what should i
do.
| |
by: deepdata |
last post by:
Hi,
I am trying to create a function which returns a scalar data. My
function tries to insert a row in a table as well.
/**
CREATE function getManufacturerId
(
manufacturer VARCHAR(128)
)
|
by: Siv |
last post by:
Hi,
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason for this being to stop
the user thinking the application has frozen when in fact it is just waiting
for a long SP to complete. Another reason for doing it like this is that I
also have had a problem in the past where the SP takes longer than the...
|
by: Darth Ferret |
last post by:
This thing is about to drive me crazy. I have about 50 queries in the AS400
that I need to put on a menu. Once I conquer this I have a bunch more rpg
reports that I need to pass a date to. In the AS400 I have a stored
procedure (SPRUNQRY) that runs the RUNQRY command with the name of the query
as a parameter. In the AS400 I would type "RUNQRY SUNTR401A" on a command
line to run this query. My connection is opening, and I can run some...
|
by: SOI_0152 |
last post by:
Hi all!
Happy New Year 2008. Il hope it will bring you love and happyness
I'm new on this forum.
I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c.
Everything works fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |