Base Functionality Script

Hello, everyone:
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Thanks
ZYTA "base functionality script" is a batch of code that can be used to quickly demonstrate the successful functioning of the database from a specific application's perspective. Most commonly this is a .sql file with batched SQL commands run into the server from ISQL. For other applications, a .bat file is more appropriate because it can contain BCP and ISQL commands. This base functionality script is very application-specific, and can take many different forms. For example, on a Decision Support/Reporting system, the script may merely be a copy of a couple of your key reporting queries; for an online transaction processing (OLTP) application it may be the execution of a batch of stored procedures to execute INSERT, UPDATE, and DELETE statements. The goal is to confirm, from a gross perspective, that everything is working as intended. The base functionality script provides a nice tool for the SA or DBA to be able to see that the database is back in a viable state, without depending on the end users for verification.

bad scripts within EM Taskpad

For some of my databases, when I want to use the Taskpad
view within EM I get an Internet Explorer script error on
the URL ending sqlmmc.rll/Tabs.html
I'm using SQL2000 on the client workstation. The Server
is 7.0. Some of the databases on that server dispaly
fine, some have this problem.
I have re-installed SQL to no avail. Thining about
reinstalling Explorer. Any other ideas? Thanks Mikein EM:
View>>large Icons
then,
View>>Taskpad
Known bug in EM
--
Kevin3NF
Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001
"Mike Hoyt" <mhoyt@.affiliatedhealth.org> wrote in message
news:07d401c3c8c5$c173f360$a001280a@.phx.gbl...
> For some of my databases, when I want to use the Taskpad
> view within EM I get an Internet Explorer script error on
> the URL ending sqlmmc.rll/Tabs.html
> I'm using SQL2000 on the client workstation. The Server
> is 7.0. Some of the databases on that server dispaly
> fine, some have this problem.
> I have re-installed SQL to no avail. Thining about
> reinstalling Explorer. Any other ideas? Thanks Mike|||Kevin, Thank you so much! works like a charm.
>--Original Message--
>in EM:
>View>>large Icons
>then,
>View>>Taskpad
>Known bug in EM
>--
>Kevin3NF
>|||Change the view to something other than "Task" then select
the "Task" view again to view the Task Pane.
>--Original Message--
>For some of my databases, when I want to use the Taskpad
>view within EM I get an Internet Explorer script error on
>the URL ending sqlmmc.rll/Tabs.html
>I'm using SQL2000 on the client workstation. The Server
>is 7.0. Some of the databases on that server dispaly
>fine, some have this problem.
>I have re-installed SQL to no avail. Thining about
>reinstalling Explorer. Any other ideas? Thanks Mike
>.
>|||Thanks. That's been driving me insane since we started
testing SQL 2000. Has anyone heard if Microsoft is going
to correct this? -Phil-
>--Original Message--
>Change the view to something other than "Task" then
select
>the "Task" view again to view the Task Pane.
>>--Original Message--
>>For some of my databases, when I want to use the Taskpad
>>view within EM I get an Internet Explorer script error
on
>>the URL ending sqlmmc.rll/Tabs.html
>>I'm using SQL2000 on the client workstation. The Server
>>is 7.0. Some of the databases on that server dispaly
>>fine, some have this problem.
>>I have re-installed SQL to no avail. Thining about
>>reinstalling Explorer. Any other ideas? Thanks Mike
>>.
>.
>

backups

I am in search of a simple .bat file or .vbs script that I can use to
stop my SQL database server. I am looking to schedule a script of sorts
to initiate at a certain time every day. This script needs to be able to
stop my server and/or initiate a backup. The script should be able to be
ran remotely from the lan. can anyone help me?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"jeff wilmoth" <jeff_wilmoth@.yahoo.com> wrote in message
news:40185174$0$70300$75868355@.news.frii.net...
> I am in search of a simple .bat file or .vbs script that I can use to
> stop my SQL database server. I am looking to schedule a script of sorts
> to initiate at a certain time every day. This script needs to be able to
> stop my server and/or initiate a backup. The script should be able to be
> ran remotely from the lan. can anyone help me?

First question, why not use the online backup capabilities of SQL Server?

In any case, a simple

NET STOP MSSQLSERVER

and

NET START MSSQLSERVER

will stop and start SQL Server

If you have SQL Server Agent running, you can either do:

NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER

or

NET STOP MSSQLSERVER /Y

And then to start either

