Script Syntax Errors

i have new server, win2003, sql2005. trying to restore a .bak file from
sql2000. when excuting the RESTORE DATABASE dbase1 from
disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
displayed.
Executing the query ...
Query (1, 9) Parser: The syntax for 'database' is incorrect.
Execution complete
any ideas?
thanks in advance.
paulWhere do you execute this restore command? The error seems like some client application error...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>i have new server, win2003, sql2005. trying to restore a .bak file from
> sql2000. when excuting the RESTORE DATABASE dbase1 from
> disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> displayed.
> Executing the query ...
> Query (1, 9) Parser: The syntax for 'database' is incorrect.
> Execution complete
> any ideas?
> thanks in advance.
> paul|||i'm in the server management studio, right click on the database, new query,
mdx.
thanks.
paul
"Tibor Karaszi" wrote:
> Where do you execute this restore command? The error seems like some client application error...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >i have new server, win2003, sql2005. trying to restore a .bak file from
> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> > displayed.
> >
> > Executing the query ...
> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> > Execution complete
> >
> > any ideas?
> > thanks in advance.
> > paul
>|||mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> i'm in the server management studio, right click on the database, new query,
> mdx.
> thanks.
> paul
> "Tibor Karaszi" wrote:
>> Where do you execute this restore command? The error seems like some client application error...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> > displayed.
>> >
>> > Executing the query ...
>> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> > Execution complete
>> >
>> > any ideas?
>> > thanks in advance.
>> > paul
>>|||oops, you are correct.
thanks.
when i try to connect to Database Engines a connection error is displayed;
name pipes provider error 40 and ms sql server error 53
"Tibor Karaszi" wrote:
> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
> command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> > i'm in the server management studio, right click on the database, new query,
> > mdx.
> > thanks.
> > paul
> >
> > "Tibor Karaszi" wrote:
> >
> >> Where do you execute this restore command? The error seems like some client application error...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> > displayed.
> >> >
> >> > Executing the query ...
> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> > Execution complete
> >> >
> >> > any ideas?
> >> > thanks in advance.
> >> > paul
> >>
> >>
>|||Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> oops, you are correct.
> thanks.
> when i try to connect to Database Engines a connection error is displayed;
> name pipes provider error 40 and ms sql server error 53
>
> "Tibor Karaszi" wrote:
>> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
>> command.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
>> > i'm in the server management studio, right click on the database, new query,
>> > mdx.
>> > thanks.
>> > paul
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Where do you execute this restore command? The error seems like some client application
>> >> error...
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> >> > displayed.
>> >> >
>> >> > Executing the query ...
>> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> >> > Execution complete
>> >> >
>> >> > any ideas?
>> >> > thanks in advance.
>> >> > paul
>> >>
>> >>
>>|||in SQL Server Config Manager there are 3 items listed; Service, Network
Config, Client Config.
in Services all are running; Integration,Analysis,Reporting, and Browser
in Network Config there are no items listed
in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
VIA
thanks.
paul
"Tibor Karaszi" wrote:
> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> > oops, you are correct.
> > thanks.
> >
> > when i try to connect to Database Engines a connection error is displayed;
> > name pipes provider error 40 and ms sql server error 53
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL Server
> >> command.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> >> > i'm in the server management studio, right click on the database, new query,
> >> > mdx.
> >> > thanks.
> >> > paul
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Where do you execute this restore command? The error seems like some client application
> >> >> error...
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> >> > displayed.
> >> >> >
> >> >> > Executing the query ...
> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> >> > Execution complete
> >> >> >
> >> >> > any ideas?
> >> >> > thanks in advance.
> >> >> > paul
> >> >>
> >> >>
> >>
> >>
>|||Seems you didn't install SQL Server:
> in Services all are running; Integration,Analysis,Reporting, and Browser
If you had installed SQL Server, you would have also a "SQL Server" service.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"paul" <paul@.discussions.microsoft.com> wrote in message
news:BFEF1B78-052E-4EB1-8A9A-D3804256436C@.microsoft.com...
> in SQL Server Config Manager there are 3 items listed; Service, Network
> Config, Client Config.
> in Services all are running; Integration,Analysis,Reporting, and Browser
> in Network Config there are no items listed
> in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
> VIA
> thanks.
> paul
> "Tibor Karaszi" wrote:
>> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "paul" <paul@.discussions.microsoft.com> wrote in message
>> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
>> > oops, you are correct.
>> > thanks.
>> >
>> > when i try to connect to Database Engines a connection error is displayed;
>> > name pipes provider error 40 and ms sql server error 53
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL
>> >> Server
>> >> command.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
>> >> > i'm in the server management studio, right click on the database, new query,
>> >> > mdx.
>> >> > thanks.
>> >> > paul
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Where do you execute this restore command? The error seems like some client application
>> >> >> error...
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
>> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
>> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
>> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
>> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
>> >> >> > displayed.
>> >> >> >
>> >> >> > Executing the query ...
>> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
>> >> >> > Execution complete
>> >> >> >
>> >> >> > any ideas?
>> >> >> > thanks in advance.
>> >> >> > paul
>> >> >>
>> >> >>
>> >>
>> >>
>>|||i'm an idiot.
thanks.
paul
"Tibor Karaszi" wrote:
> Seems you didn't install SQL Server:
> > in Services all are running; Integration,Analysis,Reporting, and Browser
> If you had installed SQL Server, you would have also a "SQL Server" service.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "paul" <paul@.discussions.microsoft.com> wrote in message
> news:BFEF1B78-052E-4EB1-8A9A-D3804256436C@.microsoft.com...
> > in SQL Server Config Manager there are 3 items listed; Service, Network
> > Config, Client Config.
> >
> > in Services all are running; Integration,Analysis,Reporting, and Browser
> > in Network Config there are no items listed
> > in Client config, Protocols, all are running; Shared, NamePipes, TCP/IP, and
> > VIA
> >
> > thanks.
> > paul
> >
> > "Tibor Karaszi" wrote:
> >
> >> Make sure the server is listening on appropriate netlibs (SQL Server Configuration Manager).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> news:7FB8AAE2-17F5-4BCB-9E53-CBF44547A053@.microsoft.com...
> >> > oops, you are correct.
> >> > thanks.
> >> >
> >> > when i try to connect to Database Engines a connection error is displayed;
> >> > name pipes provider error 40 and ms sql server error 53
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> mdx? Seems you have connected to Analysis server. The restore command you posted is a SQL
> >> >> Server
> >> >> command.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> news:13EB6139-0A6A-4F67-9F6F-12B430BE9DC3@.microsoft.com...
> >> >> > i'm in the server management studio, right click on the database, new query,
> >> >> > mdx.
> >> >> > thanks.
> >> >> > paul
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Where do you execute this restore command? The error seems like some client application
> >> >> >> error...
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "paul" <paul@.discussions.microsoft.com> wrote in message
> >> >> >> news:2DBA2DA0-FEE5-481A-AF28-E1D9558BFE05@.microsoft.com...
> >> >> >> >i have new server, win2003, sql2005. trying to restore a .bak file from
> >> >> >> > sql2000. when excuting the RESTORE DATABASE dbase1 from
> >> >> >> > disk='c:\dbasebackup.bak' command, or using FILELISTONLY, the error below is
> >> >> >> > displayed.
> >> >> >> >
> >> >> >> > Executing the query ...
> >> >> >> > Query (1, 9) Parser: The syntax for 'database' is incorrect.
> >> >> >> > Execution complete
> >> >> >> >
> >> >> >> > any ideas?
> >> >> >> > thanks in advance.
> >> >> >> > paul
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>

