Restore DB**

Hi
I've backed up my DB called DB1 in SQLServer 2000,
which include some users that owns some objects in
the database such as tables,views,...and these users
related to some logins.
now I want to restore DB1 in another Server,
but my problem is how can I define my logins and make a
relation between them and DB1'users?
(note: if I want to restore my DB1 in first server
there's no problem cause the defination of related logins exists)
or is there anyway to restore these information
without any problem?
any help would be greatly apprreciated.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Hi,
After restoration you can use the system stored procedure to fix the login
issue,
sp_change_users_login
Please refere BOL for more infomation. The only issue is you may need to
create the logins manually based on the first server. After that you can run
the above procedure with required parameters.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||This link should help you
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:
> This link should help you
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
> HTH
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:
> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>> Hi
>> I've backed up my DB called DB1 in SQLServer 2000,
>> which include some users that owns some objects in
>> the database such as tables,views,...and these users
>> related to some logins.
>> now I want to restore DB1 in another Server,
>> but my problem is how can I define my logins and make a
>> relation between them and DB1'users?
>> (note: if I want to restore my DB1 in first server
>> there's no problem cause the defination of related logins exists)
>> or is there anyway to restore these information
>> without any problem?
>> any help would be greatly apprreciated.
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

restore db dump

Hi,
I have a problem during the recovery of the sql db dump.
A user had sql server installed in driver c, my application backed up a db
using 'backup database'. Later on the user reinstalled sql server to driver
d, and ran my application to do the recovery. The restore db was successful
but after restart my app it complained that cannot connect to the db. I foun
d
the db was restored in driver c but not in driver d.
How can I fix this problem?
Thanks
WilliamThe new installation of SQL server isn't aware of the restored database. Use
the sp_attach_ db stored procedure (or wizards in Enterprise Manager) to add
the database to the server.
"William" wrote:

> Hi,
> I have a problem during the recovery of the sql db dump.
> A user had sql server installed in driver c, my application backed up a db
> using 'backup database'. Later on the user reinstalled sql server to drive
r
> d, and ran my application to do the recovery. The restore db was successfu
l
> but after restart my app it complained that cannot connect to the db. I fo
und
> the db was restored in driver c but not in driver d.
> How can I fix this problem?
> Thanks
> Williamsql

Restore database without Tran Log

Can I restore a user created database, from a normal SQL Server Full backup,
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Can I restore a user created database, from a normal SQL Server Full

---

backup,

quote:

---

> without restoring the log? My problem is that I have a user who backed up

---

a

quote:

---

> database, deleted it, and now has to restore it again (on another server,
> when the old server no longer exists) that doesn't have enough disk space

---

to

quote:

---

> accomindate the transaction log.
> Thanks in advance.
> Paul
>

---

|||Unfortunately, he doesn't have enough disk to restore to the original
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...

quote:

---

> SQL Server will create the transaction log the size it need to, when you
> issue the restore. My experience is that it will be the same as when you

---

did

quote:

---

> the backup, but I can imagine that it can potentially grow during the
> restore operation (pure speculation here).
> However, this is not anything you can influence. Just run RESTORE
> FILELISTONY and make sure you have the amount of disk that it reports (sum
> the files). Note that you can use the MOVE option to specify some other
> disk(/path) for any of the files.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> backup,
up[QUOTE]
> a
server,[QUOTE]
space[QUOTE]
> to
>

---

Restore database without Tran Log

Can I restore a user created database, from a normal SQL Server Full backup,
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Can I restore a user created database, from a normal SQL Server Full
backup,
> without restoring the log? My problem is that I have a user who backed up
a
> database, deleted it, and now has to restore it again (on another server,
> when the old server no longer exists) that doesn't have enough disk space
to
> accomindate the transaction log.
> Thanks in advance.
> Paul
>|||Unfortunately, he doesn't have enough disk to restore to the original
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> SQL Server will create the transaction log the size it need to, when you
> issue the restore. My experience is that it will be the same as when you
did
> the backup, but I can imagine that it can potentially grow during the
> restore operation (pure speculation here).
> However, this is not anything you can influence. Just run RESTORE
> FILELISTONY and make sure you have the amount of disk that it reports (sum
> the files). Note that you can use the MOVE option to specify some other
> disk(/path) for any of the files.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> > Can I restore a user created database, from a normal SQL Server Full
> backup,
> > without restoring the log? My problem is that I have a user who backed
up
> a
> > database, deleted it, and now has to restore it again (on another
server,
> > when the old server no longer exists) that doesn't have enough disk
space
> to
> > accomindate the transaction log.
> >
> > Thanks in advance.
> >
> > Paul
> >
> >
>

Restore database with no log

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in

backup database CPTS to disk = 'd:\backups\cpts_backup_billeh.bak'
with no_log

I took the backup file and I'm able to restore it on my development
machine with

RESTORE DATABASE [CPTS] FILE = N'CPTS_Data' FROM DISK = N'D:\archives
\Citrix\CPTS\SupportingMaterials\cpts_backup_bille h.bak' WITH MOVE
'CPTS_Data' TO 'd:\databases\CPTS.mdf', MOVE 'CPTS_Log' TO 'd:
\databases\CPTS_log.ldf', MOVE 'CPTS_1_Log' TO 'd:\databases
\CPTS_1_log.ldf'

Note that I cannot additionally specify FILE = N'CPTS_Log' or FILE =
N'CPTS_1_Log' because I get an error.

The restore created the CPTS_Data file only and returned the message

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<

What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Is there another way around this problem? I tried taking the the
resulting mdf file and attaching it using sp_attach_single_file_db but
this didn't work either.

Thanks,

Bill E.
Hollywood, FLI should add that both production and development environments are SQL
Server 2005|||Bill E. (billmiami2@.netscape.net) writes:

Quote:

Originally Posted by

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in
>...

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<
>
What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Books Online says about NO_LOG:

In the context of a BACKUP DATABASE statement, specifies that a backup
will not contain any log. This equates to how file backups were created
before SQL Server 2005. A database backup created with NO_LOG equates
to a full set of file backups that contains no log records.

Under the full recovery model, NO_LOG is useful if you need to back up
data quickly, and you have a complete sequence of log backups of that
data.

The last paragraph implies that you are expected to apply the transaction
logs from elsewhere.

There is a reason why this does not work as you had expected: you
see, that log serves a purpose. A database backup is taken online, which
means that pages can be updated while it's working. Therefore the
backup must include log records, so that transactions that were committed
when the backup was running can be rolled forward. And more importantly,
transactions that had not yet been committed when the backup completed,
must be rolled back. Thus a database restored from a backup with no log
content is in a inconsistent state, and thus you are not permitted in.

I would suggest that you do a regular backup/restore, and then shrink
the log files once you have restored the database and set the recovery
mode to simple. A tip is to add WITH COPY_ONLY to the BACKUP command.
That prevents the backup from being recorded as a "real" backup.

--
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|||You're not being nosey. I don't know why this little PC was formatted
that way, but it was. It's a Windows x64 PC. I don't think that I'm
going to go through the trouble of reformatting the drives. This is
the first time that the issue has come arisen.

Bill

Restore database to default location on anohter server?

I have a database called "test_db" that has been backed up with the
command:
BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
I want to restore it to another Sql Server and call it
'Test_Restored_DB', as well as save it as a different physical file
name. So I copy the file over to the new server into the folder
c:\temp\ and then execute the command:
RESTORE DATABASE Test_Restored_DB
FROM DISK = 'c:\temp\test_db.bak'
WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
This works fine. But what I really want to do is restore the database
to the default location on Sql Server 2005, i.e. typically "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
hard-coding this path in. This is because I am assuming that some Sql
Servers may be configured differently and have their default data
directory located in another folder (I have no control on the
configuration of various sql servers where this will be run). Is there
a way to restore a database to another server, give it a new name,
change its physical file name, and have it placed in the default data
directory without specifying the data directory? Or perhaps there is a
stored procedure (not xp_cmdshell) or some other t-sql code that will
tell me where the default data directory is located?
Thanks for any feedback,
MarcusHi,
I have never tried that but this is what SQL Server does in the background
to find these locations
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
@.SmoDefaultFile OUTPUT
print @.SmoDefaultFile
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
OUTPUT
print @.SmoDefaultLog
declare @.SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
print @.SmoRoot
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marcus" wrote:
> I have a database called "test_db" that has been backed up with the
> command:
> BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> I want to restore it to another Sql Server and call it
> 'Test_Restored_DB', as well as save it as a different physical file
> name. So I copy the file over to the new server into the folder
> c:\temp\ and then execute the command:
> RESTORE DATABASE Test_Restored_DB
> FROM DISK = 'c:\temp\test_db.bak'
> WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> This works fine. But what I really want to do is restore the database
> to the default location on Sql Server 2005, i.e. typically "C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> hard-coding this path in. This is because I am assuming that some Sql
> Servers may be configured differently and have their default data
> directory located in another folder (I have no control on the
> configuration of various sql servers where this will be run). Is there
> a way to restore a database to another server, give it a new name,
> change its physical file name, and have it placed in the default data
> directory without specifying the data directory? Or perhaps there is a
> stored procedure (not xp_cmdshell) or some other t-sql code that will
> tell me where the default data directory is located?
> Thanks for any feedback,
> Marcus
>|||Excellent, thanks, Ben! With a little bit more googling of
xp_instance_regread lead me to this page which more stuff that I could
use:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Cheers,
Marcus
Ben Nevarez wrote:
> Hi,
> I have never tried that but this is what SQL Server does in the background
> to find these locations
> declare @.SmoDefaultFile nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
> @.SmoDefaultFile OUTPUT
> print @.SmoDefaultFile
> declare @.SmoDefaultLog nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
> OUTPUT
> print @.SmoDefaultLog
> declare @.SmoRoot nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
> print @.SmoRoot
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marcus" wrote:
> > I have a database called "test_db" that has been backed up with the
> > command:
> >
> > BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> >
> > I want to restore it to another Sql Server and call it
> > 'Test_Restored_DB', as well as save it as a different physical file
> > name. So I copy the file over to the new server into the folder
> > c:\temp\ and then execute the command:
> >
> > RESTORE DATABASE Test_Restored_DB
> > FROM DISK = 'c:\temp\test_db.bak'
> > WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> > MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> >
> > This works fine. But what I really want to do is restore the database
> > to the default location on Sql Server 2005, i.e. typically "C:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> > hard-coding this path in. This is because I am assuming that some Sql
> > Servers may be configured differently and have their default data
> > directory located in another folder (I have no control on the
> > configuration of various sql servers where this will be run). Is there
> > a way to restore a database to another server, give it a new name,
> > change its physical file name, and have it placed in the default data
> > directory without specifying the data directory? Or perhaps there is a
> > stored procedure (not xp_cmdshell) or some other t-sql code that will
> > tell me where the default data directory is located?
> >
> > Thanks for any feedback,
> > Marcus
> >
> >sql

Restore database to default location on anohter server?

I have a database called "test_db" that has been backed up with the
command:
BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
I want to restore it to another Sql Server and call it
'Test_Restored_DB', as well as save it as a different physical file
name. So I copy the file over to the new server into the folder
c:\temp\ and then execute the command:
RESTORE DATABASE Test_Restored_DB
FROM DISK = 'c:\temp\test_db.bak'
WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_D
B.mdf',
MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
This works fine. But what I really want to do is restore the database
to the default location on Sql Server 2005, i.e. typically "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
hard-coding this path in. This is because I am assuming that some Sql
Servers may be configured differently and have their default data
directory located in another folder (I have no control on the
configuration of various sql servers where this will be run). Is there
a way to restore a database to another server, give it a new name,
change its physical file name, and have it placed in the default data
directory without specifying the data directory? Or perhaps there is a
stored procedure (not xp_cmdshell) or some other t-sql code that will
tell me where the default data directory is located?
Thanks for any feedback,
MarcusHi,
I have never tried that but this is what SQL Server does in the background
to find these locations
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultData',
@.SmoDefaultFile OUTPUT
print @.SmoDefaultFile
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultLog', @.SmoDefaultLog
OUTPUT
print @.SmoDefaultLog
declare @.SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @.SmoRoot OUTPUT
print @.SmoRoot
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marcus" wrote:

> I have a database called "test_db" that has been backed up with the
> command:
> BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> I want to restore it to another Sql Server and call it
> 'Test_Restored_DB', as well as save it as a different physical file
> name. So I copy the file over to the new server into the folder
> c:\temp\ and then execute the command:
> RESTORE DATABASE Test_Restored_DB
> FROM DISK = 'c:\temp\test_db.bak'
> WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_D
B.mdf',
> MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> This works fine. But what I really want to do is restore the database
> to the default location on Sql Server 2005, i.e. typically "C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> hard-coding this path in. This is because I am assuming that some Sql
> Servers may be configured differently and have their default data
> directory located in another folder (I have no control on the
> configuration of various sql servers where this will be run). Is there
> a way to restore a database to another server, give it a new name,
> change its physical file name, and have it placed in the default data
> directory without specifying the data directory? Or perhaps there is a
> stored procedure (not xp_cmdshell) or some other t-sql code that will
> tell me where the default data directory is located?
> Thanks for any feedback,
> Marcus
>|||Excellent, thanks, Ben! With a little bit more googling of
xp_instance_regread lead me to this page which more stuff that I could
use:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Cheers,
Marcus
Ben Nevarez wrote:[vbcol=seagreen]
> Hi,
> I have never tried that but this is what SQL Server does in the background
> to find these locations
> declare @.SmoDefaultFile nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultData',
> @.SmoDefaultFile OUTPUT
> print @.SmoDefaultFile
> declare @.SmoDefaultLog nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLSe
rver', N'DefaultLog', @.SmoDefaultL
og
> OUTPUT
> print @.SmoDefaultLog
> declare @.SmoRoot nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @.SmoRoot OUTPUT
> print @.SmoRoot
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marcus" wrote:
>

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?
Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?
|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?
|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:

> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>
|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:

> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:

> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:

> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:
> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
> >I have a development db, test db and live db.
> > I want to copy my development db to my test db.
> > I've backed both up and then restored the test db using the development db
> > backup
> > changing the file and log paths.
> > However when I go into the test db the data in the tables is not correct.
> > For example in the development db I have 10 rows in one table but in the
> > corresponding table in test db I only have the 2 original Test db rows.
> > Can you explain why?
>
>|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:
> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
> >I have a development db, test db and live db.
> > I want to copy my development db to my test db.
> > I've backed both up and then restored the test db using the development db
> > backup
> > changing the file and log paths.
> > However when I go into the test db the data in the tables is not correct.
> > For example in the development db I have 10 rows in one table but in the
> > corresponding table in test db I only have the 2 original Test db rows.
> > Can you explain why?
>
>

Restore Database from file

Hello, I had backed up a database from SQL 2000, just before I format my computer. Now I have installed SQL 2005 and trying to restore that database to it, it doesn't work and I get the following error when doing that

===================================

Restore failed for Server 'localhost\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()

===================================

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing '2for1' database. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)

========================================================

any help is appreciated

Thanks,

COuld you please post the command you were issueing in your program ?

-Jens Suessmeyer.|||

Thank you for your kind reply.

I didn't use command to restore the database from file but I did it through SQL Server Management Studio Express CTP

|||

I've also tried the following command to restore from file

RESTORE DATABASE [2for1] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\db' WITH FILE = 1, NOUNLOAD, STATS = 10

GO

|||

Hi,

I guess your are not the same as from the server your backup was originally taken from, right ? Then you have to use the WITH MOVE option. (From BOL)

[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]

This will help you to place them on a destination that exists on your system.

HTH, jens Suessmeyer.

Restore database from a network drive

Hi guys,
I have Windows 2003 server and SQL2005 Oct. CPT running. I have backed up
file .BAK on a network. So in my SQL server, I mapped the folder and try to
restore database from there, I have gotten an error. So i thought might be
the backup file is bad. So I copied my model backedup file to the network
drive and ran FilelistOnly. I got exact same error return,
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'q:\model_backup_200601240100.bak'. Operating
system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Has any one seem this error? I don't think I have restored a database from
network drive but I know I can access to the network drive without an issue.
Below is the simple steps what I did on my sql server.
on dos prompt win, net use q: \\myserver\backupfile.bak
q:
dir *.bak
I saw the backup file.
In query windows, 2005 SQL management studio.
restore filelistonly from disk = 'q:\mybackupfile.bak'
Is it not possible to restore it from network drive?
Any input will be appreciated.
Sunny,
Restoring TO and FROM NETWORK drives is possible, but rather than
mapped drives you should use the UNC drives rather than mapped driver
letters. This has many implications, one of them is that you have to
make sure that if e.g. you use the SQL Agent you have to make sure that
the sevrice account has the driver letter mapped in his logon script
(which is a hazzle). So rather use a UNC path with the appropiate
permissions.
HTH, Jens Suessmeyer.
|||Hi Jens
Thanks for the input. I just realized that the SQL server serive is running
as LocalSystem. I thought I changed all SQL services to run as a domain user
ID.
I think that's why. I have to wait until 5 PM and try it if that's the case
or not.
Will post once I find out.
Thanks,
"Jens" wrote:

> Restoring TO and FROM NETWORK drives is possible, but rather than
> mapped drives you should use the UNC drives rather than mapped driver
> letters. This has many implications, one of them is that you have to
> make sure that if e.g. you use the SQL Agent you have to make sure that
> the sevrice account has the driver letter mapped in his logon script
> (which is a hazzle). So rather use a UNC path with the appropiate
> permissions.
> HTH, Jens Suessmeyer.
>

Restore database from a network drive

Hi guys,
I have Windows 2003 server and SQL2005 Oct. CPT running. I have backed up
file .BAK on a network. So in my SQL server, I mapped the folder and try to
restore database from there, I have gotten an error. So i thought might be
the backup file is bad. So I copied my model backedup file to the network
drive and ran FilelistOnly. I got exact same error return,
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'q:\model_backup_200601240100.bak'. Operating
system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Has any one seem this error? I don't think I have restored a database from
network drive but I know I can access to the network drive without an issue.
Below is the simple steps what I did on my sql server.
on dos prompt win, net use q: \\myserver\backupfile.bak
q:
dir *.bak
I saw the backup file.
In query windows, 2005 SQL management studio.
restore filelistonly from disk = 'q:\mybackupfile.bak'
Is it not possible to restore it from network drive'
Any input will be appreciated.
Sunny,Restoring TO and FROM NETWORK drives is possible, but rather than
mapped drives you should use the UNC drives rather than mapped driver
letters. This has many implications, one of them is that you have to
make sure that if e.g. you use the SQL Agent you have to make sure that
the sevrice account has the driver letter mapped in his logon script
(which is a hazzle). So rather use a UNC path with the appropiate
permissions.
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the input. I just realized that the SQL server serive is running
as LocalSystem. I thought I changed all SQL services to run as a domain use
r
ID.
I think that's why. I have to wait until 5 PM and try it if that's the case
or not.
Will post once I find out.
Thanks,
"Jens" wrote:

> Restoring TO and FROM NETWORK drives is possible, but rather than
> mapped drives you should use the UNC drives rather than mapped driver
> letters. This has many implications, one of them is that you have to
> make sure that if e.g. you use the SQL Agent you have to make sure that
> the sevrice account has the driver letter mapped in his logon script
> (which is a hazzle). So rather use a UNC path with the appropiate
> permissions.
> HTH, Jens Suessmeyer.
>sql

Restore database from a network drive

Hi guys,
I have Windows 2003 server and SQL2005 Oct. CPT running. I have backed up
file .BAK on a network. So in my SQL server, I mapped the folder and try to
restore database from there, I have gotten an error. So i thought might be
the backup file is bad. So I copied my model backedup file to the network
drive and ran FilelistOnly. I got exact same error return,
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'q:\model_backup_200601240100.bak'. Operating
system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Has any one seem this error? I don't think I have restored a database from
network drive but I know I can access to the network drive without an issue.
Below is the simple steps what I did on my sql server.
on dos prompt win, net use q: \\myserver\backupfile.bak
q:
dir *.bak
I saw the backup file.
In query windows, 2005 SQL management studio.
restore filelistonly from disk = 'q:\mybackupfile.bak'
Is it not possible to restore it from network drive'
Any input will be appreciated.
Sunny,Restoring TO and FROM NETWORK drives is possible, but rather than
mapped drives you should use the UNC drives rather than mapped driver
letters. This has many implications, one of them is that you have to
make sure that if e.g. you use the SQL Agent you have to make sure that
the sevrice account has the driver letter mapped in his logon script
(which is a hazzle). So rather use a UNC path with the appropiate
permissions.
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the input. I just realized that the SQL server serive is running
as LocalSystem. I thought I changed all SQL services to run as a domain user
ID.
I think that's why. I have to wait until 5 PM and try it if that's the case
or not.
Will post once I find out.
Thanks,
"Jens" wrote:
> Restoring TO and FROM NETWORK drives is possible, but rather than
> mapped drives you should use the UNC drives rather than mapped driver
> letters. This has many implications, one of them is that you have to
> make sure that if e.g. you use the SQL Agent you have to make sure that
> the sevrice account has the driver letter mapped in his logon script
> (which is a hazzle). So rather use a UNC path with the appropiate
> permissions.
> HTH, Jens Suessmeyer.
>

RESTORE DATABASE ... WITH PARTIAL

I posted this to another group but I must not be subscribed as nothing
appeared. Sooo...

I've backed up a databse with the following command:

BACKUP DATABASE Thomasville
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH FORMAT,
Password = 'p@.$$w0rd'

I back up using Filegroups because I dont' want to back up the large,
static ones on a regular basis. This backup worked just fine. When I
try to restore it I run:

RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
PASSWORD = 'p@.$$w0rd',
MOVE 'Thomasville_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_Thomas.LDF',
NORECOVERY

I get this:

Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak' was created by BACKUP DATABASE...FILE=<name>
and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Any ideas what I'm doing wrong here? Except for what applies to my
backup this is copied character for character from some Microsoft BOL.

Please help.

Thanks,
Utah(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

I back up using Filegroups because I dont' want to back up the large,
static ones on a regular basis. This backup worked just fine. When I
try to restore it I run:
>
RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
PASSWORD = 'p@.$$w0rd',
MOVE 'Thomasville_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_Thomas.LDF',
NORECOVERY
>
I get this:
>
Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak' was created by BACKUP DATABASE...FILE=<name>
and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

In SQL 2000 you can only do partial restore from a full backup. SQL 2005
provides new options, although I am not certain that they addrss your
needs.

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

Restore database

Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.
Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive and
> I need to to Restore several of them to the SQL Server 2000. I have tried to
> restore three of them but in Enterprise Manager it lists the three that I am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen
|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Jim Richards wrote:
Drive. I[vbcol=seagreen]
defined[vbcol=seagreen]
and[vbcol=seagreen]
tried to[vbcol=seagreen]
I am[vbcol=seagreen]
(Loading/Suspect)
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen
|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.[vbcol=seagreen]
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)
>
|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.[vbcol=seagreen]
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)
>

