Reducing a database
in [Server Configuration]
|
From: tempman on 3 Dec 2009 13:21 Hello, I want to reduce a database in SQL Server 2000. The problem is that it has the datafiles with a initial size very high. I've done a logical backup, done a command script for generating the database, but I've changed the initial size of database to a size very short in that script. Then, I create the database with that script, but the problem is that when I go to restore the logical backup, it shows again the initial size of datafiles that it had before ... How can I reduce that database? I'm sorry for my bad english, I hope that you can understand me. Thanks beforehand. Cheers...
From: Tibor Karaszi on 3 Dec 2009 14:39 You can't reduce the size of a database using backup/restore. The restore process will create (overwrite if already exist) with same size as it had originally. So, forget about backup and restore. If you really need to reduce size, use DBCC SHRINKFILE. More info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "tempman" <tempman(a)discussions.microsoft.com> wrote in message news:D747EFA6-1023-4DD9-B522-82C15DD87ACB(a)microsoft.com... > Hello, > I want to reduce a database in SQL Server 2000. The problem is that it has > the datafiles with a initial size very high. I've done a logical backup, > done > a command script for generating the database, but I've changed the initial > size of database to a size very short in that script. Then, I create the > database with that script, but the problem is that when I go to restore > the > logical backup, it shows again the initial size of datafiles that it had > before ... How can I reduce that database? > I'm sorry for my bad english, I hope that you can understand me. > Thanks beforehand. > > Cheers...
From: tempman on 3 Dec 2009 16:04 Thank you very much Tibor. Yes, I know dbcc shrinkfile, I use it for reducing transaction log of my database. I were using dbcc shrinkdatabase, buf I had that problem too, when I did restore, it returned to the same size as it had originally. If I do restore, and I do dbcc shrinkfile, for each datafile of my database (the database has many datafiles ...), I will can reduce the datafiles? Although its original size was more great :-? Thanks beforehand. Cheers... "Tibor Karaszi" wrote: > You can't reduce the size of a database using backup/restore. The restore > process will create (overwrite if already exist) with same size as it had > originally. So, forget about backup and restore. If you really need to > reduce size, use DBCC SHRINKFILE. More info here: > http://www.karaszi.com/SQLServer/info_dont_shrink.asp > > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "tempman" <tempman(a)discussions.microsoft.com> wrote in message > news:D747EFA6-1023-4DD9-B522-82C15DD87ACB(a)microsoft.com... > > Hello, > > I want to reduce a database in SQL Server 2000. The problem is that it has > > the datafiles with a initial size very high. I've done a logical backup, > > done > > a command script for generating the database, but I've changed the initial > > size of database to a size very short in that script. Then, I create the > > database with that script, but the problem is that when I go to restore > > the > > logical backup, it shows again the initial size of datafiles that it had > > before ... How can I reduce that database? > > I'm sorry for my bad english, I hope that you can understand me. > > Thanks beforehand. > > > > Cheers... > > . >
From: Tibor Karaszi on 3 Dec 2009 16:47 Restore will re-create the existing database (if exists) to the same size as the one you did a backup of. I.e., you cannot make a db smaller using backup/restore. So, shrink either before backup or after restore is the way to go. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "tempman" <tempman(a)discussions.microsoft.com> wrote in message news:AE23ACFF-74C8-45D2-889C-C396962A5189(a)microsoft.com... > Thank you very much Tibor. > Yes, I know dbcc shrinkfile, I use it for reducing transaction log of my > database. I were using dbcc shrinkdatabase, buf I had that problem too, > when > I did restore, it returned to the same size as it had originally. > If I do restore, and I do dbcc shrinkfile, for each datafile of my > database > (the database has many datafiles ...), I will can reduce the datafiles? > Although its original size was more great :-? > Thanks beforehand. > > Cheers... > > "Tibor Karaszi" wrote: > >> You can't reduce the size of a database using backup/restore. The restore >> process will create (overwrite if already exist) with same size as it had >> originally. So, forget about backup and restore. If you really need to >> reduce size, use DBCC SHRINKFILE. More info here: >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "tempman" <tempman(a)discussions.microsoft.com> wrote in message >> news:D747EFA6-1023-4DD9-B522-82C15DD87ACB(a)microsoft.com... >> > Hello, >> > I want to reduce a database in SQL Server 2000. The problem is that it >> > has >> > the datafiles with a initial size very high. I've done a logical >> > backup, >> > done >> > a command script for generating the database, but I've changed the >> > initial >> > size of database to a size very short in that script. Then, I create >> > the >> > database with that script, but the problem is that when I go to restore >> > the >> > logical backup, it shows again the initial size of datafiles that it >> > had >> > before ... How can I reduce that database? >> > I'm sorry for my bad english, I hope that you can understand me. >> > Thanks beforehand. >> > >> > Cheers... >> >> . >>
From: Jay on 3 Dec 2009 18:44 Yes. Just don't do a restore after the shrinkfile. "tempman" <tempman(a)discussions.microsoft.com> wrote in message news:AE23ACFF-74C8-45D2-889C-C396962A5189(a)microsoft.com... > Thank you very much Tibor. > Yes, I know dbcc shrinkfile, I use it for reducing transaction log of my > database. I were using dbcc shrinkdatabase, buf I had that problem too, > when > I did restore, it returned to the same size as it had originally. > If I do restore, and I do dbcc shrinkfile, for each datafile of my > database > (the database has many datafiles ...), I will can reduce the datafiles? > Although its original size was more great :-? > Thanks beforehand. > > Cheers... > > "Tibor Karaszi" wrote: > >> You can't reduce the size of a database using backup/restore. The restore >> process will create (overwrite if already exist) with same size as it had >> originally. So, forget about backup and restore. If you really need to >> reduce size, use DBCC SHRINKFILE. More info here: >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "tempman" <tempman(a)discussions.microsoft.com> wrote in message >> news:D747EFA6-1023-4DD9-B522-82C15DD87ACB(a)microsoft.com... >> > Hello, >> > I want to reduce a database in SQL Server 2000. The problem is that it >> > has >> > the datafiles with a initial size very high. I've done a logical >> > backup, >> > done >> > a command script for generating the database, but I've changed the >> > initial >> > size of database to a size very short in that script. Then, I create >> > the >> > database with that script, but the problem is that when I go to restore >> > the >> > logical backup, it shows again the initial size of datafiles that it >> > had >> > before ... How can I reduce that database? >> > I'm sorry for my bad english, I hope that you can understand me. >> > Thanks beforehand. >> > >> > Cheers... >> >> . >>
|
Next
|
Last
Pages: 1 2 Prev: SSRS sevice start with error after clean 2008 install? Next: very slow SQL client |