Resore .bak to SQL Express

Our vendor has provided us a copy of our data in a .bak files from SQL server. I want to run reports against this data. I have installed Express as well as Server management studio.

I presume I need to restore the .BAK file before I can run any reports? If so how does one do this?

Thanks in advance

In the UI right click on a database and select restore, or review the restore command in Books On Line;

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp

Also here is a short example;

RESTORE DATABASE MyNwind
FROM MyNwind_1

Resizing margins programmatically?

Hello,

I am using

winforms reportviewer control for viewing .rdlc files (VS2005/SQL2005).

I would like to know if there was a way to control report header height

or top margin size, programmatically (or by using parameters).

Any

suggestion would be appreciated

Thank you!No, page header height or margin sizes cannot be changed programmatically. You'd need to have different reports that have different values on these properties.

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.
Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.
|||That will not do anything, as restore will create database files with same size as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>
|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.
|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...[vbcol=seagreen]
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance
>
>.
>
|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize th
e
partition, and shrinking the db does not change the allocation size. How ca
n
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same s
ize as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.ph
x.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And
then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in advan
ce
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance[vbcol=seagreen]
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but
I
>keep getting a msg saying that the drive is almost full. I cannot resize t
he
>partition, and shrinking the db does not change the allocation size. How c
an
>I reduce the allocation size and free up some disk space? Thanks in advanc
e
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.comsql

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same size as when you took the
backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>> leaves just enough room on the partition for the nightly backup files, but
> I
>> keep getting a msg saying that the drive is almost full. I cannot resize
> the
>> partition, and shrinking the db does not change the allocation size. How
> can
>> I reduce the allocation size and free up some disk space? Thanks in
> advance
>> for any suggestions.
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
>>I have a db with an allocated size of 24.3 GB, but only
2.9 GB used. This
>> leaves just enough room on the partition for the
nightly backup files, but I
>> keep getting a msg saying that the drive is almost
full. I cannot resize the
>> partition, and shrinking the db does not change the
allocation size. How can
>> I reduce the allocation size and free up some disk
space? Thanks in advance
>> for any suggestions.
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
--
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
--
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:
> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peter
KB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:

> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data files
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name>)
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making the
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over the
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

required to only move data/log files to new SAN, best method?

Hi, our sql 2000 server is configured in such a way that the sql server
resides on a box called Server_A, and has its data files present on a
storage area network called SAN_OLD. Due to limited space, we have to move
these data files across to a new storage area network called SAN_NEW and
have SERVER_A point to these relocated data files. Currently the data files
are located on D: and the log files on L: (logical drives on SAN_OLD). We
are thinking to place the data files on X: and the logs on Y: on SAN_NEW. So
in effect what we want to do is simply move the data/log files to a new
drive location. The environment uses replication, reporting services, and
analysis services.
How can this be achieved? I prefer to use Query Analyzer for this task. I
have to move all databases to SAN_NEW including the master, model etc. Do I
simply (from within query analyser):
- BACKUP PHASE -
--
- Make sure no users are attached to the SQL Server
- stop the SQL server agent
- Using Query Analyzer, perform the following on the MASTER, MODEL and MSDB
databases:
use MASTER
BACKUP DATABASE master
TO DISK = 'X:\backups\master_backup.bak'
-- now perform a backup of the model, msdb and tempdb databases
ALTER DATABASE {model, msdbb} SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
BACKUP DATABASE {model, msdb}
TO DISK = 'X:\backups\{model, msdb}_backup.bak'
- RESTORE PHASE -
--
- start command session
- type: net stop MSSQLServer
- start SQL in single user mode: sqlservr -m
- from within SQL Query Analyzer execute:
use MASTER
-- get the logical database and log file names
RESTORE FILELISTONLY
FROM DISK = 'X:\backups\master_backup.bak'
-- restore the data and log files (assuming logical names are
master_data/master_log)
RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
- Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
- from command line issue: net start MSSQLServer
- restart SQL Server
My questions are:
1. are my steps above ok?
2. how do i make sure that there are no users attached whilst im making the
backups?
3. what do i do with the TEMPDB?
4. when I do the RESTORE on the databases, does this simply restore over the
current attached databases? I.e. does a restore of the master database
simply overwrite the presently attached master database?
5. do I have to detach the databases before performing a database backup?
6. I am guessing i wont have any problems getting replication to kick off
again once i've rstored the databases. Also reporting service and analysis
service will work ok.
Is there anything I need to know prior to this move?
Many thanks.
peterKB article titled 'Moving SQL Server databases to a new location with
Detach/Attach' has very useful informtion.
http://support.microsoft.com/kb/224071/EN-US/
"peter walker" wrote:

