rman backup in sql server

do any one know if rman can perform backup on Sql server and what is the steps involved?
ThanksHi, and welcome to TSDN!

I'm moving this thread to the SQL Server / MSSQL forums, where our SQL Server experts are more likely to find it.
Please try to post your questions in a relevant forum.

Moderator

Rights needed to restore DB

I have an SQL 2005 box in an academic setting. Students log-in using SQL
server authentication. They can create, modify and backup their DBs'
When using the restore wizard and specifying a file location where the
backup is located they get an error.
c:\ProgramFiles\...\...\...\Backup
Cannot access the specified path or file on the server. Verify that you have
the necessary security privileges and that the path or file exists
If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate dialog box.
The back-ups are being created and can be restored by the Instructor (with
full system rights)
What am I missing here? students can write but not read there own files.
Any help is appreciated.
Todd>I have an SQL 2005 box in an academic setting. Students log-in using SQL
> server authentication. They can create, modify and backup their DBs'
> When using the restore wizard and specifying a file location where the
> backup is located they get an error.
> c:\ProgramFiles\...\...\...\Backup
> Cannot access the specified path or file on the server. Verify that you
> have
> the necessary security privileges and that the path or file exists
> If you know that the service account can access a specific file, type in
> the
> full path for the file in the File Name control in the Locate dialog box.
> The back-ups are being created and can be restored by the Instructor (with
> full system rights)
> What am I missing here? students can write but not read there own files.
BACKUP DATABASE permission is not enough for restoring. If the database
being restored does not exist, the user must have CREATE DATABASE
permissions to be able to execute RESTORE. If the database exists, RESTORE
permissions default to members of the sysadmin and dbcreator fixed server
roles and the owner (dbo) of the database.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||The users are a part of the dbcreator fixed role. Do they need spcific right
s
to the folder where the backups are stored or does a sytem account manage
that piece.
If so which account?
"Dejan Sarka" wrote:

> BACKUP DATABASE permission is not enough for restoring. If the database
> being restored does not exist, the user must have CREATE DATABASE
> permissions to be able to execute RESTORE. If the database exists, RESTORE
> permissions default to members of the sysadmin and dbcreator fixed server
> roles and the owner (dbo) of the database.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||> The users are a part of the dbcreator fixed role. Do they need spcific
> rights
> to the folder where the backups are stored or does a sytem account manage
> that piece.
> If so which account?
If you are using T-SQL Restore command from SSMS, then it is SQL Server
account. Check whether it can reach the folder. For example, you could use a
mapped drive, and the SQL Server account does not know anything about the
mapping you do under your user account. In addition, LocalSystem account, if
your server uses it, can not go beyond local computer - it can not reach
shared folders on the network. Try to login with servers account and read
the backup files.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Dejan,
We are using the backup/ restore wizard and trying to reach the default
SQL back-up folder on the local drive. The Administrator and Instructor
accounts can reach the folder and restore a file, but they have sysadmin
role. Students with dbcreator role can backup but not restore.
The SQLUser account in widows has full control rights to the folder.
"Dejan Sarka" wrote:

> If you are using T-SQL Restore command from SSMS, then it is SQL Server
> account. Check whether it can reach the folder. For example, you could use
a
> mapped drive, and the SQL Server account does not know anything about the
> mapping you do under your user account. In addition, LocalSystem account,
if
> your server uses it, can not go beyond local computer - it can not reach
> shared folders on the network. Try to login with servers account and read
> the backup files.
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||> We are using the backup/ restore wizard and trying to reach the default
> SQL back-up folder on the local drive. The Administrator and Instructor
> accounts can reach the folder and restore a file, but they have sysadmin
> role. Students with dbcreator role can backup but not restore.
So, what is the exact error message?
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Here's the message:
"C:\Program Files\Microsoft SQL Server\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have
the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate Dialog box."
The File Name control is greyed out and doesn't allow students to type. Also
trying to Add a file path fails.
"Dejan Sarka" wrote:

