Restoring the database using stored procedure

hey guyz...
i used this stored procedure code my system.. but it crashes saying "exclusive access could not be obtained becuase the database is in use"

i have included the stored procedure below. is the stored procedure correct?
if it is.. how can i sovle this problem?

CREATE Procedure spRestoreDatabase
@.Path VARCHAR(100)
AS
Restore Database Test From Disk = @.Path
GOA database restore can only be done when you've got exclusive use of the database. No other users can be using the database besides the one doing the restore.

The procedure can't be in the database, because then the spid trying to restore the datbase would need to be executing code in the database which would prevent the restore.

An SQL Agent job would be a better choice than a stored procedure. You may need to think about using ALTER DATABASE to force the other users out of the database, but think that through carefully before you try to use it because it can cause other problems.

-PatP|||erm... so what can i do now?|||Put your stored procedure in a different database and run it from there.

Gotta ask why you need a stored proc to restore your database in the first place. Surely this isn't going to be an automated task?|||erm... so what can i do now?As blindman pointed out, your first step ought to be to rethink what you are doing. Try to determine if there is some better way than an automated restore.

If you really, truly need to automate the restore, I'd do it as a SQL Agent Job myself. You can actually get pretty tricky with jobs, and do nearly anything you can do with a stored procedure, plus a whole lot more.

Before you get too busy coding, think hard about what you are doing, why you are doing it, and whether there are any alternatives. You are headed into the area that cartographers of olde used to label "Here be dragons"

-PatPsql

restoring from a backup file to a new database

I am trying to restore from a backup file to a new database with SQL2000. Here is my C# code:

SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.LoginSecure = true;
srv.Connect("servername","","");

//create a restore class instance
SQLDMO.Restore res = new SQLDMO.RestoreClass();

//set the backup device = files property ( easy way )
res.Devices = res.Files;
//set the files property to the File Name text box
res.Files = @."\\server\backupfile.bak";
//set the database to the chosen database
res.Database = "databasename" + "-restored";

// Restore the database
res.ReplaceDatabase = false;
res.SQLRestore(srv);

This code gives me an error basically telling me it can't restore as the ".mdf" and ".ldf" files are already in use. I think I need to specify new names for these like I have I have specified a new name for the database. (e.g. appended "-restored" to it.) but I am not sure how. Any help would be greatly appreciated.

Sorry, I don't know DMO well enough to answer your question, but here's an example using SMO and VB that may help. After setting the Database name, insert these lines of code, before the Restore:

Dim alRSFile As New RelocateFile
Dim alRSLog As New RelocateFile
alRSFile.LogicalFileName = "AdventureWorks_Data"
alRSFile.PhysicalFileName = "d:\MSSQL\Data\AdWorks.mdf"
alRSLog.LogicalFileName = "AdventureWorks_Log"
alRSLog.PhysicalFileName = "d:\MSSQL\Data\AdWorks.ldf"
res.RelocateFiles.Add(alRSFile)
res.RelocateFiles.Add(alRSLog)

Allen

|||

I believe SQLDMO is more easy to use and comprehensive. You already mentioned the problem.

When you take backup, and then restore it in a new database, the name of the .mdf file for the new database is same as was backup containing.

So if a database with the same .mdf file name is there conflict arises. Even if you dont have it there it would work fine for the first time but for 2nd time you need to change the physical file name as we do in sql commands mentioned below....

RESTORE DATABASE db3
FROM DISK = 'c:\me.bak' \\ Original name of the .mdf file was me.mdf.
WITH MOVE 'me' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\db3.mdf'

RestoreClass of SQLDMO library provided these features...but how can we specify the MOVE attribute here....still have no clues about it ..If any one has any idea...post the reply as early as possible...

|||

The code I included in my last post effectively does the MOVE operation, by setting the properties of the File objects before the restore. While you may find DMO easier to use, it's probably more due to familiarity than anything else. Everything SSMS does is done through SMO, so there can't be an argument that it's not comprehensive, and the features that are new to SQL Server 2005 aren't available in DMO.

Most importantly, though, is that there are a variety of ways to accomplish anything, so if DMO suits your needs, then by all means use it.

restoring from a backup file to a new database

I am trying to restore from a backup file to a new database with SQL2000. Here is my C# code:

SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.LoginSecure = true;
srv.Connect("servername","","");

//create a restore class instance
SQLDMO.Restore res = new SQLDMO.RestoreClass();