> Hi, our sql 2000 server is configured in such a way that the sql server
> resides on a box called Server_A, and has its data files present on a
> storage area network called SAN_OLD. Due to limited space, we have to move
> these data files across to a new storage area network called SAN_NEW and
> have SERVER_A point to these relocated data files. Currently the data file
s
> are located on D: and the log files on L: (logical drives on SAN_OLD). We
> are thinking to place the data files on X: and the logs on Y: on SAN_NEW.
So
> in effect what we want to do is simply move the data/log files to a new
> drive location. The environment uses replication, reporting services, and
> analysis services.
> How can this be achieved? I prefer to use Query Analyzer for this task. I
> have to move all databases to SAN_NEW including the master, model etc. Do
I
> simply (from within query analyser):
> --
> - BACKUP PHASE -
> --
> - Make sure no users are attached to the SQL Server
> - stop the SQL server agent
> - Using Query Analyzer, perform the following on the MASTER, MODEL and MSD
B
> databases:
> use MASTER
> BACKUP DATABASE master
> TO DISK = 'X:\backups\master_backup.bak'
> -- now perform a backup of the model, msdb and tempdb databases
> ALTER DATABASE {model, msdbb} SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> BACKUP DATABASE {model, msdb}
> TO DISK = 'X:\backups\{model, msdb}_backup.bak'
> --
> - RESTORE PHASE -
> --
> - start command session
> - type: net stop MSSQLServer
> - start SQL in single user mode: sqlservr -m
> - from within SQL Query Analyzer execute:
> use MASTER
> -- get the logical database and log file names
> RESTORE FILELISTONLY
> FROM DISK = 'X:\backups\master_backup.bak'
> -- restore the data and log files (assuming logical names are
> master_data/master_log)
> RESTORE DATABASE master FROM DISK = 'X:\backups\master_backup.bak'
> WITH MOVE 'master_data' TO 'X:\mssql\data\master_data.mdf',
> MOVE 'master_log' TO 'Y:\mssql\logs\master_log.ldf'
> - Do the above for the MODEL and MSDB (i.e. from within SQL Query Analyzer
> using RESTORE FILELISTONLY and RESTORE DATABASE <database name> )
> - from command line issue: net start MSSQLServer
> - restart SQL Server
> My questions are:
> 1. are my steps above ok?
> 2. how do i make sure that there are no users attached whilst im making th
e
> backups?
> 3. what do i do with the TEMPDB?
> 4. when I do the RESTORE on the databases, does this simply restore over t
he
> current attached databases? I.e. does a restore of the master database
> simply overwrite the presently attached master database?
> 5. do I have to detach the databases before performing a database backup?
> 6. I am guessing i wont have any problems getting replication to kick off
> again once i've rstored the databases. Also reporting service and analysis
> service will work ok.
> Is there anything I need to know prior to this move?
> Many thanks.
> peter
>
>

request a nonadmin resource grant

I granted a db_datareader role for a user (on user db, master and msdb), and he doesn't see the files of the user db int the management studio (properties\files). What have i add him ?

is this a domain account.

if it is. check to see if he is not a

member of any restricted domain group.