Restore database

Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive a
nd
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> Jim Richards wrote:
Drive. I[vbcol=seagreen]
defined[vbcol=seagreen]
and[vbcol=seagreen]
tried to[vbcol=seagreen]
I am[vbcol=seagreen]
(Loading/Suspect)[vbcol=seagreen]
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)[vbcol=seagreen]
>|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)[vbcol=seagreen]
>

Restore database

Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive and
> I need to to Restore several of them to the SQL Server 2000. I have tried to
> restore three of them but in Enterprise Manager it lists the three that I am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> Jim Richards wrote:
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive. I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried to
> > restore three of them but in Enterprise Manager it lists the three that
I am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive.
> I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
> and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried
> to
> > restore three of them but in Enterprise Manager it lists the three that
I
> am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
>|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive.
> I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
> and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried
> to
> > restore three of them but in Enterprise Manager it lists the three that
I
> am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
>

Restore Analysis Backup to new DAtabase Name

I have backed up an AS database. Now I want to restore it to a new name so I have two copies to play with. How do I do this?

Thanks,

Chris

Hi,

I'm not sure how to copy a SSAS database by backup/restore. However what about

- copy the VS project
- deploy the project with a different name

I guess that might be even better than just a copy of the database...|||Change the file name of the database backup then restore it. That's all|||