//set the backup device = files property ( easy way )
res.Devices = res.Files;
//set the files property to the File Name text box
res.Files = @."\\server\backupfile.bak";
//set the database to the chosen database
res.Database = "databasename" + "-restored";

// Restore the database
res.ReplaceDatabase = false;
res.SQLRestore(srv);

This code gives me an error basically telling me it can't restore as the ".mdf" and ".ldf" files are already in use. I think I need to specify new names for these like I have I have specified a new name for the database. (e.g. appended "-restored" to it.) but I am not sure how. Any help would be greatly appreciated.

Sorry, I don't know DMO well enough to answer your question, but here's an example using SMO and VB that may help. After setting the Database name, insert these lines of code, before the Restore:

Dim alRSFile As New RelocateFile
Dim alRSLog As New RelocateFile
alRSFile.LogicalFileName = "AdventureWorks_Data"
alRSFile.PhysicalFileName = "d:\MSSQL\Data\AdWorks.mdf"
alRSLog.LogicalFileName = "AdventureWorks_Log"
alRSLog.PhysicalFileName = "d:\MSSQL\Data\AdWorks.ldf"
res.RelocateFiles.Add(alRSFile)
res.RelocateFiles.Add(alRSLog)

Allen

|||

I believe SQLDMO is more easy to use and comprehensive. You already mentioned the problem.

When you take backup, and then restore it in a new database, the name of the .mdf file for the new database is same as was backup containing.

So if a database with the same .mdf file name is there conflict arises. Even if you dont have it there it would work fine for the first time but for 2nd time you need to change the physical file name as we do in sql commands mentioned below....

RESTORE DATABASE db3
FROM DISK = 'c:\me.bak' \\ Original name of the .mdf file was me.mdf.
WITH MOVE 'me' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\db3.mdf'

RestoreClass of SQLDMO library provided these features...but how can we specify the MOVE attribute here....still have no clues about it ..If any one has any idea...post the reply as early as possible...

|||

The code I included in my last post effectively does the MOVE operation, by setting the properties of the File objects before the restore. While you may find DMO easier to use, it's probably more due to familiarity than anything else. Everything SSMS does is done through SMO, so there can't be an argument that it's not comprehensive, and the features that are new to SQL Server 2005 aren't available in DMO.

Most importantly, though, is that there are a variety of ways to accomplish anything, so if DMO suits your needs, then by all means use it.

restoring from .bak file

Tried restoring a database from .bak file through Enterprise Manager and also by usng the following code :

RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH
REPLACE,
RECOVERY,
MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'

But, I get an error :
Server: Msg 3156, Level 16, State 2, Line 1
The file 'd:\mssql7\data\apm.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.

I have got the two filenames i.e."ap0data" and "ap0log" by using the command "restore fileslistonly from disk = c:\dba.bak".

Can anyone help me to do the things rigtly ?This should work !!!

RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'|||Thanks for your help.

I tried the code, but got error:

Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing 'apm' database.

I don't have the original database. I have just created a blank database and I am trying to restore.|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'

RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2

Run these two and get back with the results|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'

RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2

Server: Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 2 on device 'c:\windows\desktop\cmpbk.BAK'.|||Try this :

RESTORE DATABASE DBB FROM DISK = N'c:\DBA.BAK' WITH FILE = 1,
RECOVERY , REPLACE ,
MOVE N'ap0data' TO N'c:\mssql\data\apm_data.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apm_log.ldf'|||Tried :

Server: Msg 3156, Level 16, State 2, Line 1
The file 'c:\mssql\data\apm_data.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.|||sp_helpdb dbb

?|||name = apm
db_size = 2.00mb
owner = sa
bdid = 8
status = select into/bulkocopy, trun. log on chkpt

name = apm_data
fileid = 1
filename = c:\mssql7\data\apm_data.mdf
filegroup = primary
maxsize = unlimited
growth = 10%
usage = data only

name = apm_log
fileid = 2
filename = c:\mssql7\data\apm_log.ldf
filegroup = null
maxsize = unlimited
growth = 10%
usage = log only|||am clutching at straws now

RESTORE DATABASE TestDB FROM DISK = N'c:\DBA.BAK' WITH
MOVE N'ap0data' TO N'c:\mssql\data\apb1_data1.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apb1_log1.ldf'

i mean try restoring to a completely new database ... let the restore statements create the db|||Hey, It worked.

Thank you very much, Sir|||now you can use the sp_renamedb command to change it to the name you want.

Restoring Databases with Encrypted Data to Another Server