Script Master DB Restore

I will be transferring database from one server to another along with the ma
ster and msdb database for use as a disaster recovery server. On the second
server, I'd like to script the loading of all the databases (including the m
aster). I can stop the SQL
Service using NET STOP MSSQLSERVER. Then, I can place the database in single
user mode using the command "sqlservr -m". Next, I used OSQL to restore the
master database (which restores the database and stops the sql server - inh
erently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the
window by manually typing <ctrl-c> and exiting the shell. I want this to be
a hands-off operation. Any suggestions?
Thanks!
AdamFirst I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Have you tried the "exit" command?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed.
If you create a .bat file with the commands "sqlservr -m" followed on the n
ext line by "exit", the "exit" command would not be executed until the <cntr
l-c> was entered to end the
"sqlservr -m" command.|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.micros
oft.com...
> I will be transferring database from one server to another along with the master a
nd msdb database for use
as a disaster recovery server. On the second server, I'd like to script the
loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVE
R. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to re
store the master database (which
restores the database and stops the sql server - inherently). The problem is
that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ct
rl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam|||I can not bring down the production server at any time during the day (24x7)
.

Script Master DB Restore

I will be transferring database from one server to another along with the master and msdb database for use as a disaster recovery server. On the second server, I'd like to script the loading of all the databases (including the master). I can stop the SQL
Service using NET STOP MSSQLSERVER. Then, I can place the database in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which restores the database and stops the sql server - inherently). The problem is th
at the command "sqlservr -m" leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I want this to be a hands-off operation. Any suggestions?
Thanks!
Adam
First I believe if you just create a cmd file and run your sqlservr -m from
there I think it will kill the window.
Jeff Duncan
MCDBA, MCSE+I
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Have you tried the "exit" command?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||When you run the "sqlservr -m" the command shell hangs until <ctrl-c> typed. If you create a .bat file with the commands "sqlservr -m" followed on the next line by "exit", the "exit" command would not be executed until the <cntrl-c> was entered to end the
"sqlservr -m" command.
|||"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
Try using:
start sqlservr -m
|||Try using the TSQL "SHUTDOWN" command inside the script you run through OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <ahafner@.itape.com> wrote in message news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the master and msdb database for use
as a disaster recovery server. On the second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP MSSQLSERVER. Then, I can place the database
in single user mode using the command "sqlservr -m". Next, I used OSQL to restore the master database (which
restores the database and stops the sql server - inherently). The problem is that the command "sqlservr -m"
leave the command shell open. I must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||Hi,
Since you are setting up a Disaster recovery server, instead of restoring
the databases one one by , you have a alternate method which is very easy.
I have done this many times and succeeded always.This will work out if
you're moving to a different machine with the same logical drive layout
(Same folder structure) same as production.
Steps:-
1. Install the SQL Server and the same service pack as on your existing
machine.
2 Stop the SQL service on your old machine. Copy the entire SQL7/SQL 2000
folder and it's subfolders,
plus any separate folders containing logs or data, to the new machine.
Also copy over any other folders containing
data that you want to move to the new machine.
3. Start the SQL Server service in new machine. Login to query analyzer and
execute sp_dropserver <drop server> and use sp_addserver <new server>,Local
4. Stop and Start the SQL Server service
5. Check all the database are running fine.
6. Start the SQL Server in production server.
Have a look into Vyas's site
http://vyaskn.tripod.com/moving_sql_server.htm
Thanks
Hari
MCDBA
"Adam" <ahafner@.itape.com> wrote in message
news:B4188C24-EC76-40F1-BD02-A583C87A3075@.microsoft.com...
> I will be transferring database from one server to another along with the
master and msdb database for use as a disaster recovery server. On the
second server, I'd like to script the loading of all the databases
(including the master). I can stop the SQL Service using NET STOP
MSSQLSERVER. Then, I can place the database in single user mode using the
command "sqlservr -m". Next, I used OSQL to restore the master database
(which restores the database and stops the sql server - inherently). The
problem is that the command "sqlservr -m" leave the command shell open. I
must close the window by manually typing <ctrl-c> and exiting the shell. I
want this to be a hands-off operation. Any suggestions?
> Thanks!
> Adam
|||I can not bring down the production server at any time during the day (24x7).