NET START MSSQLSERVER
NET START SQLSERVERAGENT

or simply

NET START SQLSERVERAGENT

which should start SQL Server first.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

backuping logins + passwords.

I can backup my logins but without passwords....using SQL 2005 , generate sql scripts...

any idea i can generate a SQL script with the passwords in cleartext? i like to recreate them in my test database later.

As MSSQL 2005 doesn't store passwords (it stores only password hashes), there is no way to get them in clear text. If you want to back up your logins, you should backup master database; if you want to thransfer them to another server, you should use the DTS "Transfer Login Task" (just don't know analogue in MSSQL 2005 SSIS, it is possible that task didn't change the name).

Anyway, maybe it would be useful : http://search.microsoft.com/results.aspx?q=Transfer+Logins+and+Passwords+Between+Instances+of+SQL+Server&l=1&mkt=en-US&FORM=QBME1

Backup/restore to different servers

Hi,
Is it possible to specify a server in a backup script & likewise specify a
restore to a different server e.g.
BACKUP DATABASE 'LIVESERVER..' + @.DBName
TO DISK = @.Path
Where LIVESERVER is the server you want to backup from?
Is this possible or must you backup & restore from the server you are in?
Many thanks for your help in advance
AntIt can only be a local database.
If you have configured a linked server, then you can use sp_executesql to execute the backup command
on the remote server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant|||Oh well,
Thanks very much none the less Tibor
Ant
"Tibor Karaszi" wrote:
> It can only be a local database.
> If you have configured a linked server, then you can use sp_executesql to execute the backup command
> on the remote server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > Hi,
> > Is it possible to specify a server in a backup script & likewise specify a
> > restore to a different server e.g.
> >
> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > TO DISK = @.Path
> >
> > Where LIVESERVER is the server you want to backup from?
> >
> > Is this possible or must you backup & restore from the server you are in?
> >
> > Many thanks for your help in advance
> >
> > Ant
>
>|||But you can always do it remotely from a client utility using osql or sqlcmd
and direct the backup file to a network drive. If you want to manage multiple
server instances, T-SQL may not be the best tool. A scripting language or
even a batch file would be more convenient.
Linchi
"Ant" wrote:
> Oh well,
> Thanks very much none the less Tibor
> Ant
> "Tibor Karaszi" wrote:
> > It can only be a local database.
> >
> > If you have configured a linked server, then you can use sp_executesql to execute the backup command
> > on the remote server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Ant" <Ant@.discussions.microsoft.com> wrote in message
> > news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > > Hi,
> > > Is it possible to specify a server in a backup script & likewise specify a
> > > restore to a different server e.g.
> > >
> > > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > > TO DISK = @.Path
> > >
> > > Where LIVESERVER is the server you want to backup from?
> > >
> > > Is this possible or must you backup & restore from the server you are in?
> > >
> > > Many thanks for your help in advance
> > >
> > > Ant
> >
> >
> >|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant
You can't quite do that.
But you can do
BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
Assuming you have share permissions setup correctly.
You can then on otherserver do
RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
Or variations thereof.
And if you're really fancy, you can have a backup job on one server that
when it's done with its backup fires off a job ON the other server to do a
restore.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi greg,
That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:
Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)
ALTER PROC MyBackupLiveData
@.Office NVARCHAR(4),
@.SecmanDB NVARCHAR(20),
@.ERPEDB NVARCHAR(20),
@.ControlDB NVARCHAR(20),
@.CompanyDB NVARCHAR(20)
AS
DECLARE @.FilePath NVARCHAR(60)
DECLARE @.FullPath NVARCHAR(60)
DECLARE @.DatePart CHAR(8)
DECLARE @.DBName NVARCHAR(30)
-- CREATE DATE TO APPEND TO BACKUP NAME
SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))
-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@.SecmanDB)
INSERT #Databases VALUES (@.ERPDB)
INSERT #Databases VALUES (@.ControlDB)
INSERT #Databases VALUES (@.CompanyDB)
-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- SET PATH & FILENAME OF BACKUP FILE
SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
SET @.FullPath = 'C:\Backups\' + @.FilePath
-- PERFORM BACKUP FROM LIVE SERVER
BACKUP DATABASE @.DBName
TO DISK = @.FullPath
-- PERFORM RESTORE TO TEST SERVER
SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
RESTORE DATABASE @.DBName
FROM DISK = @.FullPath
FETCH NEXT FROM TablesCursor INTO @.DBName
END
-- REMOVE ALL OBJECTS
CLOSE TablesCursor
DEALLOCATE TablesCursor
DROP TABLE #Databases
-- Thanks very much for your time on this
Ant
"Greg D. Moore (Strider)" wrote:
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> > Hi,
> > Is it possible to specify a server in a backup script & likewise specify a
> > restore to a different server e.g.
> >
> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> > TO DISK = @.Path
> >
> > Where LIVESERVER is the server you want to backup from?
> >
> > Is this possible or must you backup & restore from the server you are in?
> >
> > Many thanks for your help in advance
> >
> > Ant
> You can't quite do that.
> But you can do
> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> Assuming you have share permissions setup correctly.
> You can then on otherserver do
> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> Or variations thereof.
> And if you're really fancy, you can have a backup job on one server that
> when it's done with its backup fires off a job ON the other server to do a
> restore.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Hi greg,
> That's pretty much what I'm after, I created a share folder & it backusp
> ok
> but when it tries to restore it comes up with a device offline error:
Make sure the box doing the restore has access to \\etestsql\Backups\.
Remember, this needs to be in the context that the SQL Server engine runs
in.
> Below is the sql for it: ( I removed the .bak extension off the finlesname
> but didn't seem to make a differnce)
> ALTER PROC MyBackupLiveData
> @.Office NVARCHAR(4),
> @.SecmanDB NVARCHAR(20),
> @.ERPEDB NVARCHAR(20),
> @.ControlDB NVARCHAR(20),
> @.CompanyDB NVARCHAR(20)
> AS
> DECLARE @.FilePath NVARCHAR(60)
> DECLARE @.FullPath NVARCHAR(60)
> DECLARE @.DatePart CHAR(8)
> DECLARE @.DBName NVARCHAR(30)
>
> -- CREATE DATE TO APPEND TO BACKUP NAME
> SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
>
> -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> CREATE Table #Databases(DBName NVARCHAR(20))
>
> -- INSERT ALL TABLES PASSED INTO PROC
> INSERT #Databases VALUES (@.SecmanDB)
> INSERT #Databases VALUES (@.ERPDB)
> INSERT #Databases VALUES (@.ControlDB)
> INSERT #Databases VALUES (@.CompanyDB)
>
> -- CREATE CURSOR
> DECLARE TablesCursor CURSOR
> FOR
> SELECT DBName FROM #Databases
>
> OPEN TablesCursor
> FETCH NEXT FROM TablesCursor INTO @.DBName
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- SET PATH & FILENAME OF BACKUP FILE
> SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> SET @.FullPath = 'C:\Backups\' + @.FilePath
>
> -- PERFORM BACKUP FROM LIVE SERVER
> BACKUP DATABASE @.DBName
> TO DISK = @.FullPath
>
> -- PERFORM RESTORE TO TEST SERVER
> SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> RESTORE DATABASE @.DBName
> FROM DISK = @.FullPath
>
> FETCH NEXT FROM TablesCursor INTO @.DBName
> END
>
> -- REMOVE ALL OBJECTS
> CLOSE TablesCursor
> DEALLOCATE TablesCursor
> DROP TABLE #Databases
>
> -- Thanks very much for your time on this
> Ant
>
>
>
> "Greg D. Moore (Strider)" wrote:
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
>> > Hi,
>> > Is it possible to specify a server in a backup script & likewise
>> > specify a
>> > restore to a different server e.g.
>> >
>> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
>> > TO DISK = @.Path
>> >
>> > Where LIVESERVER is the server you want to backup from?
>> >
>> > Is this possible or must you backup & restore from the server you are
>> > in?
>> >
>> > Many thanks for your help in advance
>> >
>> > Ant
>> You can't quite do that.
>> But you can do
>> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
>> Assuming you have share permissions setup correctly.
>> You can then on otherserver do
>> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
>> Or variations thereof.
>> And if you're really fancy, you can have a backup job on one server that
>> when it's done with its backup fires off a job ON the other server to do
>> a
>> restore.
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
Many thanks for your help. Your answer was in fact what helped me out here.
Much Appreciated
"Greg D. Moore (Strider)" wrote:
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> > Hi Greg,
> >
> > That's pretty much what I'm after, I created a share folder & it backusp
> > ok
> > but when it tries to restore it comes up with a device offline error:
> Make sure the box doing the restore has access to \\etestsql\Backups\.
> Remember, this needs to be in the context that the SQL Server engine runs
> in.
>
>
> >
> > Below is the sql for it: ( I removed the .bak extension off the finlesname
> > but didn't seem to make a differnce)
> >
> > ALTER PROC MyBackupLiveData
> >
> > @.Office NVARCHAR(4),
> > @.SecmanDB NVARCHAR(20),
> > @.ERPEDB NVARCHAR(20),
> > @.ControlDB NVARCHAR(20),
> > @.CompanyDB NVARCHAR(20)
> >
> > AS
> >
> > DECLARE @.FilePath NVARCHAR(60)
> > DECLARE @.FullPath NVARCHAR(60)
> > DECLARE @.DatePart CHAR(8)
> > DECLARE @.DBName NVARCHAR(30)
> >
> >
> >
> > -- CREATE DATE TO APPEND TO BACKUP NAME
> > SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> > CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> > CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
> >
> >
> >
> > -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> > CREATE Table #Databases(DBName NVARCHAR(20))
> >
> >
> >
> > -- INSERT ALL TABLES PASSED INTO PROC
> > INSERT #Databases VALUES (@.SecmanDB)
> > INSERT #Databases VALUES (@.ERPDB)
> > INSERT #Databases VALUES (@.ControlDB)
> > INSERT #Databases VALUES (@.CompanyDB)
> >
> >
> > -- CREATE CURSOR
> > DECLARE TablesCursor CURSOR
> > FOR
> > SELECT DBName FROM #Databases
> >
> >
> >
> > OPEN TablesCursor
> >
> > FETCH NEXT FROM TablesCursor INTO @.DBName
> >
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > -- SET PATH & FILENAME OF BACKUP FILE
> > SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> >
> > SET @.FullPath = 'C:\Backups\' + @.FilePath
> >
> >
> > -- PERFORM BACKUP FROM LIVE SERVER
> > BACKUP DATABASE @.DBName
> > TO DISK = @.FullPath
> >
> >
> > -- PERFORM RESTORE TO TEST SERVER
> >
> > SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> >
> > RESTORE DATABASE @.DBName
> > FROM DISK = @.FullPath
> >
> >
> > FETCH NEXT FROM TablesCursor INTO @.DBName
> > END
> >
> >
> > -- REMOVE ALL OBJECTS
> >
> > CLOSE TablesCursor
> > DEALLOCATE TablesCursor
> >
> > DROP TABLE #Databases
> >
> >
> > -- Thanks very much for your time on this
> >
> > Ant
> >
> >
> >
> >
> >
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> >> > Hi,
> >> > Is it possible to specify a server in a backup script & likewise
> >> > specify a
> >> > restore to a different server e.g.
> >> >
> >> > BACKUP DATABASE 'LIVESERVER..' + @.DBName
> >> > TO DISK = @.Path
> >> >
> >> > Where LIVESERVER is the server you want to backup from?
> >> >
> >> > Is this possible or must you backup & restore from the server you are
> >> > in?
> >> >
> >> > Many thanks for your help in advance
> >> >
> >> > Ant
> >>
> >> You can't quite do that.
> >>
> >> But you can do
> >>
> >> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> >>
> >> Assuming you have share permissions setup correctly.
> >>
> >> You can then on otherserver do
> >>
> >> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> >>
> >> Or variations thereof.
> >>
> >> And if you're really fancy, you can have a backup job on one server that
> >> when it's done with its backup fires off a job ON the other server to do
> >> a
> >> restore.
> >>
> >>
> >>
> >>
> >> --
> >> Greg Moore
> >> SQL Server DBA Consulting Remote and Onsite available!
> >> Email: sql (at) greenms.com
> >> http://www.greenms.com/sqlserver.html
> >>
> >>
> >>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>

Backup/restore to different servers

Hi,
Is it possible to specify a server in a backup script & likewise specify a
restore to a different server e.g.
BACKUP DATABASE 'LIVESERVER..' + @.DBName
TO DISK = @.Path
Where LIVESERVER is the server you want to backup from?
Is this possible or must you backup & restore from the server you are in?
Many thanks for your help in advance
Ant
Oh well,
Thanks very much none the less Tibor
Ant
"Tibor Karaszi" wrote:

> It can only be a local database.
> If you have configured a linked server, then you can use sp_executesql to execute the backup command
> on the remote server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
>
>
|||But you can always do it remotely from a client utility using osql or sqlcmd
and direct the backup file to a network drive. If you want to manage multiple
server instances, T-SQL may not be the best tool. A scripting language or
even a batch file would be more convenient.
Linchi
"Ant" wrote:
[vbcol=seagreen]
> Oh well,
> Thanks very much none the less Tibor
> Ant
> "Tibor Karaszi" wrote:
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> Hi,
> Is it possible to specify a server in a backup script & likewise specify a
> restore to a different server e.g.
> BACKUP DATABASE 'LIVESERVER..' + @.DBName
> TO DISK = @.Path
> Where LIVESERVER is the server you want to backup from?
> Is this possible or must you backup & restore from the server you are in?
> Many thanks for your help in advance
> Ant
You can't quite do that.
But you can do
BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
Assuming you have share permissions setup correctly.
You can then on otherserver do
RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
Or variations thereof.
And if you're really fancy, you can have a backup job on one server that
when it's done with its backup fires off a job ON the other server to do a
restore.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi greg,
That's pretty much what I'm after, I created a share folder & it backusp ok
but when it tries to restore it comes up with a device offline error:
Below is the sql for it: ( I removed the .bak extension off the finlesname
but didn't seem to make a differnce)
ALTER PROC MyBackupLiveData
@.Office NVARCHAR(4),
@.SecmanDB NVARCHAR(20),
@.ERPEDB NVARCHAR(20),
@.ControlDB NVARCHAR(20),
@.CompanyDB NVARCHAR(20)
AS
DECLARE @.FilePath NVARCHAR(60)
DECLARE @.FullPath NVARCHAR(60)
DECLARE @.DatePart CHAR(8)
DECLARE @.DBName NVARCHAR(30)
-- CREATE DATE TO APPEND TO BACKUP NAME
SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
-- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
CREATE Table #Databases(DBName NVARCHAR(20))
-- INSERT ALL TABLES PASSED INTO PROC
INSERT #Databases VALUES (@.SecmanDB)
INSERT #Databases VALUES (@.ERPDB)
INSERT #Databases VALUES (@.ControlDB)
INSERT #Databases VALUES (@.CompanyDB)
-- CREATE CURSOR
DECLARE TablesCursor CURSOR
FOR
SELECT DBName FROM #Databases
OPEN TablesCursor
FETCH NEXT FROM TablesCursor INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- SET PATH & FILENAME OF BACKUP FILE
SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
SET @.FullPath = 'C:\Backups\' + @.FilePath
-- PERFORM BACKUP FROM LIVE SERVER
BACKUP DATABASE @.DBName
TO DISK = @.FullPath
-- PERFORM RESTORE TO TEST SERVER
SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
RESTORE DATABASE @.DBName
FROM DISK = @.FullPath
FETCH NEXT FROM TablesCursor INTO @.DBName
END
-- REMOVE ALL OBJECTS
CLOSE TablesCursor
DEALLOCATE TablesCursor
DROP TABLE #Databases
-- Thanks very much for your time on this
Ant
"Greg D. Moore (Strider)" wrote:

>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:F0107985-322B-40C5-91B7-E25A6EE76FA6@.microsoft.com...
> You can't quite do that.
> But you can do
> BACKUP DATABASE FOO to DISK ='\\otherserver\backup\backup.bkf'
> Assuming you have share permissions setup correctly.
> You can then on otherserver do
> RESTORE DATABASE FOO from DISK='c:\backup\backup.bkf'
> Or variations thereof.
> And if you're really fancy, you can have a backup job on one server that
> when it's done with its backup fires off a job ON the other server to do a
> restore.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Hi greg,
> That's pretty much what I'm after, I created a share folder & it backusp
> ok
> but when it tries to restore it comes up with a device offline error:
Make sure the box doing the restore has access to \\etestsql\Backups\.
Remember, this needs to be in the context that the SQL Server engine runs
in.
[vbcol=seagreen]
> Below is the sql for it: ( I removed the .bak extension off the finlesname
> but didn't seem to make a differnce)
> ALTER PROC MyBackupLiveData
> @.Office NVARCHAR(4),
> @.SecmanDB NVARCHAR(20),
> @.ERPEDB NVARCHAR(20),
> @.ControlDB NVARCHAR(20),
> @.CompanyDB NVARCHAR(20)
> AS
> DECLARE @.FilePath NVARCHAR(60)
> DECLARE @.FullPath NVARCHAR(60)
> DECLARE @.DatePart CHAR(8)
> DECLARE @.DBName NVARCHAR(30)
>
> -- CREATE DATE TO APPEND TO BACKUP NAME
> SET@.DatePart = '_' + CONVERT(NVARCHAR, DATEPART(yyyy,GETDATE()))+
> CONVERT(NVARCHAR, DATEPART(mm,GETDATE())) +
> CONVERT(NVARCHAR, DATEPART(dd,GETDATE()))
>
> -- TEMP TBL TO STORE DB NAMES TO BE BACKED UP
> CREATE Table #Databases(DBName NVARCHAR(20))
>
> -- INSERT ALL TABLES PASSED INTO PROC
> INSERT #Databases VALUES (@.SecmanDB)
> INSERT #Databases VALUES (@.ERPDB)
> INSERT #Databases VALUES (@.ControlDB)
> INSERT #Databases VALUES (@.CompanyDB)
>
> -- CREATE CURSOR
> DECLARE TablesCursor CURSOR
> FOR
> SELECT DBName FROM #Databases
>
> OPEN TablesCursor
> FETCH NEXT FROM TablesCursor INTO @.DBName
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- SET PATH & FILENAME OF BACKUP FILE
> SET @.FilePath = @.Office + '\' + @.DBName + @.DatePart + '.bak'
> SET @.FullPath = 'C:\Backups\' + @.FilePath
>
> -- PERFORM BACKUP FROM LIVE SERVER
> BACKUP DATABASE @.DBName
> TO DISK = @.FullPath
>
> -- PERFORM RESTORE TO TEST SERVER
> SET @.FullPath = '\\etestsql\Backups\' + @.FilePath
> RESTORE DATABASE @.DBName
> FROM DISK = @.FullPath
>
> FETCH NEXT FROM TablesCursor INTO @.DBName
> END
>
> -- REMOVE ALL OBJECTS
> CLOSE TablesCursor
> DEALLOCATE TablesCursor
> DROP TABLE #Databases
>
> -- Thanks very much for your time on this
> Ant
>
>
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi Greg,
Many thanks for your help. Your answer was in fact what helped me out here.
Much Appreciated
"Greg D. Moore (Strider)" wrote:

> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:06B83767-9E27-4B0A-979F-C9CB6F398EA1@.microsoft.com...
> Make sure the box doing the restore has access to \\etestsql\Backups\.
> Remember, this needs to be in the context that the SQL Server engine runs
> in.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

OR

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

OR

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Backup/Restore Script

am new at this and I am stuck...
I am trying to backup an entire database and restore it on the same SQL
server with a different name.
I have this in the Query Analyzer
BACKUP DATABASE merchant
TO DISK = 'c:\merchant.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\merchant.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\merchant.bak'
WITH MOVE 'merchant' TO 'c:\testdb.mdf',
MOVE 'merchant_log' TO 'c:\testdb.ldf'
GO
I get the following error
Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file
13.
Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13.
BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5
Logical file 'merchant' is not part of database 'TestDB'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
Can anyone help with this?
I want to copy the entire database called MERCHANT to another databaseWell that because it looks like your Data files logical name is
'Struky3_Data' and the Log files logical name is really 'Struky3_Log'. So
your restore script needs to specify those for the Logical names.
Andrew J. Kelly SQL MVP
"Brian Branco" <bbranco@.bncconsulting.com> wrote in message
news:OTAtoi5HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> am new at this and I am stuck...
> I am trying to backup an entire database and restore it on the same SQL
> server with a different name.
> I have this in the Query Analyzer
> BACKUP DATABASE merchant
> TO DISK = 'c:\merchant.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'c:\merchant.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\merchant.bak'
> WITH MOVE 'merchant' TO 'c:\testdb.mdf',
> MOVE 'merchant_log' TO 'c:\testdb.ldf'
> GO
> I get the following error
> Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file
> 13.
> Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13.
> BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5
> Logical file 'merchant' is not part of database 'TestDB'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 5
> RESTORE DATABASE is terminating abnormally.
>
> Can anyone help with this?
> I want to copy the entire database called MERCHANT to another database
>
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary Blakely
You can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
--
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
--
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
--
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
--
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>> How can I backup and restore just stored procedures. I know in the old
>> Enterprise Manager it was easy to script just all stored procedures so I
>> could do it that way but in the new Management Studio Stored Procedures
>> are now one of the object types that can be selected.
>> Any ways to get this done?
>> --
>> Regards,
>> Gary Blakely
>>
>