I use the following code to create encrypted data in a table called TEST in
a
SQL Server 2005 database.
-- Use the AdventureWorks database
USE AdventureWorks;
-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@.ssw0rd';
-- Create a Temp Table
CREATE TABLE Person.#Temp
(ContactID INT PRIMARY KEY,
FirstName NVARCHAR(200),
MiddleName NVARCHAR(200),
LastName NVARCHAR(200),
eFirstName VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName VARBINARY(200));
-- Create a Test Certificate
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'Adventureworks Test Certificate',
EXPIRY_DATE = '10/31/2009';
-- Create a Symmetric Key
CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate;
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
-- EncryptByKey demonstration encrypts 100 names from the Person.Contact tab
le
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID,
EncryptByKey(Key_GUID('TestSymmetricKey'
), FirstName),
EncryptByKey(Key_GUID('TestSymmetricKey'
), MiddleName),
EncryptByKey(Key_GUID('TestSymmetricKey'
), LastName)
FROM Person.Contact
WHERE ContactID <= 100;
-- DecryptByKey demonstration decrypts the previously encrypted data
UPDATE Person.#Temp
SET FirstName = DecryptByKey(eFirstName),
MiddleName = DecryptByKey(eMiddleName),
LastName = DecryptByKey(eLastName);
-- View the results
SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM Person.#Temp
--create physical table
select * into TEST from person.#temp
--view results from physical table
SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM TEST
----
I backup the database and restore to another server. After the restore
finishes, I run the following commands in the restored database:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@.ssw0rd';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
When I run the following SELECT statement to view my data in the restored
database, I only get NULLs.
SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
FROM TEST
From what I have researched, this is all you have to do. I must be missing
a step. Can anyone tell me what I have forgotten or provide me a list of
steps to execute when I am restoring a database with encrypted data to
another server'
Thanks,
DBI think you forgot to open the key - you were doing this step on the
original server:
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"DB" <DB@.discussions.microsoft.com> wrote in message
news:8511E6F6-0912-4274-9CA0-605295981376@.microsoft.com...
>I use the following code to create encrypted data in a table called TEST in
>a
> SQL Server 2005 database.
> -- Use the AdventureWorks database
> USE AdventureWorks;
> -- Create a Database Master Key
> CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@.ssw0rd';
> -- Create a Temp Table
> CREATE TABLE Person.#Temp
> (ContactID INT PRIMARY KEY,
> FirstName NVARCHAR(200),
> MiddleName NVARCHAR(200),
> LastName NVARCHAR(200),
> eFirstName VARBINARY(200),
> eMiddleName VARBINARY(200),
> eLastName VARBINARY(200));
> -- Create a Test Certificate
> CREATE CERTIFICATE TestCertificate
> WITH SUBJECT = 'Adventureworks Test Certificate',
> EXPIRY_DATE = '10/31/2009';
> -- Create a Symmetric Key
> CREATE SYMMETRIC KEY TestSymmetricKey
> WITH ALGORITHM = TRIPLE_DES
> ENCRYPTION BY CERTIFICATE TestCertificate;
> OPEN SYMMETRIC KEY TestSymmetricKey
> DECRYPTION BY CERTIFICATE TestCertificate;
> -- EncryptByKey demonstration encrypts 100 names from the Person.Contact
> table
> INSERT
> INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
> SELECT ContactID,
> EncryptByKey(Key_GUID('TestSymmetricKey'
), FirstName),
> EncryptByKey(Key_GUID('TestSymmetricKey'
), MiddleName),
> EncryptByKey(Key_GUID('TestSymmetricKey'
), LastName)
> FROM Person.Contact
> WHERE ContactID <= 100;
> -- DecryptByKey demonstration decrypts the previously encrypted data
> UPDATE Person.#Temp
> SET FirstName = DecryptByKey(eFirstName),
> MiddleName = DecryptByKey(eMiddleName),
> LastName = DecryptByKey(eLastName);
> -- View the results
> SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM Person.#Temp
> --create physical table
> select * into TEST from person.#temp
> --view results from physical table
> SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM TEST
> ----
> I backup the database and restore to another server. After the restore
> finishes, I run the following commands in the restored database:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@.ssw0rd';
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
> When I run the following SELECT statement to view my data in the restored
> database, I only get NULLs.
> SELECT convert(nvarchar(1000),DecryptByKey(eFir
stName)) as FName,
> convert(nvarchar(1000),DecryptByKey(eMid
dleName)) AS MName,
> convert(nvarchar(1000),DecryptByKey(eLas
tName)) AS LName
> FROM TEST
> From what I have researched, this is all you have to do. I must be
> missing
> a step. Can anyone tell me what I have forgotten or provide me a list of
> steps to execute when I am restoring a database with encrypted data to
> another server'
> Thanks,
> --
> DB