That won't work as we are looking at a way to automate deployment of around 2300 databases. The technique we use with SQL is to have a template database that is created as part of our build process and then we restore this with a new name for each unique instance.

This does not appear to work for Analysis Services. We even looked at restoring using the template name and then renaming the database. However while that changes the name it appears it does not change the ID preventing you from doing another restore.

|||That doesn't work. The database name is not based on the backup file name. It is encoded in the backup.|||Just an idea, I didn't have any time to try it, yet... But there is a way (as far as I remember) to script out a complete database... Save the script any modify it (i.e. the name of the database)... Then send the modified script to the server.. That might work...|||That is what I am doing but it is very different than our current practice for the relational stores so I was hoping someone else had hit this.

With this technique I have to get the operations guys to edit the XML which they have a high likelihood of hosing up...

Thanks,

Chris|||Chris,

Took me a while to work this out, but I think this should do.

Within BIDS, if you use the "Build" command instead of the "Deploy" command, you'll end up with a set of files in a \bin directory under your project's path. This directory contains a set of files that can be used to deploy the project manually using the deployment wizard, located by default at the following path:

c:\program files\microsoft sql server\90\tools\binn\vsshell\common7\ide\microsoft.analysisservices.deployment.exe

The files created are as follows:

<databasename>.asdatabase
<databasename>.configsettings
<databasename>.deploymentoptions
<databasename>.deploymenttargets

