Restoring a BAK Do I Need the Schema ?

Hello everyone,
I'm new to SQL Server 2005. I'm using the Express version.
A co-worker sent me a BAK file which I need to restore. My question is, do I
need to have the database structure already in place before I can do the
restore? I was wondering if there's a way to simply restore the BAK file
into an empty database and have underlying tables/sprocs automatically
created as part of the restore process. Is this possible?
Thanks very much.
Yes. You can either just restore the bak file and let it create what it
needs or you can
1. Create empty, new database with the same name
2. Do a backup of the same name as your backup
3. Put the co-worker's backup in place of the backup in 2
4. Restore the backup and tell it to overwrite an existing DB.
Alan Foxmore wrote:
> Hello everyone,
> I'm new to SQL Server 2005. I'm using the Express version.
> A co-worker sent me a BAK file which I need to restore. My question is, do I
> need to have the database structure already in place before I can do the
> restore? I was wondering if there's a way to simply restore the BAK file
> into an empty database and have underlying tables/sprocs automatically
> created as part of the restore process. Is this possible?
> Thanks very much.
>
|||Actually, step 1 to 3 are unnecessary and a waste of time. The restore process will create the
database for you.
You might need to investigate what files were used for the original database (RESTORE HEADERONLY and
RESTORE FILELISTONLY) and then specify new physical filenames when you restore (using the MOVE
option of the RESTORE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MSDN" <Grunt@.newsgroup.nospam> wrote in message news:eAc3EaxkHHA.492@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Yes. You can either just restore the bak file and let it create what it needs or you can
> 1. Create empty, new database with the same name
> 2. Do a backup of the same name as your backup
> 3. Put the co-worker's backup in place of the backup in 2
> 4. Restore the backup and tell it to overwrite an existing DB.
>
> Alan Foxmore wrote:
|||I always wondered about that. I've taken to just restoring the file as I
suggested. However, when I inherited the SQL stuff that was the
technique the current admin used and recommended. It didn't make much
sense to me but at the time he new much more than I did <G>.
Tibor Karaszi wrote:
> Actually, step 1 to 3 are unnecessary and a waste of time. The restore
> process will create the database for you.
> You might need to investigate what files were used for the original
> database (RESTORE HEADERONLY and RESTORE FILELISTONLY) and then specify
> new physical filenames when you restore (using the MOVE option of the
> RESTORE command).
>
|||Perhaps that person had worked with the old architecture (6.5 and older), where you actually *had*
to create the database first?
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MSDN" <Grunt@.newsgroup.nospam> wrote in message news:%23e7CJmykHHA.568@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>I always wondered about that. I've taken to just restoring the file as I suggested. However, when
>I inherited the SQL stuff that was the technique the current admin used and recommended. It didn't
>make much sense to me but at the time he new much more than I did <G>.
>
> Tibor Karaszi wrote:
|||That would explain it. I picked it up when I migrated a SQL 7 to SQL
2000. He did have 6.5 or earlier experience. Thanks.
Tibor Karaszi wrote:
> Perhaps that person had worked with the old architecture (6.5 and
> older), where you actually *had* to create the database first?
> :-)
>
|||Thank you all... It works now. I had to change to the account under which
the service was running to the Local Account.
Thanks again.
"Alan Foxmore" <afoxmore@.yahoo.com> wrote in message
news:upPyLKxkHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hello everyone,
> I'm new to SQL Server 2005. I'm using the Express version.
> A co-worker sent me a BAK file which I need to restore. My question is, do
> I need to have the database structure already in place before I can do the
> restore? I was wondering if there's a way to simply restore the BAK file
> into an empty database and have underlying tables/sprocs automatically
> created as part of the restore process. Is this possible?
> Thanks very much.
>

.MDF recovery after data failure

Hi,
I recently had a problem with a database which resulted in restoring the
database from backup. The .mdf and .ldf files were restored, however I coul
d
not reattach the database.
I attempted to create a new database, stop SQL Server, and copy over the
.mdf and .ldf files which put the database into Suspect/Online. I was not
able to bring the database back online.
Further, after changing the status in to Emergency Mode and attempting a
DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
error with some extra data.
I also tried a third party .MDF data recovery software which yielded 100KB
worth of one table instead for a 100MB database. Any advice?Hi
I am not sure where the problem is if you restored the database from backup!
I can only assume there was data loss because either didn't have full
recovery mode and/or log backups available to do point in time recovery?
If your database is suspect you could try, but make sure that you copy the
files first
http://tinyurl.com/jhv9k
John
"t-o-m-h-VA" wrote:

> Hi,
> I recently had a problem with a database which resulted in restoring the
> database from backup. The .mdf and .ldf files were restored, however I co
uld
> not reattach the database.
> I attempted to create a new database, stop SQL Server, and copy over the
> .mdf and .ldf files which put the database into Suspect/Online. I was not
> able to bring the database back online.
> Further, after changing the status in to Emergency Mode and attempting a
> DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> error with some extra data.
> I also tried a third party .MDF data recovery software which yielded 100KB
> worth of one table instead for a 100MB database. Any advice?|||The recovered MDF file was pulled from disk backup and did not have full
recovery, it was the exact file from the server. The log file is also there
but i tried the steps in the URL,
1.) Reset the status from "Suspect':
EXEC sp_resetstatus 'myDatabaseName'
2.) Set the database property "allow updates':
exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
3.) Set the database to "Emergency Mode":
UPDATE master..sysdatabases SET status=-32768 WHERE
name='myDatabseName'
4.) Rebuild the database log file:
dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ myDatabaseName2_log.LDF')
However, when I try to do a DTS Import it will not authenicate the SYSTEM
user saying initalization failed. I suspect the .mdf file is corrupt
somehow, is there any recovery tools to view the MDF file?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I am not sure where the problem is if you restored the database from backu
p!
> I can only assume there was data loss because either didn't have full
> recovery mode and/or log backups available to do point in time recovery?
> If your database is suspect you could try, but make sure that you copy the
> files first
> http://tinyurl.com/jhv9k
> John
>
> "t-o-m-h-VA" wrote:
>|||Hi
Check to see if you have orphaned users see
121120120" target="_blank">http://support.microsoft.com/defaul...r />
121120120 also there is link regarding resolving permissions issues.
John
"t-o-m-h-VA" wrote:
[vbcol=seagreen]
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also the
re
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
>|||>is there any recovery tools to view the MDF file?
http://www.officerecovery.com/mssql/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"t-o-m-h-VA" <tomhVA@.discussions.microsoft.com> wrote in message
news:31D481E6-B762-4090-8A79-E469E3CD661D@.microsoft.com...[vbcol=seagreen]
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also the
re
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
>

.MDF recovery after data failure