Restoring databases

You don't actually need to restore any of you system
databases unless you have modified or added code in them
(which is a bit dangeous anyway).
In your text you said the pubs database, thats not a
system database but a user database, the answer is it
depends if you have changed it.
BTW the best way of doing it is not to restore them, but
to detach the database and copy over the log and data file
onto the new server then re-attach them. It will save you
a bit of time ;)
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain

>--Original Message--
>This weekend I have to restore a backup from one SQL
server 2000 machine to
>another. I am doing this simply because we are updating
the hardware, so
>when the new machine has been built it will have the same
network name as
>the original box. The current box is running Windows 2000
Server with SQL
>2000 sp2, the new box will be Windows 2003 and SQL 2000
sp3a. There are 8
>small databases totaling 1.5Gb. My question is do I need
to restore any of
>the SQL databases? msdb? pubs? I normally do this for SAP
databases where I
>only have to restore the SAP database and nothing else.
Can anbody enlighten
>me?
>Gav
>
>.
>
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:2f1d01c520d6$32bc7970$a501280a@.phx.gbl...[vbcol=seagreen]
> You don't actually need to restore any of you system
> databases unless you have modified or added code in them
> (which is a bit dangeous anyway).
> In your text you said the pubs database, thats not a
> system database but a user database, the answer is it
> depends if you have changed it.
> BTW the best way of doing it is not to restore them, but
> to detach the database and copy over the log and data file
> onto the new server then re-attach them. It will save you
> a bit of time ;)
> Peter
> "Adam and Eve had many advantages but the principal one
> was that they escaped teething."
> Mark Twain
>
> server 2000 machine to
> the hardware, so
> network name as
> Server with SQL
> sp3a. There are 8
> to restore any of
> databases where I
> Can anbody enlighten
What about any users that are defined in Logins? Where are they stored?
Thought about detaching the databases but they are on local storage and both
machines have the same name so without changing server names we cannot have
them both on the network at the same time. Restore takes no time at all so
its probably less hassle.
Just read up on the other databases, doh, didn't realise pub was just a
sample like northwind. :o)
Gav

Restoring databases

You don't actually need to restore any of you system
databases unless you have modified or added code in them
(which is a bit dangeous anyway).
In your text you said the pubs database, thats not a
system database but a user database, the answer is it
depends if you have changed it.
BTW the best way of doing it is not to restore them, but
to detach the database and copy over the log and data file
onto the new server then re-attach them. It will save you
a bit of time ;)
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain

>--Original Message--
>This weekend I have to restore a backup from one SQL
server 2000 machine to
>another. I am doing this simply because we are updating
the hardware, so
>when the new machine has been built it will have the same
network name as
>the original box. The current box is running Windows 2000
Server with SQL
>2000 sp2, the new box will be Windows 2003 and SQL 2000
sp3a. There are 8
>small databases totaling 1.5Gb. My question is do I need
to restore any of
>the SQL databases? msdb? pubs? I normally do this for SAP
databases where I
>only have to restore the SAP database and nothing else.
Can anbody enlighten
>me?
>Gav
>
>.
>"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:2f1d01c520d6$32bc7970$a501280a@.phx.gbl...[vbcol=seagreen]
> You don't actually need to restore any of you system
> databases unless you have modified or added code in them
> (which is a bit dangeous anyway).
> In your text you said the pubs database, thats not a
> system database but a user database, the answer is it
> depends if you have changed it.
> BTW the best way of doing it is not to restore them, but
> to detach the database and copy over the log and data file
> onto the new server then re-attach them. It will save you
> a bit of time ;)
> Peter
> "Adam and Eve had many advantages but the principal one
> was that they escaped teething."
> Mark Twain
>
>
> server 2000 machine to
> the hardware, so
> network name as
> Server with SQL
> sp3a. There are 8
> to restore any of
> databases where I
> Can anbody enlighten
What about any users that are defined in Logins? Where are they stored?
Thought about detaching the databases but they are on local storage and both
machines have the same name so without changing server names we cannot have
them both on the network at the same time. Restore takes no time at all so
its probably less hassle.
Just read up on the other databases, doh, didn't realise pub was just a
sample like northwind. :o)
Gav