You can review each of these, but basically the first contains metadata to create the database (almost the equivalent of using the "Create Database As" scripting option in SSMS) and the other three contain settings that the deployment wizard will use to deploy the database. The last one is the key for you -- it contains the name of the server and the name of the deployed database, which can be different from the name given to the database in BIDS.

To work with these files, you have two options:

The first would be to have your operations folks modify the .deploymenttargets file manually, modifying the server and database names as needed. This file is MUCH simpler than a complete DDL script, so modifying it manually might be an option for you. The operations folks would them simply have to use the deployment wizard to deploy the database. In this case, the wizard would need two parameters --the name of the .asdatabase file plus a "/s" option, which tells the wizard to read the other files as input and do a deployment.

The second option would be to have the operations folks use the deployment wizard to update the files and then use it again to do a deployment. If you start the wizard with the name of the .asdatabase file plus a "/a" option, the wizard will ask a bunch of questions related to doing a deployment. The answers to the questions will be used to rewrite the other three files listed above. The wizard could then be run again with the .asdatabase name plus a "/s" option to do the deployment. This method would keep you from having to rely on someone correctly modifying the .deploymenttargets file manually.

You can find information about the deployment wizard in BOL...

Hope this helps.

Dave Fackler

PS. I did a test of this by using the "Build" option within BIDS on a copy of the Adventure Works DW solution. I then updated the .deploymenttargets file, updating the name of the database from "Adventure Works DW" to "My New Database". I then ran the deployment wizard as follows:

microsoft.analysisservices.deployment.exe "Adventure Works DW.asdatabase" /s

The wizard deployed the database to my server, where it showed up with the name "My New Database". I then checked via a "Create Database As" script option on the new database and both the name and the ID of the database reflected my new name.
|||Post CTP 15 builds will have a fix allowing you to restore database with different name. This could be done by running Restore command and specifying alternative Name.

--
This posting is provided "AS IS" with no warranties, and confers no rights|||

How do you get around the problem of credentials of multiple domains. Dev machine on one domain, server on anther (via VPN at client).

I know I could problably copy all the files to the server and change the configs.

Anybody researched this?

Thanks

Restore Analysis Backup to new DAtabase Name

I have backed up an AS database. Now I want to restore it to a new name so I have two copies to play with. How do I do this?

Thanks,

Chris

Hi,

I'm not sure how to copy a SSAS database by backup/restore. However what about

- copy the VS project
- deploy the project with a different name

I guess that might be even better than just a copy of the database...|||Change the file name of the database backup then restore it. That's all|||

That won't work as we are looking at a way to automate deployment of around 2300 databases. The technique we use with SQL is to have a template database that is created as part of our build process and then we restore this with a new name for each unique instance.

This does not appear to work for Analysis Services. We even looked at restoring using the template name and then renaming the database. However while that changes the name it appears it does not change the ID preventing you from doing another restore.

