Basic Question about password and logon command

I have running SQL queries all day and have been getting tired of selecting the DATA SOURCE NAME and then having to type in the appropriate password. What can I add to my SQL command to do this for me?
Thanks for the help!
NormDo you mean like a connect line in your script?|||Originally posted by exdter
Do you mean like a connect line in your script?

Yes, exactly|||Is this what you mean?
The first line of your script should be
conn[ect] user/password/database
The problem with this is that your password won't be encrypted, so someone can see it.
I hope this helps.|||Originally posted by exdter
Is this what you mean?
The first line of your script should be
conn[ect] user/password/database
The problem with this is that your password won't be encrypted, so someone can see it.
I hope this helps.

Forgive my ignorance but I don't think I asked for the right thing.

Here's what I'm looking at....

I'm in Access and I run an SQL query such as:

select COUNT(*) from employee_data where title = 'Programmer'

and the next thing you know is a window pops up where I need to click on the "Machine Data Source" tab and then the appropriate "Data Source Name" which then has me type in my password.

Thanks for the help.

Norm|||Oh, sorry. I can't help with that one.
I don't know Access.

backups OK in MSDE2000 but won't work in SQLexpress

I'm migrating our app from VS2003/MSDE2000 to VS2005/SQLexpress. Generally no
problems, but the backup of database always fails.
The backup command is
"BACKUP DATABASE icespy5 TO DISK='C:\testbackup'"
I get:
"Cannot open backup device 'C:\testbackup'. Operating system error 5(error not
found). BACKUP DATABASE is terminating abnormally."
The same command works fine with the MSDE database. I have tried other
backup destinations but with no success. I have full admin rights; backup
fails in both debug and release environments.
Any ideas what's wrong?
Evidently permissions are not granted! I can back up to the folder where the
..mdf file is stored, so that will be fine, thanks. However it would be useful
to know how to grant SQL permission to an alternative folder.
Thanks for your help.
"Andrea Montanari" wrote:

> hi,
> quilkin wrote:
> please verify the Windows account the SQLExpress instance is running on has
> been granted adeguate NTFS permissions on the destination path..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||quilkin,
Try changing SQL service to log in as a Local System instead of a Local or Network Service
Jeff
Check Point Software
================================================== ===
You must be using Outlook Express/Windows Mail or some other type of newsgroup reader to
see and download the file attachment(s). If you are not using a reader, follow
the link below to setup Outlook Express. Click on "Open with newsreader"
under the MS Retail Management System on the right.
http://tinyurl.com/75bgz
================================================== ===
"quilkin" <quilkin@.discussions.microsoft.com> wrote in message news:D58DA277-121E-49AE-96B1-7F91C674DD8D@.microsoft.com...
Evidently permissions are not granted! I can back up to the folder where the
.mdf file is stored, so that will be fine, thanks. However it would be useful
to know how to grant SQL permission to an alternative folder.
Thanks for your help.
"Andrea Montanari" wrote:

> hi,
> quilkin wrote:
> please verify the Windows account the SQLExpress instance is running on has
> been granted adeguate NTFS permissions on the destination path..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>

BackupDiskFile::CreateMedia: Backup device failed to create

Hi,
In SQL Query Analyzer, I run the following command.
Backup database sharepoint_webspace To sharepoint_webspace with init,
name='sharepoint_webspace_full'.
The result was,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'sharepoint_webspace'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
SQL server log shows:
=== BACKUP failed to complete the command Backup database SharePoint_WebSpace To
sharepoint_webspace with init, name='SharePoint_WebSpace_full'
BackupDiskFile::CreateMedia: Backup device
'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
system error=32 (The process cannot access the file because it is being used
by another process.)
===
The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
old backed up stuff. It's over 11G right now.
Question I have:
How should I know what process is using the sharepoint_webspace.BAK file? I
checked Process Info in EM, there are four processes associated with the
sharepoint_webspace database, but three of them are connections from the IIS
web server (Network Library: TCP/IP). The other one is the Backup command
I'm running in SQL Query Analyzer (Network Library).
How should I get the problem fixed?
Thanks much in advance,
BingNever mind. I've figured it out. Another process is trying to write the
backup disk file to tapes but no more tapes available.
Bing
"bing" wrote:
> Hi,
> In SQL Query Analyzer, I run the following command.
> Backup database sharepoint_webspace To sharepoint_webspace with init,
> name='sharepoint_webspace_full'.
> The result was,
> Server: Msg 3201, Level 16, State 1, Line 1
> Cannot open backup device 'sharepoint_webspace'. Device error or device
> off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> SQL server log shows:
> ===> BACKUP failed to complete the command Backup database SharePoint_WebSpace To
> sharepoint_webspace with init, name='SharePoint_WebSpace_full'
> BackupDiskFile::CreateMedia: Backup device
> 'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
> system error=32 (The process cannot access the file because it is being used
> by another process.)
> ===> The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
> old backed up stuff. It's over 11G right now.
> Question I have:
> How should I know what process is using the sharepoint_webspace.BAK file? I
> checked Process Info in EM, there are four processes associated with the
> sharepoint_webspace database, but three of them are connections from the IIS
> web server (Network Library: TCP/IP). The other one is the Backup command
> I'm running in SQL Query Analyzer (Network Library).
> How should I get the problem fixed?
> Thanks much in advance,
> Bing