> So, what is the exact error message?
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||Have you tried to login with the service account and access the path? I know
it does not sound logical that SQL Server would not have access to its own
path, but anway...
In addition, could you script your student login / user / permissions, in
order I can check it?
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"TLuebke" <TLuebke@.discussions.microsoft.com> wrote in message
news:78358925-255D-4EB7-B5D5-8448256078E3@.microsoft.com...[vbcol=seagreen]
> Here's the message:
> "C:\Program Files\Microsoft SQL Server\MSSQL\Backup
> Cannot access the specified path or file on the server. Verify that you
> have
> the necessary security privileges and that the path or file exists.
> If you know that the service account can access a specific file, type in
> the
> full path for the file in the File Name control in the Locate Dialog box."
> The File Name control is greyed out and doesn't allow students to type.
> Also
> trying to Add a file path fails.
>
> "Dejan Sarka" wrote:
>

right backup example

Please give me a good way to backup databases
on my MS SQL 2000 Server.
I've created a job in
"Enterprise Manager"..."SQL Server Agent"->Jobs
"BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
backup', NOSKIP , STATS = 10, NOFORMAT"
and they creates the backup.
However, I don't like that BUFF.bkp grows too much.
How can I (probably using a third-party utility):
- create backups with file names with current date, for example
BUFF-DD_MM_YYYY.bkp,
- automatically delete too old backups
- move them on a network drive after creating
(I remember that SQL Server doesn't allow to backup directly to a network
drive).
You can use the maintenance wizard to handle the full backup tasks with all
the features you asked for. BOL explains it better than I do.
You can also back up to a network share, you just have to follow a simple
procedure.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Smirnov" <smbsmb@.mail.ru> wrote in message
news:urqrZOl1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Please give me a good way to backup databases
> on my MS SQL 2000 Server.
> I've created a job in
> "Enterprise Manager"..."SQL Server Agent"->Jobs
> "BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
> backup', NOSKIP , STATS = 10, NOFORMAT"
> and they creates the backup.
> However, I don't like that BUFF.bkp grows too much.
> How can I (probably using a third-party utility):
> - create backups with file names with current date, for example
> BUFF-DD_MM_YYYY.bkp,
> - automatically delete too old backups
> - move them on a network drive after creating
> (I remember that SQL Server doesn't allow to backup directly to a
network
> drive).
>
>
|||Our product, MiniSQLBackup, does all that you require, besides giving you
the option to compress and encrypt your backups. An example of how the
backup command would look like in MiniSQLBackup:
BACKUP DATABASE [BUFF] TO DISK = '<AUTO> WITH NAME = 'BUFF backup',
COPYTO = '<remote folder 1>', COPYTO = '<remote folder 2>'
where <AUTO> represents a naming convention you can set up using date time
elements, and COPYTO are extensions to copy the backup file to other folders
upon completion of the backup. If you're interested, MiniSQLBackup can also
perform mirrored backups i.e. simultaneous backups to two or more disks.
Price is USD99/- per named server, free for the Lite edition.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free.

right backup example

Please give me a good way to backup databases
on my MS SQL 2000 Server.
I've created a job in
"Enterprise Manager"..."SQL Server Agent"->Jobs
"BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
backup', NOSKIP , STATS = 10, NOFORMAT"
and they creates the backup.
However, I don't like that BUFF.bkp grows too much.
How can I (probably using a third-party utility):
- create backups with file names with current date, for example
BUFF-DD_MM_YYYY.bkp,
- automatically delete too old backups
- move them on a network drive after creating
(I remember that SQL Server doesn't allow to backup directly to a network
drive).You can use the maintenance wizard to handle the full backup tasks with all
the features you asked for. BOL explains it better than I do.
You can also back up to a network share, you just have to follow a simple
procedure.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Smirnov" <smbsmb@.mail.ru> wrote in message
news:urqrZOl1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Please give me a good way to backup databases
> on my MS SQL 2000 Server.
> I've created a job in
> "Enterprise Manager"..."SQL Server Agent"->Jobs
> "BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
> backup', NOSKIP , STATS = 10, NOFORMAT"
> and they creates the backup.
> However, I don't like that BUFF.bkp grows too much.
> How can I (probably using a third-party utility):
> - create backups with file names with current date, for example
> BUFF-DD_MM_YYYY.bkp,
> - automatically delete too old backups
> - move them on a network drive after creating
> (I remember that SQL Server doesn't allow to backup directly to a
network
> drive).
>
>|||Our product, MiniSQLBackup, does all that you require, besides giving you
the option to compress and encrypt your backups. An example of how the
backup command would look like in MiniSQLBackup:
BACKUP DATABASE [BUFF] TO DISK = '<AUTO> WITH NAME = 'BUFF backup',
COPYTO = '<remote folder 1>', COPYTO = '<remote folder 2>'
where <AUTO> represents a naming convention you can set up using date time
elements, and COPYTO are extensions to copy the backup file to other folders
upon completion of the backup. If you're interested, MiniSQLBackup can also
perform mirrored backups i.e. simultaneous backups to two or more disks.
Price is USD99/- per named server, free for the Lite edition.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free.