|||That doesn't work. The database name is not based on the backup file name. It is encoded in the backup.|||Just an idea, I didn't have any time to try it, yet... But there is a way (as far as I remember) to script out a complete database... Save the script any modify it (i.e. the name of the database)... Then send the modified script to the server.. That might work...|||That is what I am doing but it is very different than our current practice for the relational stores so I was hoping someone else had hit this.

With this technique I have to get the operations guys to edit the XML which they have a high likelihood of hosing up...

Thanks,

Chris|||Chris,

Took me a while to work this out, but I think this should do.

Within BIDS, if you use the "Build" command instead of the "Deploy" command, you'll end up with a set of files in a \bin directory under your project's path. This directory contains a set of files that can be used to deploy the project manually using the deployment wizard, located by default at the following path:

c:\program files\microsoft sql server\90\tools\binn\vsshell\common7\ide\microsoft.analysisservices.deployment.exe

The files created are as follows:

<databasename>.asdatabase
<databasename>.configsettings
<databasename>.deploymentoptions
<databasename>.deploymenttargets

You can review each of these, but basically the first contains metadata to create the database (almost the equivalent of using the "Create Database As" scripting option in SSMS) and the other three contain settings that the deployment wizard will use to deploy the database. The last one is the key for you -- it contains the name of the server and the name of the deployed database, which can be different from the name given to the database in BIDS.