BackupDiskFile::CreateMedia: Backup device failed to create

Hi,
In SQL Query Analyzer, I run the following command.
Backup database sharepoint_webspace To sharepoint_webspace with init,
name='sharepoint_webspace_full'.
The result was,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'sharepoint_webspace'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
SQL server log shows:
===
BACKUP failed to complete the command Backup database SharePoint_WebSpace To
sharepoint_webspace with init, name='SharePoint_WebSpace_full'
BackupDiskFile::CreateMedia: Backup device
'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
system error=32 (The process cannot access the file because it is being used
by another process.)
===
The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
old backed up stuff. It's over 11G right now.
Question I have:
How should I know what process is using the sharepoint_webspace.BAK file? I
checked Process Info in EM, there are four processes associated with the
sharepoint_webspace database, but three of them are connections from the IIS
web server (Network Library: TCP/IP). The other one is the Backup command
I'm running in SQL Query Analyzer (Network Library).
How should I get the problem fixed?
Thanks much in advance,
Bing
Never mind. I've figured it out. Another process is trying to write the
backup disk file to tapes but no more tapes available.
Bing
"bing" wrote:

> Hi,
> In SQL Query Analyzer, I run the following command.
> Backup database sharepoint_webspace To sharepoint_webspace with init,
> name='sharepoint_webspace_full'.
> The result was,
> Server: Msg 3201, Level 16, State 1, Line 1
> Cannot open backup device 'sharepoint_webspace'. Device error or device
> off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> SQL server log shows:
> ===
> BACKUP failed to complete the command Backup database SharePoint_WebSpace To
> sharepoint_webspace with init, name='SharePoint_WebSpace_full'
> BackupDiskFile::CreateMedia: Backup device
> 'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
> system error=32 (The process cannot access the file because it is being used
> by another process.)
> ===
> The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
> old backed up stuff. It's over 11G right now.
> Question I have:
> How should I know what process is using the sharepoint_webspace.BAK file? I
> checked Process Info in EM, there are four processes associated with the
> sharepoint_webspace database, but three of them are connections from the IIS
> web server (Network Library: TCP/IP). The other one is the Backup command
> I'm running in SQL Query Analyzer (Network Library).
> How should I get the problem fixed?
> Thanks much in advance,
> Bing

BackupDiskFile::CreateMedia: Backup device failed to create

Hi,
In SQL Query Analyzer, I run the following command.
Backup database sharepoint_webspace To sharepoint_webspace with init,
name='sharepoint_webspace_full'.
The result was,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'sharepoint_webspace'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
SQL server log shows:
===
BACKUP failed to complete the command Backup database SharePoint_WebSpace To
sharepoint_webspace with init, name='SharePoint_WebSpace_full'
BackupDiskFile::CreateMedia: Backup device
'z:\data\MYSQL\BACKUP\sharepoint_webspac
e.BAK' failed to create. Operating
system error=32 (The process cannot access the file because it is being used
by another process.)
===
The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace
.BAK has some
old backed up stuff. It's over 11G right now.
Question I have:
How should I know what process is using the sharepoint_webspace.BAK file? I
checked Process Info in EM, there are four processes associated with the
sharepoint_webspace database, but three of them are connections from the IIS
web server (Network Library: TCP/IP). The other one is the Backup command
I'm running in SQL Query Analyzer (Network Library).
How should I get the problem fixed?
Thanks much in advance,
BingNever mind. I've figured it out. Another process is trying to write the
backup disk file to tapes but no more tapes available.
Bing
"bing" wrote:

> Hi,
> In SQL Query Analyzer, I run the following command.
> Backup database sharepoint_webspace To sharepoint_webspace with init,
> name='sharepoint_webspace_full'.
> The result was,
> Server: Msg 3201, Level 16, State 1, Line 1
> Cannot open backup device 'sharepoint_webspace'. Device error or device
> off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> SQL server log shows:
> ===
> BACKUP failed to complete the command Backup database SharePoint_WebSpace
To
> sharepoint_webspace with init, name='SharePoint_WebSpace_full'
> BackupDiskFile::CreateMedia: Backup device
> 'z:\data\MYSQL\BACKUP\sharepoint_webspac
e.BAK' failed to create. Operatin
g
> system error=32 (The process cannot access the file because it is being us
ed
> by another process.)
> ===
> The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace
.BAK has some
> old backed up stuff. It's over 11G right now.
> Question I have:
> How should I know what process is using the sharepoint_webspace.BAK file?
I
> checked Process Info in EM, there are four processes associated with the
> sharepoint_webspace database, but three of them are connections from the I
IS
> web server (Network Library: TCP/IP). The other one is the Backup command
> I'm running in SQL Query Analyzer (Network Library).
> How should I get the problem fixed?
> Thanks much in advance,
> Bing

Backup/Restore databse - "physical_backup_device_name"