Hi,
I recently had a problem with a database which resulted in restoring the
database from backup. The .mdf and .ldf files were restored, however I could
not reattach the database.
I attempted to create a new database, stop SQL Server, and copy over the
.mdf and .ldf files which put the database into Suspect/Online. I was not
able to bring the database back online.
Further, after changing the status in to Emergency Mode and attempting a
DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
error with some extra data.
I also tried a third party .MDF data recovery software which yielded 100KB
worth of one table instead for a 100MB database. Any advice?Hi
I am not sure where the problem is if you restored the database from backup!
I can only assume there was data loss because either didn't have full
recovery mode and/or log backups available to do point in time recovery?
If your database is suspect you could try, but make sure that you copy the
files first
http://tinyurl.com/jhv9k
John
"t-o-m-h-VA" wrote:
> Hi,
> I recently had a problem with a database which resulted in restoring the
> database from backup. The .mdf and .ldf files were restored, however I could
> not reattach the database.
> I attempted to create a new database, stop SQL Server, and copy over the
> .mdf and .ldf files which put the database into Suspect/Online. I was not
> able to bring the database back online.
> Further, after changing the status in to Emergency Mode and attempting a
> DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> error with some extra data.
> I also tried a third party .MDF data recovery software which yielded 100KB
> worth of one table instead for a 100MB database. Any advice?|||The recovered MDF file was pulled from disk backup and did not have full
recovery, it was the exact file from the server. The log file is also there
but i tried the steps in the URL,
1.) Reset the status from "Suspect':
EXEC sp_resetstatus 'myDatabaseName'
2.) Set the database property "allow updates':
exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
3.) Set the database to "Emergency Mode":
UPDATE master..sysdatabases SET status=-32768 WHERE
name='myDatabseName'
4.) Rebuild the database log file:
dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ myDatabaseName2_log.LDF')
However, when I try to do a DTS Import it will not authenicate the SYSTEM
user saying initalization failed. I suspect the .mdf file is corrupt
somehow, is there any recovery tools to view the MDF file?
"John Bell" wrote:
> Hi
> I am not sure where the problem is if you restored the database from backup!
> I can only assume there was data loss because either didn't have full
> recovery mode and/or log backups available to do point in time recovery?
> If your database is suspect you could try, but make sure that you copy the
> files first
> http://tinyurl.com/jhv9k
> John
>
> "t-o-m-h-VA" wrote:
> > Hi,
> >
> > I recently had a problem with a database which resulted in restoring the
> > database from backup. The .mdf and .ldf files were restored, however I could
> > not reattach the database.
> >
> > I attempted to create a new database, stop SQL Server, and copy over the
> > .mdf and .ldf files which put the database into Suspect/Online. I was not
> > able to bring the database back online.
> >
> > Further, after changing the status in to Emergency Mode and attempting a
> > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> > error with some extra data.
> >
> > I also tried a third party .MDF data recovery software which yielded 100KB
> > worth of one table instead for a 100MB database. Any advice?|||Hi
Check to see if you have orphaned users see
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#XSLTH3182121121120121120120 also there is link regarding resolving permissions issues.
John
"t-o-m-h-VA" wrote:
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
> > Hi
> >
> > I am not sure where the problem is if you restored the database from backup!
> > I can only assume there was data loss because either didn't have full
> > recovery mode and/or log backups available to do point in time recovery?
> >
> > If your database is suspect you could try, but make sure that you copy the
> > files first
> > http://tinyurl.com/jhv9k
> >
> > John
> >
> >
> >
> > "t-o-m-h-VA" wrote:
> >
> > > Hi,
> > >
> > > I recently had a problem with a database which resulted in restoring the
> > > database from backup. The .mdf and .ldf files were restored, however I could
> > > not reattach the database.
> > >
> > > I attempted to create a new database, stop SQL Server, and copy over the
> > > .mdf and .ldf files which put the database into Suspect/Online. I was not
> > > able to bring the database back online.
> > >
> > > Further, after changing the status in to Emergency Mode and attempting a
> > > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> > > error with some extra data.
> > >
> > > I also tried a third party .MDF data recovery software which yielded 100KB
> > > worth of one table instead for a 100MB database. Any advice?|||>is there any recovery tools to view the MDF file?
http://www.officerecovery.com/mssql/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"t-o-m-h-VA" <tomhVA@.discussions.microsoft.com> wrote in message
news:31D481E6-B762-4090-8A79-E469E3CD661D@.microsoft.com...
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
>> Hi
>> I am not sure where the problem is if you restored the database from backup!
>> I can only assume there was data loss because either didn't have full
>> recovery mode and/or log backups available to do point in time recovery?
>> If your database is suspect you could try, but make sure that you copy the
>> files first
>> http://tinyurl.com/jhv9k
>> John
>>
>> "t-o-m-h-VA" wrote:
>> > Hi,
>> >
>> > I recently had a problem with a database which resulted in restoring the
>> > database from backup. The .mdf and .ldf files were restored, however I could
>> > not reattach the database.
>> >
>> > I attempted to create a new database, stop SQL Server, and copy over the
>> > .mdf and .ldf files which put the database into Suspect/Online. I was not
>> > able to bring the database back online.
>> >
>> > Further, after changing the status in to Emergency Mode and attempting a
>> > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
>> > error with some extra data.
>> >
>> > I also tried a third party .MDF data recovery software which yielded 100KB
>> > worth of one table instead for a 100MB database. Any advice?

.MDF recovery after data failure

