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 .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.