right backup example

Please give me a good way to backup databases
on my MS SQL 2000 Server.
I've created a job in
"Enterprise Manager"..."SQL Server Agent"->Jobs
"BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
backup', NOSKIP , STATS = 10, NOFORMAT"
and they creates the backup.
However, I don't like that BUFF.bkp grows too much.
How can I (probably using a third-party utility):
- create backups with file names with current date, for example
BUFF-DD_MM_YYYY.bkp,
- automatically delete too old backups
- move them on a network drive after creating
(I remember that SQL Server doesn't allow to backup directly to a network
drive).You can use the maintenance wizard to handle the full backup tasks with all
the features you asked for. BOL explains it better than I do.
You can also back up to a network share, you just have to follow a simple
procedure.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Smirnov" <smbsmb@.mail.ru> wrote in message
news:urqrZOl1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Please give me a good way to backup databases
> on my MS SQL 2000 Server.
> I've created a job in
> "Enterprise Manager"..."SQL Server Agent"->Jobs
> "BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
> backup', NOSKIP , STATS = 10, NOFORMAT"
> and they creates the backup.
> However, I don't like that BUFF.bkp grows too much.
> How can I (probably using a third-party utility):
> - create backups with file names with current date, for example
> BUFF-DD_MM_YYYY.bkp,
> - automatically delete too old backups
> - move them on a network drive after creating
> (I remember that SQL Server doesn't allow to backup directly to a
network
> drive).
>
>|||Our product, MiniSQLBackup, does all that you require, besides giving you
the option to compress and encrypt your backups. An example of how the
backup command would look like in MiniSQLBackup:
BACKUP DATABASE [BUFF] TO DISK = '<AUTO> WITH NAME = 'BUFF backup',
COPYTO = '<remote folder 1>', COPYTO = '<remote folder 2>'
where <AUTO> represents a naming convention you can set up using date time
elements, and COPYTO are extensions to copy the backup file to other folders
upon completion of the backup. If you're interested, MiniSQLBackup can also
perform mirrored backups i.e. simultaneous backups to two or more disks.
Price is USD99/- per named server, free for the Lite edition.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free.

Revover MS SQL 2000 from Log file (last 4 hours)

Sorry in advance for the stupid question but I am not a technical expert at all!!!

I made a backup from a SQL-Server 2000 DB 3 days ago. Today data have been deleted from the system and they should be recovered. I have a full recovery model.

I tried to restore the DB but before I made a new backup (Just in case) I hope that was no error??

Is there a way to restore the DB from the log file but just restore the last 4 hours?

Thanks
AIf you maintain Tlog backups and rEview information on books online about restoring Tlogs upto a point of time.

revised attach files to new box

sql2k sp3
Box crashed. Uninstalled/ reinstalled sql. I have a backup
of the master db but not for the users db's. I will
restore from the master backup. But after that, is there a
way to attach the mdf's that werent cleanly detached?
TIA, ChrisRTry first using sp_attach_db. This requires both mdf, ndf and ldf file(s).
If that doesn't cut it, you can *try* sp_attach_single_file_db, which doesn't require ldf. With much luck, SQL
Server can create the ldf file for you.
Above two are only documented to work if you cleanly detached the databases first. It might work for you even
if you didn't. If it doesn't, open a case with MS support and see if they have any suggestions (which will
probably result in possible data-loss and/or physical and/or logical inconsistencies).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message news:ca3f01c48a14$a0052fd0$a601280a@.phx.gbl...
> sql2k sp3
> Box crashed. Uninstalled/ reinstalled sql. I have a backup
> of the master db but not for the users db's. I will
> restore from the master backup. But after that, is there a
> way to attach the mdf's that werent cleanly detached?
> TIA, ChrisR

Returning from single user mode