Hi,
I ran into a command today like
RESTORE DATABASE testdb FROM DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
WITH FILE = 7....
Does anyone know what the letter "N", right before the
physical_backup_device_name 'E:\MsSQLServer\Backup\MyDb.bak', is
for?
Thanks in advance!
TonyIt indicates that the following string is Unicode.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<xtonysun@.gmail.com> wrote in message
news:1172181091.530376.165090@.v45g2000cwv.googlegroups.com...
> Hi,
> I ran into a command today like
> RESTORE DATABASE testdb FROM DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
> WITH FILE = 7....
> Does anyone know what the letter "N", right before the
> physical_backup_device_name 'E:\MsSQLServer\Backup\MyDb.bak', is
> for?
> Thanks in advance!
> Tony
>|||On Feb 22, 4:17 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> It indicates that the following string is Unicode.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <xtony...@.gmail.com> wrote in message
> news:1172181091.530376.165090@.v45g2000cwv.googlegroups.com...
>
> > Hi,
> > I ran into a command today like
> > RESTORE DATABASE testdb FROM DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
> > WITH FILE = 7....
> > Does anyone know what the letter "N", right before the
> > physical_backup_device_name 'E:\MsSQLServer\Backup\MyDb.bak', is
> > for?
> > Thanks in advance!
> > Tony- Hide quoted text -
> - Show quoted text -
Thanks!

Backup/Restore databse - "physical_backup_device_name"

Hi,
I ran into a command today like
RESTORE DATABASE testdb FROM DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
WITH FILE = 7....
Does anyone know what the letter "N", right before the
physical_backup_device_name 'E:\MsSQLServer\Backup\MyDb.bak', is
for?
Thanks in advance!
TonyIt indicates that the following string is Unicode.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<xtonysun@.gmail.com> wrote in message
news:1172181091.530376.165090@.v45g2000cwv.googlegroups.com...
> Hi,
> I ran into a command today like
> RESTORE DATABASE testdb FROM DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
> WITH FILE = 7....
> Does anyone know what the letter "N", right before the
> physical_backup_device_name 'E:\MsSQLServer\Backup\MyDb.bak', is
> for?
> Thanks in advance!
> Tony
>|||On Feb 22, 4:17 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> It indicates that the following string is Unicode.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <xtony...@.gmail.com> wrote in message
> news:1172181091.530376.165090@.v45g2000cwv.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Thanks!

Backup/Restore databse - "physical_backup_device_name"

Hi,
I ran into a command today like
RESTORE DATABASE testdb FROM DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
WITH FILE = 7....
Does anyone know what the letter "N", right before the
physical_backup_device_name 'E:\MsSQLServer\Backup\MyDb.bak', is
for?
Thanks in advance!
Tony
It indicates that the following string is Unicode.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<xtonysun@.gmail.com> wrote in message
news:1172181091.530376.165090@.v45g2000cwv.googlegr oups.com...
> Hi,
> I ran into a command today like
> RESTORE DATABASE testdb FROM DISK = N'E:\MsSQLServer\Backup\MyDb.bak'
> WITH FILE = 7....
> Does anyone know what the letter "N", right before the
> physical_backup_device_name 'E:\MsSQLServer\Backup\MyDb.bak', is
> for?
> Thanks in advance!
> Tony
>
|||On Feb 22, 4:17 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> It indicates that the following string is Unicode.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <xtony...@.gmail.com> wrote in message
> news:1172181091.530376.165090@.v45g2000cwv.googlegr oups.com...
>
>
>
>
> - Show quoted text -
Thanks!

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and the
n
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and t
hen
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and l
og
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:
> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GO
Backup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

Backup using TSQL Question

Ladies / Gentlemen
When I issue the command to 'BACKUP' my Database it does so
with no problem.
However it appends to the last backup. How do I make it
overwrite it.
I am using the following code
if not exists (select *
from master.dbo.sysdevices
where name = 'SACS2' )
begin
USE master
EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\SACS2.dat'
end
BACKUP DATABASE SACS TO SACS2
Mark MossMark Moss wrote:
> Ladies / Gentlemen
>
> When I issue the command to 'BACKUP' my Database it does s
o
> with no problem.
> However it appends to the last backup. How do I make it
> overwrite it.
>
> I am using the following code
> if not exists (select *
> from master.dbo.sysdevices
> where name = 'SACS2' )
> begin
> USE master
> EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft S
QL
> Server\MSSQL\BACKUP\SACS2.dat'
> end
> BACKUP DATABASE SACS TO SACS2
>
> Mark Moss
>
Include WITH INIT in the backup command, or write to a different file...
Found this in Books Online, under the BACKUP command...|||Try:
BACKUP DATABASE SACS TO SACS2 WITH INIT
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:efN9mkGnGHA.4620@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> When I issue the command to 'BACKUP' my Database it does so
> with no problem.
> However it appends to the last backup. How do I make it
> overwrite it.
>
> I am using the following code
> if not exists (select *
> from master.dbo.sysdevices
> where name = 'SACS2' )
> begin
> USE master
> EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\SACS2.dat'
> end
> BACKUP DATABASE SACS TO SACS2
>
> Mark Moss
>