Restored database in loading mode.

I tried to restore a dump database to a newly created
database.After it prompts the msg that the database has
beem successfully restored, in the Enterprise Manager, the
database (the restored database) is on the loading mode.
Is there any solution for this.
The data is running on SQL 2000(service pack 3A) in Win
Server 2003.
Pls advise.
Thanks.Seems like you forgot to do the Recovery. Check the "Recovering a Database
Without Restoring" topic in Books OnLine
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_bkprst_4zc7.htm).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.|||In addition to Dejans message when you restore a database it is unavailable
(loading) while you restore the differential and all of the transaction
logs. When you have completed the restore, you run recovery will rolls back
incomplete transactions and makes the database available.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ash'" <sukli@.hotmail.com> wrote in message
news:0c1101c38afb$e92d4b70$a401280a@.phx.gbl...
> I tried to restore a dump database to a newly created
> database.After it prompts the msg that the database has
> beem successfully restored, in the Enterprise Manager, the
> database (the restored database) is on the loading mode.
> Is there any solution for this.
> The data is running on SQL 2000(service pack 3A) in Win
> Server 2003.
> Pls advise.
> Thanks.

Restore to SQL 2005?

Hello,
I'm using SQL 2005 Express and I have a database that I've created a backup
for, but when my ISP tries to restore it with the full version of SQL 2005,
they say the tables are missing. Any ideas why I can restore it with the
Express edition and they can't do it with the full one?
Thanks
JimWhat exactly is meant by "the tables are missing"? Is it that the database
restores successfully but is empty?
On possible cause is that the database backup file contains multiple backups
and only the first (oldest) is restored by default.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>|||Hi Jim
Can you give us more details?
What does 'full version of SQL 2005' mean? Is it standard or enterprise?
32-bit or 64-bit? What OS?
WHO says tables are missing? What tables? Is there an error message? When
does it show up? What does it say exactly?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>|||I'm using Visual Web Developer with SQL 2005 Express. I have a database that
I created for my website and everything works the way it should on my
computer. My ISP says the only way they can create the database on their
side is to restore it from a backup. I created a full backup by right
clicking on the database and going through all the steps. When my ISP tries
to restore it, they're telling me that the database is empty. They are using
SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00. I'm using Win
Xp 32bit. There are no error messages on either my side or their's when
creating or restoring the tables.
Jim
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi Jim
> Can you give us more details?
> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
> 32-bit or 64-bit? What OS?
> WHO says tables are missing? What tables? Is there an error message? When
> does it show up? What does it say exactly?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a
>> backup for, but when my ISP tries to restore it with the full version of
>> SQL 2005, they say the tables are missing. Any ideas why I can restore it
>> with the Express edition and they can't do it with the full one?
>> Thanks
>> Jim
>>
>
>|||Make sure that you backup to a new file (a file which doesn't exist) so you don't by mistake append
the backup and the always restore the most oldest backup. Or check out the INIT option of the backup
command.
Also, don't take your ISP word that the db is empty. Login using a query window (if at all possible)
and verify for yourself. Also, make sure this isn't just a side effect of login - username mismatch
(read about sp_change_users_login and Google for sp_help_revlogins).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim" <jim@.gordonferon.com> wrote in message news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
> I'm using Visual Web Developer with SQL 2005 Express. I have a database that I created for my
> website and everything works the way it should on my computer. My ISP says the only way they can
> create the database on their side is to restore it from a backup. I created a full backup by right
> clicking on the database and going through all the steps. When my ISP tries to restore it, they're
> telling me that the database is empty. They are using SQL 2005 Standard version 9.0.3 & my version
> is 9.00.2047.00. I'm using Win Xp 32bit. There are no error messages on either my side or their's
> when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>> Hi Jim
>> Can you give us more details?
>> What does 'full version of SQL 2005' mean? Is it standard or enterprise? 32-bit or 64-bit? What
>> OS?
>> WHO says tables are missing? What tables? Is there an error message? When does it show up? What
>> does it say exactly?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Jim" <jim@.gordonferon.com> wrote in message news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a backup for, but when my ISP
>> tries to restore it with the full version of SQL 2005, they say the tables are missing. Any
>> ideas why I can restore it with the Express edition and they can't do it with the full one?
>> Thanks
>> Jim
>>
>>
>
>|||A database can never be completely empty, so this makes me wonder how your
ISP is determining it is 'empty'. My guess is that they are not SQL Server
Professionals, and are jumping to wild conclusions.
Perhaps they are just running sp_help to see a list of objects, or expanding
the tables folder in SSMS Object Explorer, and if they don't have the
appropriate permissions, they will get an empty list.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
> I'm using Visual Web Developer with SQL 2005 Express. I have a database
> that I created for my website and everything works the way it should on my
> computer. My ISP says the only way they can create the database on their
> side is to restore it from a backup. I created a full backup by right
> clicking on the database and going through all the steps. When my ISP
> tries to restore it, they're telling me that the database is empty. They
> are using SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00.
> I'm using Win Xp 32bit. There are no error messages on either my side or
> their's when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>> Hi Jim
>> Can you give us more details?
>> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
>> 32-bit or 64-bit? What OS?
>> WHO says tables are missing? What tables? Is there an error message? When
>> does it show up? What does it say exactly?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Jim" <jim@.gordonferon.com> wrote in message
>> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a
>> backup for, but when my ISP tries to restore it with the full version of
>> SQL 2005, they say the tables are missing. Any ideas why I can restore
>> it with the Express edition and they can't do it with the full one?
>> Thanks
>> Jim
>>
>>
>
>|||Thanks for the suggestions. I ended up copying the data over to tables they
created.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OmZWtQdrHHA.4828@.TK2MSFTNGP04.phx.gbl...
>A database can never be completely empty, so this makes me wonder how your
>ISP is determining it is 'empty'. My guess is that they are not SQL Server
>Professionals, and are jumping to wild conclusions.
> Perhaps they are just running sp_help to see a list of objects, or
> expanding the tables folder in SSMS Object Explorer, and if they don't
> have the appropriate permissions, they will get an empty list.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
>> I'm using Visual Web Developer with SQL 2005 Express. I have a database
>> that I created for my website and everything works the way it should on
>> my computer. My ISP says the only way they can create the database on
>> their side is to restore it from a backup. I created a full backup by
>> right clicking on the database and going through all the steps. When my
>> ISP tries to restore it, they're telling me that the database is empty.
>> They are using SQL 2005 Standard version 9.0.3 & my version is
>> 9.00.2047.00. I'm using Win Xp 32bit. There are no error messages on
>> either my side or their's when creating or restoring the tables.
>> Jim
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>> Hi Jim
>> Can you give us more details?
>> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
>> 32-bit or 64-bit? What OS?
>> WHO says tables are missing? What tables? Is there an error message?
>> When does it show up? What does it say exactly?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Jim" <jim@.gordonferon.com> wrote in message
>> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I'm using SQL 2005 Express and I have a database that I've created a
>> backup for, but when my ISP tries to restore it with the full version
>> of SQL 2005, they say the tables are missing. Any ideas why I can
>> restore it with the Express edition and they can't do it with the full
>> one?
>> Thanks
>> Jim
>>
>>
>>
>>
>
>sql

Restore to SQL 2005?

Hello,
I'm using SQL 2005 Express and I have a database that I've created a backup
for, but when my ISP tries to restore it with the full version of SQL 2005,
they say the tables are missing. Any ideas why I can restore it with the
Express edition and they can't do it with the full one?
Thanks
Jim
What exactly is meant by "the tables are missing"? Is it that the database
restores successfully but is empty?
On possible cause is that the database backup file contains multiple backups
and only the first (oldest) is restored by default.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>
|||Hi Jim
Can you give us more details?
What does 'full version of SQL 2005' mean? Is it standard or enterprise?
32-bit or 64-bit? What OS?
WHO says tables are missing? What tables? Is there an error message? When
does it show up? What does it say exactly?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>
|||I'm using Visual Web Developer with SQL 2005 Express. I have a database that
I created for my website and everything works the way it should on my
computer. My ISP says the only way they can create the database on their
side is to restore it from a backup. I created a full backup by right
clicking on the database and going through all the steps. When my ISP tries
to restore it, they're telling me that the database is empty. They are using
SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00. I'm using Win
Xp 32bit. There are no error messages on either my side or their's when
creating or restoring the tables.
Jim
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi Jim
> Can you give us more details?
> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
> 32-bit or 64-bit? What OS?
> WHO says tables are missing? What tables? Is there an error message? When
> does it show up? What does it say exactly?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
>
|||Make sure that you backup to a new file (a file which doesn't exist) so you don't by mistake append
the backup and the always restore the most oldest backup. Or check out the INIT option of the backup
command.
Also, don't take your ISP word that the db is empty. Login using a query window (if at all possible)
and verify for yourself. Also, make sure this isn't just a side effect of login - username mismatch
(read about sp_change_users_login and Google for sp_help_revlogins).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim" <jim@.gordonferon.com> wrote in message news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
> I'm using Visual Web Developer with SQL 2005 Express. I have a database that I created for my
> website and everything works the way it should on my computer. My ISP says the only way they can
> create the database on their side is to restore it from a backup. I created a full backup by right
> clicking on the database and going through all the steps. When my ISP tries to restore it, they're
> telling me that the database is empty. They are using SQL 2005 Standard version 9.0.3 & my version
> is 9.00.2047.00. I'm using Win Xp 32bit. There are no error messages on either my side or their's
> when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>
>
|||A database can never be completely empty, so this makes me wonder how your
ISP is determining it is 'empty'. My guess is that they are not SQL Server
Professionals, and are jumping to wild conclusions.
Perhaps they are just running sp_help to see a list of objects, or expanding
the tables folder in SSMS Object Explorer, and if they don't have the
appropriate permissions, they will get an empty list.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
> I'm using Visual Web Developer with SQL 2005 Express. I have a database
> that I created for my website and everything works the way it should on my
> computer. My ISP says the only way they can create the database on their
> side is to restore it from a backup. I created a full backup by right
> clicking on the database and going through all the steps. When my ISP
> tries to restore it, they're telling me that the database is empty. They
> are using SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00.
> I'm using Win Xp 32bit. There are no error messages on either my side or
> their's when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>
>
|||Thanks for the suggestions. I ended up copying the data over to tables they
created.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OmZWtQdrHHA.4828@.TK2MSFTNGP04.phx.gbl...
>A database can never be completely empty, so this makes me wonder how your
>ISP is determining it is 'empty'. My guess is that they are not SQL Server
>Professionals, and are jumping to wild conclusions.
> Perhaps they are just running sp_help to see a list of objects, or
> expanding the tables folder in SSMS Object Explorer, and if they don't
> have the appropriate permissions, they will get an empty list.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
>
>

Restore to SQL 2005?

Hello,
I'm using SQL 2005 Express and I have a database that I've created a backup
for, but when my ISP tries to restore it with the full version of SQL 2005,
they say the tables are missing. Any ideas why I can restore it with the
Express edition and they can't do it with the full one?
Thanks
JimWhat exactly is meant by "the tables are missing"? Is it that the database
restores successfully but is empty?
On possible cause is that the database backup file contains multiple backups
and only the first (oldest) is restored by default.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>|||Hi Jim
Can you give us more details?
What does 'full version of SQL 2005' mean? Is it standard or enterprise?
32-bit or 64-bit? What OS?
WHO says tables are missing? What tables? Is there an error message? When
does it show up? What does it say exactly?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm using SQL 2005 Express and I have a database that I've created a
> backup for, but when my ISP tries to restore it with the full version of
> SQL 2005, they say the tables are missing. Any ideas why I can restore it
> with the Express edition and they can't do it with the full one?
> Thanks
> Jim
>|||I'm using Visual Web Developer with SQL 2005 Express. I have a database that
I created for my website and everything works the way it should on my
computer. My ISP says the only way they can create the database on their
side is to restore it from a backup. I created a full backup by right
clicking on the database and going through all the steps. When my ISP tries
to restore it, they're telling me that the database is empty. They are using
SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00. I'm using Win
Xp 32bit. There are no error messages on either my side or their's when
creating or restoring the tables.
Jim
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi Jim
> Can you give us more details?
> What does 'full version of SQL 2005' mean? Is it standard or enterprise?
> 32-bit or 64-bit? What OS?
> WHO says tables are missing? What tables? Is there an error message? When
> does it show up? What does it say exactly?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%235uFQKUrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>
>|||Make sure that you backup to a new file (a file which doesn't exist) so you
don't by mistake append
the backup and the always restore the most oldest backup. Or check out the I
NIT option of the backup
command.
Also, don't take your ISP word that the db is empty. Login using a query win
dow (if at all possible)
and verify for yourself. Also, make sure this isn't just a side effect of lo
gin - username mismatch
(read about sp_change_users_login and Google for sp_help_revlogins).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jim" <jim@.gordonferon.com> wrote in message news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
> I'm using Visual Web Developer with SQL 2005 Express. I have a database th
at I created for my
> website and everything works the way it should on my computer. My ISP says
the only way they can
> create the database on their side is to restore it from a backup. I create
d a full backup by right
> clicking on the database and going through all the steps. When my ISP trie
s to restore it, they're
> telling me that the database is empty. They are using SQL 2005 Standard ve
rsion 9.0.3 & my version
> is 9.00.2047.00. I'm using Win Xp 32bit. There are no error messages on ei
ther my side or their's
> when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>
>[/vbcol]|||A database can never be completely empty, so this makes me wonder how your
ISP is determining it is 'empty'. My guess is that they are not SQL Server
Professionals, and are jumping to wild conclusions.
Perhaps they are just running sp_help to see a list of objects, or expanding
the tables folder in SSMS Object Explorer, and if they don't have the
appropriate permissions, they will get an empty list.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jim" <jim@.gordonferon.com> wrote in message
news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
> I'm using Visual Web Developer with SQL 2005 Express. I have a database
> that I created for my website and everything works the way it should on my
> computer. My ISP says the only way they can create the database on their
> side is to restore it from a backup. I created a full backup by right
> clicking on the database and going through all the steps. When my ISP
> tries to restore it, they're telling me that the database is empty. They
> are using SQL 2005 Standard version 9.0.3 & my version is 9.00.2047.00.
> I'm using Win Xp 32bit. There are no error messages on either my side or
> their's when creating or restoring the tables.
> Jim
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OTM%23wWVrHHA.1208@.TK2MSFTNGP02.phx.gbl...
>
>|||Thanks for the suggestions. I ended up copying the data over to tables they
created.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OmZWtQdrHHA.4828@.TK2MSFTNGP04.phx.gbl...
>A database can never be completely empty, so this makes me wonder how your
>ISP is determining it is 'empty'. My guess is that they are not SQL Server
>Professionals, and are jumping to wild conclusions.
> Perhaps they are just running sp_help to see a list of objects, or
> expanding the tables folder in SSMS Object Explorer, and if they don't
> have the appropriate permissions, they will get an empty list.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Jim" <jim@.gordonferon.com> wrote in message
> news:O4X7j6crHHA.4300@.TK2MSFTNGP05.phx.gbl...
>
>

restore to a specific transaction in the log

i have been doing tests and i cannot manage to do this. here is what i have done and am trying to do:

I created a full backup of the adventureworks database

I made some updates to tables.

I created a differential backup.

I done an update update to another table, dropped a table, and done another update.

Lets say in a production envrionment i just realised the table was dropped and i wanted to go to the transaction just before the table was dropped. is this possible, or are all transactions in the log going to be lost? All i have found remotely related to this are marked transactions, but they are not what i am looking for. i want a list of transactions in the log and be able to select one as the backup point ( via SSMS or TSQL). i am not looking for a point in time restore.

Thanks all

You can use the RESTORE syntax for point-in-time RESTORE to have the log stop at whatever time or LSN you choose. See the STOPAT clause for the RESTORE statement in books-online.

|||yea, i have since discovered that there is no way to restore to a specific transaction, i have to know the time of the transaction i want and restore to that time. pity, it would be a great feature. thanks for the reply.

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR
1. You don't have to create the database before RESTORE. The restore process will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command and the error message(s)
returned by the database engine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl...
>I have backed up our production database to a file and would like to restore it to a test database
>I created. This seems very simple but I seem to be missing something. I am using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>
|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
Paul G
Software engineer.
"TRD" wrote:

> I have backed up our production database to a file and would like to restore
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>
|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...[vbcol=seagreen]
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR1. You don't have to create the database before RESTORE. The restore process
will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command
and the error message(s)
returned by the database engine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl.
.
>I have backed up our production database to a file and would like to restor
e it to a test database
>I created. This seems very simple but I seem to be missing something. I am
using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
Paul G
Software engineer.
"TRD" wrote:

> I have backed up our production database to a file and would like to resto
re
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...[vbcol=seagreen]
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:
>

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR1. You don't have to create the database before RESTORE. The restore process will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command and the error message(s)
returned by the database engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl...
>I have backed up our production database to a file and would like to restore it to a test database
>I created. This seems very simple but I seem to be missing something. I am using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
--
Paul G
Software engineer.
"TRD" wrote:
> I have backed up our production database to a file and would like to restore
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:
>> I have backed up our production database to a file and would like to
>> restore
>> it to a test database I created. This seems very simple but I seem to be
>> missing something. I am using query analyzer. Could someone please assist
>> me?
>>
>> TIA
>> TR
>>

Restore Sql Express (2005) to Sql Server 2000

Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.

Doesn't work.

When I launch the restore, I get the following error dialog:

Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK

Only got one backup device selected. Is this possible or not?

I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.

TIA,

Rick

No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps

(a) Create Script of Database Objects from 2005

(c) Create new database in sql server 2000

(d) run the objects script in 2000

(e) use DTS to transfer data from 2005 to 2000

Madhu

|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?
|||

Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.

Madhu

|||Madju,

Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).

Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:

Cannot perform requested task because full-text memory manager is not initialized

Not sure what that pertains to.

(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?

(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.

|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.

This method worked much smoother. I also had to run the DTS on the server itself for it to work.

Restore Sql Express (2005) to Sql Server 2000

Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.

Doesn't work.

When I launch the restore, I get the following error dialog:

Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK

Only got one backup device selected. Is this possible or not?

I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.

TIA,

Rick

No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps

(a) Create Script of Database Objects from 2005

(c) Create new database in sql server 2000

(d) run the objects script in 2000

(e) use DTS to transfer data from 2005 to 2000

Madhu

|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?
|||

Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.

Madhu

|||Madju,

Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).

Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:

Cannot perform requested task because full-text memory manager is not initialized

Not sure what that pertains to.

(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?

(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.

|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.

This method worked much smoother. I also had to run the DTS on the server itself for it to work.

Restore sp_grantdbaccess

I need to know how to restore a stored procedure that was deleted. I created
a new one, but it is set to user type and doesn't work. I don't know how to
pull it out of a master db backup. Any help would be GREAT.
Thanks,
FrankI assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
installation. You will fine one or a few which installs the system procedures and based on that code
within that script you will see how to add it as a system procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>I need to know how to restore a stored procedure that was deleted. I created
> a new one, but it is set to user type and doesn't work. I don't know how to
> pull it out of a master db backup. Any help would be GREAT.
> Thanks,
> Frank|||Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
user type and not system. Tells me i don't have access to active directory.
"Tibor Karaszi" wrote:
> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
> installation. You will fine one or a few which installs the system procedures and based on that code
> within that script you will see how to add it as a system procedures.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
> >I need to know how to restore a stored procedure that was deleted. I created
> > a new one, but it is set to user type and doesn't work. I don't know how to
> > pull it out of a master db backup. Any help would be GREAT.
> > Thanks,
> > Frank
>|||You need to dig further into that script file. In the beginning of the script, it should execute a
system stored procedure which result in every following procedures created are classified as system
stored procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:26C50283-2EC2-4A5D-8436-1027E5B79D08@.microsoft.com...
> Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
> user type and not system. Tells me i don't have access to active directory.
> "Tibor Karaszi" wrote:
>> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL
>> Server
>> installation. You will fine one or a few which installs the system procedures and based on that
>> code
>> within that script you will see how to add it as a system procedures.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>> >I need to know how to restore a stored procedure that was deleted. I created
>> > a new one, but it is set to user type and doesn't work. I don't know how to
>> > pull it out of a master db backup. Any help would be GREAT.
>> > Thanks,
>> > Frank
>>

Restore runs longer in batch mode

I have created a Job that uses "cmcexec" to execute a SQL script. This Job
brings SQL to single_user mode then restores from a directory that is local
to the server.
1) When I run this restore in the GUI it takes 15 minutes but is still
executing
(1 + hours).
2) How can I tell that it is actually running and not hung up?
Hi,
From Query Analyzer, Could you execute the syetm procedure sp_who2 and see
the status of the backup process.
Repeatedly execute sp2 and check if the CPU time and DISK IO are increasing
for backup process. If the value is increasing
then the process is active.
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:E13E54D2-C77F-4A44-BDAB-630166185032@.microsoft.com...
> I have created a Job that uses "cmcexec" to execute a SQL script. This
Job
> brings SQL to single_user mode then restores from a directory that is
local
> to the server.
> 1) When I run this restore in the GUI it takes 15 minutes but is still
> executing
> (1 + hours).
> 2) How can I tell that it is actually running and not hung up?

Restore only data on a database

Hi to all,
I have created a program that weekly restore a database with differential backup.

It works fine the only thing is that when I restore the database the new user are dropped.

There is a way to restore only the data ?

code:

use master
ALTER DATABASE GOROB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE GOROB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\GOROBFULL'
with NORECOVERY

RESTORE DATABASE GOROB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Differential'
WITH FILE =1 ,
RECOVERY

ALTER DATABASE GOROB SET MULTI_USER

Thank you in advance

And data added to the database SINCE the differential backup will be lost when you do a restore.

Database Users are 'data' in the database.

You may wish to add a job step to script out the Users when you make the differential backup, and then add a job step to the restore process to run that script.

|||

Anrie thank you

for your reply, there is a way to look insite the users of the database and grant the access to it?

My problem is that when I do the restore and some database administrator create new user on a database

it cannot access to data .

So I have thinked to grant the access to each user of the db .

The thinks is how this cold be done?

Restore of Database Terminates

I have 58 GB of free space on my server and I created a database and tried t
o
restore a 500 mb database and it tells me that it requires 85071298560.
The database i am restoring is quite small and I have space. I dont
understand whats the problem.Please post result fo following. Preferably, the result should be nicely
formatted to make it easy to read.:
restore filelistonly from disk =
'File Name of Database Backup File With Full Path'
"Lontae Jones" wrote:

> I have 58 GB of free space on my server and I created a database and tried
to
> restore a 500 mb database and it tells me that it requires 85071298560.
> The database i am restoring is quite small and I have space. I dont
> understand whats the problem.|||My backup is 534 mb and below is the information from the filelist and it
shows something different
DB1_Data_Data D:\relic\db\Data\db1_Data.MDF D PRIMARY 31055347712 3518437208
0640
Db1_Log D:\relic\db\Data\DB1_Log.LDF L NULL 54018048000 35184372080640
"Absar Ahmad" wrote:
> Please post result fo following. Preferably, the result should be nicely
> formatted to make it easy to read.:
> restore filelistonly from disk =
> 'File Name of Database Backup File With Full Path'
> "Lontae Jones" wrote:
>|||It is obvious from this information that the File Size of the Source Databas
e
is about 28.9 GB for the Data File and 50.3 GB for the Log File. When you tr
y
to restore from its backup, SQL Server will also need to create files of sam
e
size on the destination server. As the Free Space is only 58 GB, the restore
is failing.
Your backup size is so small because the source database has very little dat
a.
So you have following options:
1. Get the source database shrinked, take a backup and restore from that
backup.
or
2. Create about 80 GB Free Space.
Best of Luck.
"Lontae Jones" wrote:

> My backup is 534 mb and below is the information from the filelist and it
> shows something different
> DB1_Data_Data D:\relic\db\Data\db1_Data.MDF D PRIMARY 31055347712 35184372
080640
> Db1_Log D:\relic\db\Data\DB1_Log.LDF L NULL 54018048000 35184372080640|||Yeah I shrank the log and data. Thanks
"Absar Ahmad" wrote:

> It is obvious from this information that the File Size of the Source Datab
ase
> is about 28.9 GB for the Data File and 50.3 GB for the Log File. When you
try
> to restore from its backup, SQL Server will also need to create files of s
ame
> size on the destination server. As the Free Space is only 58 GB, the resto
re
> is failing.
> Your backup size is so small because the source database has very little d
ata.
> So you have following options:
> 1. Get the source database shrinked, take a backup and restore from that
> backup.
> or
> 2. Create about 80 GB Free Space.
> Best of Luck.
> "Lontae Jones" wrote:
>
>