Hi,
I recently had a problem with a database which resulted in restoring the
database from backup. The .mdf and .ldf files were restored, however I could
not reattach the database.
I attempted to create a new database, stop SQL Server, and copy over the
..mdf and .ldf files which put the database into Suspect/Online. I was not
able to bring the database back online.
Further, after changing the status in to Emergency Mode and attempting a
DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
error with some extra data.
I also tried a third party .MDF data recovery software which yielded 100KB
worth of one table instead for a 100MB database. Any advice?
Hi
I am not sure where the problem is if you restored the database from backup!
I can only assume there was data loss because either didn't have full
recovery mode and/or log backups available to do point in time recovery?
If your database is suspect you could try, but make sure that you copy the
files first
http://tinyurl.com/jhv9k
John
"t-o-m-h-VA" wrote:

> Hi,
> I recently had a problem with a database which resulted in restoring the
> database from backup. The .mdf and .ldf files were restored, however I could
> not reattach the database.
> I attempted to create a new database, stop SQL Server, and copy over the
> .mdf and .ldf files which put the database into Suspect/Online. I was not
> able to bring the database back online.
> Further, after changing the status in to Emergency Mode and attempting a
> DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> error with some extra data.
> I also tried a third party .MDF data recovery software which yielded 100KB
> worth of one table instead for a 100MB database. Any advice?
|||The recovered MDF file was pulled from disk backup and did not have full
recovery, it was the exact file from the server. The log file is also there
but i tried the steps in the URL,
1.) Reset the status from "Suspect':
EXEC sp_resetstatus 'myDatabaseName'
2.) Set the database property "allow updates':
exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
3.) Set the database to "Emergency Mode":
UPDATE master..sysdatabases SET status=-32768 WHERE
name='myDatabseName'
4.) Rebuild the database log file:
dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ myDatabaseName2_log.LDF')
However, when I try to do a DTS Import it will not authenicate the SYSTEM
user saying initalization failed. I suspect the .mdf file is corrupt
somehow, is there any recovery tools to view the MDF file?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I am not sure where the problem is if you restored the database from backup!
> I can only assume there was data loss because either didn't have full
> recovery mode and/or log backups available to do point in time recovery?
> If your database is suspect you could try, but make sure that you copy the
> files first
> http://tinyurl.com/jhv9k
> John
>
> "t-o-m-h-VA" wrote:
|||Hi
Check to see if you have orphaned users see
http://support.microsoft.com/default...21120121120120 also there is link regarding resolving permissions issues.
John
"t-o-m-h-VA" wrote:
[vbcol=seagreen]
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
|||>is there any recovery tools to view the MDF file?
http://www.officerecovery.com/mssql/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"t-o-m-h-VA" <tomhVA@.discussions.microsoft.com> wrote in message
news:31D481E6-B762-4090-8A79-E469E3CD661D@.microsoft.com...[vbcol=seagreen]
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:

**Restoring just the data part of a DB **

Hi
I'm working with SQL 2000,and I've a backup of a full recovery DB from
another server,how can I restore this DB on my server without restoring the
log file,cause it needs a lot space?
Any help would be thankful.You can't. See my prior post (no need to re-post to a new thread). Shrink th
e database before you do
the backup, or make sure you have enough disk space to do the restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M R" <rezvani@.parskhazar.net> wrote in message news:%23tBM1SfVFHA.3076@.TK2MSFTNGP10.phx.gb
l...
> Hi
> I'm working with SQL 2000,and I've a backup of a full recovery DB from
> another server,how can I restore this DB on my server without restoring th
e
> log file,cause it needs a lot space?
> Any help would be thankful.
>|||but I've got the backup from another server which I've no access to that
server to shrink it first,
and I can't make more than avalable space now I have,so is there any other
solution?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uXytGXfVFHA.3944@.TK2MSFTNGP10.phx.gbl...
> You can't. See my prior post (no need to re-post to a new thread). Shrink
the database before you do
> the backup, or make sure you have enough disk space to do the restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "M R" <rezvani@.parskhazar.net> wrote in message
news:%23tBM1SfVFHA.3076@.TK2MSFTNGP10.phx.gbl...
the
>|||I'm afraid not. You could try and open a support case with MS and see if the
re exist any hack for
this, but there's no supported or documented way to accomplish this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M R" <rezvani@.parskhazar.net> wrote in message news:e90bd5fVFHA.616@.TK2MSFTNGP12.phx.gbl..
.
> but I've got the backup from another server which I've no access to that
> server to shrink it first,
> and I can't make more than avalable space now I have,so is there any other
> solution?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uXytGXfVFHA.3944@.TK2MSFTNGP10.phx.gbl...
> the database before you do
> news:%23tBM1SfVFHA.3076@.TK2MSFTNGP10.phx.gbl...
> the
>