Script jobs for restore to another server

I want to restore all jobs from one server to another. I seem to remember that in SQL 2000 there was an option to script all jobs but it seems that in 2005 you have to script them 1 at a time. Is there a more efficient way than scripting each job and then executing each script individually?

Thanks,

-Dave

you still can. just open the 'summary' window and go to jobs. highlight all the jobs and generate the script.

Script for Adding Users after Restore?

When I backup a database to a single file in SQL2K, then restore that
database onto another machine, I lose users I have created (and I understand
that is expected behavior). Then I manually remove the name of the user
under "Name" since it doesn't have a "Login Name" and then just add this
same user as a New user and both the Name and Login Name appear and
everything is fine.
Is there a way to script this using SQL so if someone unfamiliar with EM
could simply run a script?This happens only with SQL Server authentication, not with Windows
authentication. So the first recommendation to avoid this problem is to use
Windows authentication.
In both cases, the login must exist on the new server.
For SQL Server authentication only use the sp_change_users_login to update
the link between logins and users of a database. For example,
sp_change_users_login 'auto_fix', myuser
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:

> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I understa
nd
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>
>|||Hope this helps!
http://support.microsoft.com/kb/246133/|||Thanks.
"Don Miller" <nospam@.nospam.com> wrote in message
news:e5$tB1scGHA.3888@.TK2MSFTNGP02.phx.gbl...
> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I
understand
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>