I'm using a modified backup plan to perform nightly backups. In the
integrity check I've checked the Attempt to repair (I know some don't
recommend it). To prepare for that stage of the backup I kill the user
processes that are still active when the backup begins. However, when the
backup finishes the databases are being left in single user mode. How can I
return the databases to multi user mode? I've tried a procedure like so:
create procedure setmultiuser
as
declare @.dbname varchar(128),
@.cmd varchar(128)
declare dbname_crsr cursor for
select name from sysdatabases
where sid <> 0x01
open dbname_crsr
fetch dbname_crsr into @.dbname
while (@.@.fetch_status <> -1)
begin
set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
EXEC ('sp_dboption ' + @.cmd)
fetch dbname_crsr into @.dbname
end
close dbname_crsr
deallocate dbname_crsr
But, the accursed EXEC command refuses to allow me to understand it's
syntax.
Any help would be greatly appreciated.I get:
Line 16: Incorrect syntax near 'single'.
The actual line reads:
set @.cmd = @.dbname + ', 'single user', 'FALSE''
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
Throw a print in there to see where the wheels may be coming off...
PRINT ('sp_dboption ' + @.cmd)
How many databases exist on your server? Would it be easier to simply
hardcode the appropriate call within a separate step of the job?
--
Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> I'm using a modified backup plan to perform nightly backups. In the
> integrity check I've checked the Attempt to repair (I know some don't
> recommend it). To prepare for that stage of the backup I kill the user
> processes that are still active when the backup begins. However, when the
> backup finishes the databases are being left in single user mode. How can
I
> return the databases to multi user mode? I've tried a procedure like so:
> create procedure setmultiuser
> as
> declare @.dbname varchar(128),
> @.cmd varchar(128)
> declare dbname_crsr cursor for
> select name from sysdatabases
> where sid <> 0x01
> open dbname_crsr
> fetch dbname_crsr into @.dbname
> while (@.@.fetch_status <> -1)
> begin
> set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
> EXEC ('sp_dboption ' + @.cmd)
> fetch dbname_crsr into @.dbname
> end
> close dbname_crsr
> deallocate dbname_crsr
> But, the accursed EXEC command refuses to allow me to understand it's
> syntax.
> Any help would be greatly appreciated.
>|||Does this help?
declare @.cmd varchar(100), @.dbname char(5)
set @.cmd =3D 'thecmd ' set @.dbname =3D 'thedb' set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
print @.cmd
-- Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message =news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> I get:
> > Line 16: Incorrect syntax near 'single'.
> > The actual line reads:
> set @.cmd =3D @.dbname + ', 'single user', 'FALSE''
> > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote =in
> message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Throw a print in there to see where the wheels may be coming off...
> > PRINT ('sp_dboption ' + @.cmd)
> > How many databases exist on your server? Would it be easier to simply
> hardcode the appropriate call within a separate step of the job?
> > -- > Keith
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I'm using a modified backup plan to perform nightly backups. In the
> > integrity check I've checked the Attempt to repair (I know some =don't
> > recommend it). To prepare for that stage of the backup I kill the =user
> > processes that are still active when the backup begins. However, =when the
> > backup finishes the databases are being left in single user mode. =How can
> I
> > return the databases to multi user mode? I've tried a procedure =like so:
> >
> > create procedure setmultiuser
> > as
> >
> > declare @.dbname varchar(128),
> > @.cmd varchar(128)
> >
> > declare dbname_crsr cursor for
> > select name from sysdatabases
> > where sid <> 0x01
> >
> > open dbname_crsr
> > fetch dbname_crsr into @.dbname
> >
> > while (@.@.fetch_status <> -1)
> > begin
> > set @.cmd =3D @.dbname + ', ' + ''single user', 'FALSE''
> > EXEC ('sp_dboption ' + @.cmd)
> >
> > fetch dbname_crsr into @.dbname
> > end
> >
> > close dbname_crsr
> > deallocate dbname_crsr
> >
> > But, the accursed EXEC command refuses to allow me to understand =it's
> > syntax.
> >
> > Any help would be greatly appreciated.
> >
> >
> >|||That was real close. This is what finally worked:
set @.cmd = @.dbname + ''', ''single user'', ''FALSE'''
exec ('sp_dboption ''' + @.cmd)
But you put me on the right track. Thanks a billion!!!
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:ug4IM1bZDHA.2236@.TK2MSFTNGP10.phx.gbl...
Does this help?
declare @.cmd varchar(100), @.dbname char(5)
set @.cmd = 'thecmd '
set @.dbname = 'thedb'
set @.cmd = @.dbname + ''', ''single user'', ''FALSE'''
print @.cmd
--
Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> I get:
> Line 16: Incorrect syntax near 'single'.
> The actual line reads:
> set @.cmd = @.dbname + ', 'single user', 'FALSE''
> "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
> message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Throw a print in there to see where the wheels may be coming off...
> PRINT ('sp_dboption ' + @.cmd)
> How many databases exist on your server? Would it be easier to simply
> hardcode the appropriate call within a separate step of the job?
> --
> Keith
> "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > I'm using a modified backup plan to perform nightly backups. In the
> > integrity check I've checked the Attempt to repair (I know some don't
> > recommend it). To prepare for that stage of the backup I kill the user
> > processes that are still active when the backup begins. However, when
the
> > backup finishes the databases are being left in single user mode. How
can
> I
> > return the databases to multi user mode? I've tried a procedure like
so:
> >
> > create procedure setmultiuser
> > as
> >
> > declare @.dbname varchar(128),
> > @.cmd varchar(128)
> >
> > declare dbname_crsr cursor for
> > select name from sysdatabases
> > where sid <> 0x01
> >
> > open dbname_crsr
> > fetch dbname_crsr into @.dbname
> >
> > while (@.@.fetch_status <> -1)
> > begin
> > set @.cmd = @.dbname + ', ' + ''single user', 'FALSE''
> > EXEC ('sp_dboption ' + @.cmd)
> >
> > fetch dbname_crsr into @.dbname
> > end
> >
> > close dbname_crsr
> > deallocate dbname_crsr
> >
> > But, the accursed EXEC command refuses to allow me to understand it's
> > syntax.
> >
> > Any help would be greatly appreciated.
> >
> >
>|||Learning is half the fun. Applying what you learn is the other half.
-- Keith
"Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message =news:Opv#VOcZDHA.2020@.TK2MSFTNGP10.phx.gbl...
> That was real close. This is what finally worked:
> > set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
> exec ('sp_dboption ''' + @.cmd)
> > But you put me on the right track. Thanks a billion!!!
> > > > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote =in
> message news:ug4IM1bZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> Does this help?
> > > declare @.cmd varchar(100), @.dbname char(5)
> set @.cmd =3D 'thecmd '
> set @.dbname =3D 'thedb'
> set @.cmd =3D @.dbname + ''', ''single user'', ''FALSE'''
> print @.cmd
> > -- > Keith
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> news:#zHrdrbZDHA.1832@.TK2MSFTNGP10.phx.gbl...
> > I get:
> >
> > Line 16: Incorrect syntax near 'single'.
> >
> > The actual line reads:
> > set @.cmd =3D @.dbname + ', 'single user', 'FALSE''
> >
> > "Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> =wrote in
> > message news:OqtVkLbZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> > Throw a print in there to see where the wheels may be coming off...
> >
> > PRINT ('sp_dboption ' + @.cmd)
> >
> > How many databases exist on your server? Would it be easier to =simply
> > hardcode the appropriate call within a separate step of the job?
> >
> > -- > > Keith
> >
> > "Paul Nations" <pauln@.adhe.arknet.nospam.edu> wrote in message
> > news:OxzwW$aZDHA.2032@.TK2MSFTNGP10.phx.gbl...
> > > I'm using a modified backup plan to perform nightly backups. In =the
> > > integrity check I've checked the Attempt to repair (I know some =don't
> > > recommend it). To prepare for that stage of the backup I kill the =user
> > > processes that are still active when the backup begins. However, =when
> the
> > > backup finishes the databases are being left in single user mode. =How
> can
> > I
> > > return the databases to multi user mode? I've tried a procedure =like
> so:
> > >
> > > create procedure setmultiuser
> > > as
> > >
> > > declare @.dbname varchar(128),
> > > @.cmd varchar(128)
> > >
> > > declare dbname_crsr cursor for
> > > select name from sysdatabases
> > > where sid <> 0x01
> > >
> > > open dbname_crsr
> > > fetch dbname_crsr into @.dbname
> > >
> > > while (@.@.fetch_status <> -1)
> > > begin
> > > set @.cmd =3D @.dbname + ', ' + ''single user', 'FALSE''
> > > EXEC ('sp_dboption ' + @.cmd)
> > >
> > > fetch dbname_crsr into @.dbname
> > > end
> > >
> > > close dbname_crsr
> > > deallocate dbname_crsr
> > >
> > > But, the accursed EXEC command refuses to allow me to understand =it's
> > > syntax.
> > >
> > > Any help would be greatly appreciated.
> > >
> > >
> >
> >
> >