**Restoring a db with size 167049KB**

Hi
I'm working with SQL2000 and I want to restore a db which I've got from
another server into my server with this size : 167049KB,and I've 11gb on
Drive E,but following error appearred after restoring:
"There's insufficient free space on disk volume 'e:' to create the
database.the database requires 28614066176 additional free bytes ,while only
2147155 968 bytes are available. Restore databse is terminated abnormally."
why?Can anybody help me?
Any help would be thankful.Do RESTORE FILELISTONLY and check the sum of size for the database files. Th
is is the size of the
database files when you took the backup and this is the space you need to do
restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"M R" <rezvani@.parskhazar.net> wrote in message news:OTxT%23eeVFHA.3424@.TK2MSFTNGP09.phx.gb
l...
> Hi
> I'm working with SQL2000 and I want to restore a db which I've got from
> another server into my server with this size : 167049KB,and I've 11gb on
> Drive E,but following error appearred after restoring:
> "There's insufficient free space on disk volume 'e:' to create the
> database.the database requires 28614066176 additional free bytes ,while on
ly
> 2147155 968 bytes are available. Restore databse is terminated abnormally.
"
> why?Can anybody help me?
> Any help would be thankful.
>|||Looks like the database you backed up might have lot of free space in the
data file (.mdf/ .ndf) or/and Huge Log file(.ldf).Check the original
database size. Just looking the backup size of the database could be
deceiving. The backup file is the size of the data in your database it does
not include the free space.
Try to backup the transaction log in the original database/ if you don't
need the transaction backup truncate the log file and shrink the log file
to a reasonable size. Do not shrink the data file (.mdf / .ndf) that might
create fragmentation on the data.
-regards
Sarav...
"M R" <rezvani@.parskhazar.net> wrote in message
news:OTxT%23eeVFHA.3424@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm working with SQL2000 and I want to restore a db which I've got from
> another server into my server with this size : 167049KB,and I've 11gb on
> Drive E,but following error appearred after restoring:
> "There's insufficient free space on disk volume 'e:' to create the
> database.the database requires 28614066176 additional free bytes ,while
only
> 2147155 968 bytes are available. Restore databse is terminated
abnormally."
> why?Can anybody help me?
> Any help would be thankful.
>

**Restore error**

Hi
I define a login name 'L1' in SQL2000 the owner of it's db called 'db1' .L1
should backup and restore (restoring as new db too) it's databes ,so I set
the L1 as a 'db_owner' and 'db_backupoperator' and 'database creator' in db1
and 'db_owner' in master too.now it can backup the database successfully but
when it wants to restore the db as a new one at the end of progressing the
blue bar of restoring following error appearred :
"Server user 'L1' is not a valid user in database 'db1' ,restore db is
terminated abnormally."
any help would be thakful.Hi
You don't say if these are on the same server! But this may help
http://support.microsoft.com/defaul...kb;en-us;240872
John
"M R" wrote:

> Hi
> I define a login name 'L1' in SQL2000 the owner of it's db called 'db1' .L
1
> should backup and restore (restoring as new db too) it's databes ,so I set
> the L1 as a 'db_owner' and 'db_backupoperator' and 'database creator' in d
b1
> and 'db_owner' in master too.now it can backup the database successfully b
ut
> when it wants to restore the db as a new one at the end of progressing the
> blue bar of restoring following error appearred :
> "Server user 'L1' is not a valid user in database 'db1' ,restore db is
> terminated abnormally."
> any help would be thakful.
>
>|||These are on the same server,
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:714B1D9E-A21F-4850-AFE6-8A85137E05DD@.microsoft.com...
> Hi
> You don't say if these are on the same server! But this may help
> http://support.microsoft.com/defaul...kb;en-us;240872
> John
> "M R" wrote:
>
.L1
set
db1
but
the|||Hi
If you change the owner of the database before backing up do you have
the same problem?
You may want to try dropping the user/login and re-create them.
John