To work with these files, you have two options:

The first would be to have your operations folks modify the .deploymenttargets file manually, modifying the server and database names as needed. This file is MUCH simpler than a complete DDL script, so modifying it manually might be an option for you. The operations folks would them simply have to use the deployment wizard to deploy the database. In this case, the wizard would need two parameters --the name of the .asdatabase file plus a "/s" option, which tells the wizard to read the other files as input and do a deployment.

The second option would be to have the operations folks use the deployment wizard to update the files and then use it again to do a deployment. If you start the wizard with the name of the .asdatabase file plus a "/a" option, the wizard will ask a bunch of questions related to doing a deployment. The answers to the questions will be used to rewrite the other three files listed above. The wizard could then be run again with the .asdatabase name plus a "/s" option to do the deployment. This method would keep you from having to rely on someone correctly modifying the .deploymenttargets file manually.

You can find information about the deployment wizard in BOL...

Hope this helps.

Dave Fackler

PS. I did a test of this by using the "Build" option within BIDS on a copy of the Adventure Works DW solution. I then updated the .deploymenttargets file, updating the name of the database from "Adventure Works DW" to "My New Database". I then ran the deployment wizard as follows:

microsoft.analysisservices.deployment.exe "Adventure Works DW.asdatabase" /s

The wizard deployed the database to my server, where it showed up with the name "My New Database". I then checked via a "Create Database As" script option on the new database and both the name and the ID of the database reflected my new name.|||Post CTP 15 builds will have a fix allowing you to restore database with different name. This could be done by running Restore command and specifying alternative Name.

--
This posting is provided "AS IS" with no warranties, and confers no rights|||

How do you get around the problem of credentials of multiple domains. Dev machine on one domain, server on anther (via VPN at client).

I know I could problably copy all the files to the server and change the configs.

Anybody researched this?

Thanks