I can't see a way of backing up all databases at once, or
the ability to choose multiple databases to back up. I am
in the process of moving a bunch of databases from one
laptop to another and I would rather not do it one at a
time.
Ideas?
Thanks in advance!!can you take SQL off line, copy database files to new server and attach
databases?
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
> I can't see a way of backing up all databases at once, or
> the ability to choose multiple databases to back up. I am
> in the process of moving a bunch of databases from one
> laptop to another and I would rather not do it one at a
> time.
> Ideas?
> Thanks in advance!!|||I can! In fact I am doing just that as I write this. I
had hoped there would be a way of doing a full backup and
a full restore bring over security intact, etc., but I
hearing there isn't. Seems rather manual this way!
Thanks!!
>--Original Message--
>can you take SQL off line, copy database files to new
server and attach
>databases?
>"Top Cat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
or
am
>
>.
>|||Hi,
Using a automated script you can
1. Backup all the user databases remotely to Laptop
2. After the backup restore it in Laptop ( You might need to start all the
databases in sinle_user using Alter database statement)
I have a script whckh will Backup remotely, please add the restore portion
in the script
Prerequisites
1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am|||I had to restore from a bunch of backup files in a directory a while ago, so
I write a stored proc which loops
the files and do the restore for each file. You can have a similar loop to p
erform the backup as well. But you
really need some TSQL programming skill to write the loop to perform the bac
kups, and you probably want to
verify my code which does the restore:
http://www.karaszi.com/sqlserver/ut...all_in_file.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
Multiple Data & Log Drives
I have a 20g database, the data and log files are spread
across multiple drives. What is the best way to get the
data back to 1 drive and the log back to 1 drive ?Use
Dbcc shrinkfile(Friendlyfilename, null, emptyfile) on a file
then
Alter database prod drop file friendlyfilename
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
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:05a401c3ce0d$9aafb910$a601280a@.phx.gbl...
> I have a 20g database, the data and log files are spread
> across multiple drives. What is the best way to get the
> data back to 1 drive and the log back to 1 drive ?|||Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
For the log files, shrink the second log file with DBCC SHRINKFILE and use
the EMPTYFILE option. Next, alter the database to drop the now-empty file.
As for the data files, you did not say if they are in the same filegroup.
If so, proceed as above. If not, you will have to drop the indexes in the
second file and re-create them in the primary file. Then, proceed as above.
HTH
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
For the log files, shrink the second =log file with DBCC SHRINKFILE and use the EMPTYFILE option. Next, alter the =database to drop the now-empty file.
As for the data files, you did not say =if they are in the same filegroup. If so, proceed as above. If not, you =will have to drop the indexes in the second file and re-create them in the =primary file. Then, proceed as above.
HTH
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:060001c3ce13$e0=c5dd70$a601280a@.phx.gbl...Let me be more specific:My database consists of 2 data files and 2 =log files:e:\data1.mdff:\data2.mdfg:\log1.ldff:\log2.=ldfHow can I get the data back to 1 file and 1 drive ? How can I get =the log bacl to 1 file and 1 drice.>--Original =Message-->I have a 20g database, the data and log files are spread >across =multiple drives. What is the best way to get the >data back to 1 drive and =the log back to 1 drive ?>.>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0--|||Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
OK, moving files is different. What you can do is detach and reattach the
database:
1. Detach the database, using sp_detach_db
2. Move the files to a different drive
3. Reattach the database, using sp_attach_db
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c3ce17$192e4be0$a601280a@.phx.gbl...
Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK, moving files is different. =What you can do is detach and reattach the database:
1. Detach the =database, using sp_detach_db
2. Move the files to =a different drive
3. Reattach the =database, using sp_attach_db
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:062b01c3ce17$19=2e4be0$a601280a@.phx.gbl...Yes they are in the primary file group. My problem is that I need to =move the primary data and log files. Those are the drives that are running =out of space. >--Original Message-->For the log =files, shrink the second log file with DBCC SHRINKFILE and use>the =EMPTYFILE option. Next, alter the database to drop the now-empty file.>>As for the data files, you did not say if they are =in the same filegroup.>If so, proceed as above. If not, you =will have to drop the indexes in the>second file and re-create them in =the primary file. Then, proceed as above.>>HTH>>-->Tom>>--=------>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Kell=y" wrote in message>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...>=Let me be more specific:>>My database consists of 2 data files =and 2 log files:>>e:\data1.mdf>f:\data2.mdf>>g:\=log1.ldf>f:\log2.ldf>>How can I get the data back to 1 file and 1 drive ?>>How can I =get the log bacl to 1 file and 1 drice.>>--Original Message-->I have a 20g database, the data and log files =are spread>across multiple drives. What is the best way to get the>data back to 1 drive and the log back to 1 drive ?>.>>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0--
across multiple drives. What is the best way to get the
data back to 1 drive and the log back to 1 drive ?Use
Dbcc shrinkfile(Friendlyfilename, null, emptyfile) on a file
then
Alter database prod drop file friendlyfilename
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
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:05a401c3ce0d$9aafb910$a601280a@.phx.gbl...
> I have a 20g database, the data and log files are spread
> across multiple drives. What is the best way to get the
> data back to 1 drive and the log back to 1 drive ?|||Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
For the log files, shrink the second log file with DBCC SHRINKFILE and use
the EMPTYFILE option. Next, alter the database to drop the now-empty file.
As for the data files, you did not say if they are in the same filegroup.
If so, proceed as above. If not, you will have to drop the indexes in the
second file and re-create them in the primary file. Then, proceed as above.
HTH
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
For the log files, shrink the second =log file with DBCC SHRINKFILE and use the EMPTYFILE option. Next, alter the =database to drop the now-empty file.
As for the data files, you did not say =if they are in the same filegroup. If so, proceed as above. If not, you =will have to drop the indexes in the second file and re-create them in the =primary file. Then, proceed as above.
HTH
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:060001c3ce13$e0=c5dd70$a601280a@.phx.gbl...Let me be more specific:My database consists of 2 data files and 2 =log files:e:\data1.mdff:\data2.mdfg:\log1.ldff:\log2.=ldfHow can I get the data back to 1 file and 1 drive ? How can I get =the log bacl to 1 file and 1 drice.>--Original =Message-->I have a 20g database, the data and log files are spread >across =multiple drives. What is the best way to get the >data back to 1 drive and =the log back to 1 drive ?>.>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0--|||Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
OK, moving files is different. What you can do is detach and reattach the
database:
1. Detach the database, using sp_detach_db
2. Move the files to a different drive
3. Reattach the database, using sp_attach_db
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c3ce17$192e4be0$a601280a@.phx.gbl...
Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK, moving files is different. =What you can do is detach and reattach the database:
1. Detach the =database, using sp_detach_db
2. Move the files to =a different drive
3. Reattach the =database, using sp_attach_db
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:062b01c3ce17$19=2e4be0$a601280a@.phx.gbl...Yes they are in the primary file group. My problem is that I need to =move the primary data and log files. Those are the drives that are running =out of space. >--Original Message-->For the log =files, shrink the second log file with DBCC SHRINKFILE and use>the =EMPTYFILE option. Next, alter the database to drop the now-empty file.>>As for the data files, you did not say if they are =in the same filegroup.>If so, proceed as above. If not, you =will have to drop the indexes in the>second file and re-create them in =the primary file. Then, proceed as above.>>HTH>>-->Tom>>--=------>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Kell=y" wrote in message>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...>=Let me be more specific:>>My database consists of 2 data files =and 2 log files:>>e:\data1.mdf>f:\data2.mdf>>g:\=log1.ldf>f:\log2.ldf>>How can I get the data back to 1 file and 1 drive ?>>How can I =get the log bacl to 1 file and 1 drice.>>--Original Message-->I have a 20g database, the data and log files =are spread>across multiple drives. What is the best way to get the>data back to 1 drive and the log back to 1 drive ?>.>>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0--
Subscribe to:
Posts (Atom)