Script for Adding Users after Restore?

When I backup a database to a single file in SQL2K, then restore that
database onto another machine, I lose users I have created (and I understand
that is expected behavior). Then I manually remove the name of the user
under "Name" since it doesn't have a "Login Name" and then just add this
same user as a New user and both the Name and Login Name appear and
everything is fine.
Is there a way to script this using SQL so if someone unfamiliar with EM
could simply run a script?Hope this helps!
http://support.microsoft.com/kb/246133/|||This happens only with SQL Server authentication, not with Windows
authentication. So the first recommendation to avoid this problem is to use
Windows authentication.
In both cases, the login must exist on the new server.
For SQL Server authentication only use the sp_change_users_login to update
the link between logins and users of a database. For example,
sp_change_users_login 'auto_fix', myuser
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:
> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I understand
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>
>|||Thanks.
"Don Miller" <nospam@.nospam.com> wrote in message
news:e5$tB1scGHA.3888@.TK2MSFTNGP02.phx.gbl...
> When I backup a database to a single file in SQL2K, then restore that
> database onto another machine, I lose users I have created (and I
understand
> that is expected behavior). Then I manually remove the name of the user
> under "Name" since it doesn't have a "Login Name" and then just add this
> same user as a New user and both the Name and Login Name appear and
> everything is fine.
> Is there a way to script this using SQL so if someone unfamiliar with EM
> could simply run a script?
>

Script DB Object Level Security with SQL2005

We regularly need to take production DB backups and restore them onto our
development SQL Server boxes for testing (SQL2005 SP2). Since DB level
security is much different between production and stage, we need to script
stage rights (server and object level) and refresh those rights onto the
restored production DB. I was able to do that with EM in SQL 2000, but am
unable to do it with 2005 (SP2). I have tried (I think) every possible
option in Management Studio under Tasks / Generate Scripts for the DB and
none generate SQL for object level security. Is this a "bug" with SQL2005
SP2? I believe we were able to do it with SP1, but I don't recall which
options we chose.
--
KevinKevinL (KevinL@.discussions.microsoft.com) writes:
> We regularly need to take production DB backups and restore them onto our
> development SQL Server boxes for testing (SQL2005 SP2). Since DB level
> security is much different between production and stage, we need to script
> stage rights (server and object level) and refresh those rights onto the
> restored production DB. I was able to do that with EM in SQL 2000, but am
> unable to do it with 2005 (SP2). I have tried (I think) every possible
> option in Management Studio under Tasks / Generate Scripts for the DB and
> none generate SQL for object level security. Is this a "bug" with SQL2005
> SP2? I believe we were able to do it with SP1, but I don't recall which
> options we chose.
There is a scripting option "Script Object-level Permissions". that you
can use when you use the Generate Scripts task.
Under Tools->Options there is a new page "Scripting" where you can set
options for when you script individual objects from Object Explorer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.
That appears to have resolved the problem (bug?) with scripting object level
permission, but role membership is still not scripted. Any idea why that
might be?|||KevinL (KevinL@.discussions.microsoft.com) writes:
> That appears to have resolved the problem (bug?) with scripting object
> level permission, but role membership is still not scripted. Any idea
> why that might be?
How do you script? When I script a database by right-clicking it, selecting
Tasks/Generate Scripts and include both Database Roles and Users I do get
role membership scripted at the end.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I see no option to script roles. This is what I did:
Open Management Studio, expand databases, right click the DB, click Tasks,
then Generate Script. The DB I right clicked is highlighted, I click next.
I verified the Script Options to make sure Script Object-Level Permissions i
s
True (there is no Option that mentions Roles) and click next. On Object
Types window choices are Schema, Stored Procedures, Tables, User-defined dat
a
types, User-defined functions and Users.
No matter which option(s) I choose, and I've tried every combination I can
think of, Role level permissions are not scripted.
--
Kevin
"Erland Sommarskog" wrote:

> KevinL (KevinL@.discussions.microsoft.com) writes:
> How do you script? When I script a database by right-clicking it, selectin
g
> Tasks/Generate Scripts and include both Database Roles and Users I do get
> role membership scripted at the end.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||KevinL (KevinL@.discussions.microsoft.com) writes:
> I see no option to script roles. This is what I did:
> Open Management Studio, expand databases, right click the DB, click
> Tasks, then Generate Script. The DB I right clicked is highlighted, I
> click next. I verified the Script Options to make sure Script
> Object-Level Permissions is True (there is no Option that mentions
> Roles) and click next. On Object Types window choices are Schema,
> Stored Procedures, Tables, User-defined data types, User-defined
> functions and Users.
> No matter which option(s) I choose, and I've tried every combination I can
> think of, Role level permissions are not scripted.
If "Database roles" are not listed, this would indicate that you don't
have any user-defined roles in the database, only the pre-defined roles,
db_owner and the like. Indeed, it appears that membership in these roles
are not scripted.
You can script all role membership with this SELECT:
SELECT 'EXEC sp_addrolemember ''' + r.name + ''', ''' + u.name + ''''
FROM sys.database_role_members rm
JOIN sys.database_principals u
ON rm.member_principal_id = u.principal_id
JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
WHERE u.name <> 'dbo'
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

script .bak restore w/ .trn files

I'm the DBA in an environment with approximately 240 identical (structurally) MSSQL 2000 databases in an environment. Each one of these databases are different business units around the country. For each of these DBs, I have my backup schedule set up to do a full backup every friday night, along w/ doing a transactional every 4 hours in between.

Every once in a while (about once every 3 weeks), I get the call to do a restore to pull some piece of data that some end user accidentally deleted or broke. Now most of the time it isn't a big deal, because I can use enterprise mgr to restore to a new DB from the live backup set of the database in question.

But, several of the databases seem to have an issue where (for some reason) my predecessor renamed the logical file names of the data and log files to various goofy things. It doesn't affect their operation, but when I try to do a 'restore from database,' it gives me a bunch of big fat errors. This is also the case when the backup I want to restore something that has been written to tape and has been rolled out of the backup history.

SO... basically for these databases, now I go into Enterprise mgr and first restore the base .bak file to a new database, leaving it marked 'read only' and 'able to restore additional transactions.' I then have to do the same thing with restoring each additional TRN file, all the while making sure I don't forget to set the 'able to restore additional transactions.' Whenever I forget to set that I have to start all over. And honestly, it happens more often than not when I have to restore 5 days out from the .bak file, which means that I have restore 30 trn files on top of the .bak. Not hard, but incredibly tedious.

So here's my idea. I know I can script restores thru QA. I've been looking quite a bit at this MS doc (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E2AA) and have come up with some good ideas. What I want to eventually do is create a script that you can just paste the .bak file and the list of .trn files. The script basically creates a temp table w/ those file names and cursors thru the restore process. The cursor part is easy for me, I'm just not sure about scripting the trn restore. The MS doc is a bit vague and really only shows the Enterprise Mgr method.

Here's what I've been playing w/ so far for restoring the base .bak file:

restore database restore_dbname --NAME OF NEW DB
from disk = 's:\mssql\backup\user\dbname\dbname_db_20060120184 1.bak' --PATH TO BAK FILE
with
move 'pm65mr1_default_data_OldDBName_Data' --LOGICAL FILE NAME OF ORIGINAL MDF
to 'I:\MSSQL$MSSQLSERVER2\Data\restore_dbname.mdf' --PATH TO NEW MDF
move 'pm65mr1_default_data_OldDBName_Log' to --LOGICAL FILE NAME OF ORIGINAL LDF
'U:\MSSQL$MSSQLSERVER2\Log\restore_dbname.ldf' --PATH TO NEW LDF
partial, recovery

I think I'm pretty close with this, but I'm not really sure how I can stack the trn restores on top of that. Any ideas? I've been putzing around the web and so far haven't been able to come up w/ anything really useful.Here is a template for your log restore:


restore log DBAOnly
from disk = 'D:\MSSQL\Backup\InterchangeBTM\InterchangeBTM'
, with NORECOVERY

-- change to 'with RECOVERY' for final log


Now if you are going to use a cursor to restore your logs, just use NORECOVERY in the cursor loop, then when your fetch <> 0 and you exit and deallocate the cursor, build and execute a line like this:

RESTORE database <DBName> WITH RECOVERY

that will finish the roll forward and rollback and make the database available.|||awesome! thanks... I figured it would be something simple like that. I'm gonna have a go at it later today and see if I can get a manual DB restored.