Need Help In Automating SQL Server Backup...!!!
May 20, 2005Hi,
View 3 Replies
ADVERTISEMENT
Automating A Backup And Restore Across Servers
Jan 10, 2005Hi,
I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?
Thanks,
Maria
Automating Access To SQL Server
Oct 14, 2005We're trying to put a view of data maintained in desktop Access databases online and into SQL Server.The desktop Access system uses separate databases instead of tables within one database, It's a strange design, but it can't be changed.We have been importing all of the separate databases into a single, new Access database, then upsizing the new databse to SQL Server, then uploading it.This is not going to work long term, because we are stuck with a 250 mB Access database to upsize and upload, when we never need to update more than 2 or 3 of the tables and upload more than 2 mB.We'd like to be able to upload only the tables -- preferably the Access *.mdb's -- that have changed, and then replace the SQL Server tables with the new information. And we'd like to automate it as much as possible, without upsizing Wizardy.I don't know where to even begin looking for information about how this might be done.Any suggestions would be deeply appreciated.- Tinker
View 4 Replies View RelatedAutomating Export From One Server Import To Another
Oct 18, 2015Both using SSMS 2012 Enterprise on Windows 2012
I am able to import - export manually, how can you schedule this import export in a job? I want the table to drop and be rebuilt each time it runs. Since linking servers isn't recommended I want go with that approach.
Automating Configuration Of Reporting Server
Jan 30, 2008
Is there anyway to automate the configuring Reporting Server after is installed. I have many servers that I need to install SQL 2005 with Analysis servcies and Reporting Server and it will be good if I could run a tool to configure reporting server with some default values.
Thanks
NEWBIE Needs Help Automating Data From Another Server
Sep 7, 2007Hi,
I am able to do these actions interactively from SQL 2005 (not developers nor enterprise edition, just using SQL 2005 Mgmt Studio) and want to "script/batch" them so I can have them automatically run at a pre selected time.
First: I am able to delete the table by performing a right click on the table, then click Delete from Mgmt Studio SQL 2005. I verify the table is completely gone with a refresh. (I pulled the code that did this ..... DROP TABLE etc. to Notepad)
2nd: I am able to import the table (again from Mgmt Studio SQL 2005) and have saved this action as a SSIS. Execute the script and "waLa" I have all 17K rows of data. I pulled this create table code into notepad also.
Now I put the code of both of the above actions together (drop table and create table) into one SQL query and execute it. This does not give me the same results of above, instead my table is blank now.
Maybe there is a better way. The business problem I am attempting to solve: I am refreshing the data in a as/400 table weekly. I want that refreshed data to be available in the SQL2005 database without my having to press buttons first thing Monday morning. Can any one help? Thanks in advance.
Below is the Code:
USE [400kas]
GO
/****** Object: Table [dbo].[navar100] Script Date: 09/07/2007 16:09:04 ******/
DROP TABLE [dbo].[navar100]
GO
USE [400kas]
GO
/****** Object: Table [dbo].[Query] Script Date: 09/07/2007 16:12:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[navar100](
[CMPNO] [decimal](3, 0) NOT NULL,
[ARTDT] [datetime] NOT NULL,
[AUDDT] [datetime] NOT NULL,
[ARDDT] [datetime] NOT NULL,
[CCUS#] [decimal](6, 0) NOT NULL,
[CCNAM] [nvarchar](25) NOT NULL,
[CUSNO] [decimal](6, 0) NOT NULL,
[CNAME] [nvarchar](25) NOT NULL,
[SHPNO] [decimal](4, 0) NOT NULL,
[ARRCD] [nvarchar](1) NOT NULL,
[AUDUS] [nvarchar](10) NOT NULL,
[INVNO] [decimal](6, 0) NOT NULL,
[CUSPO] [nvarchar](15) NOT NULL,
[REFNO] [decimal](6, 0) NOT NULL,
[COMNT] [nvarchar](10) NOT NULL,
[SHPPO] [nvarchar](15) NOT NULL,
[AMONT] [decimal](13, 2) NOT NULL,
[AMOUNT] [decimal](24, 8) NOT NULL,
[REMAN] [decimal](13, 2) NOT NULL,
[INREG] [decimal](3, 0) NOT NULL,
[INSAL] [decimal](3, 0) NOT NULL,
[TMCOD] [nvarchar](2) NOT NULL,
[CRHLD] [nvarchar](1) NOT NULL,
[CRLIM] [decimal](13, 0) NOT NULL,
[CRDAY] [decimal](3, 0) NOT NULL,
[TCRCD] [nvarchar](3) NOT NULL,
[TEXRT] [decimal](11, 6) NOT NULL,
[R1RGL] [decimal](13, 2) NOT NULL,
[TAXAM] [decimal](13, 2) NOT NULL,
[TFRTX] [decimal](13, 3) NOT NULL,
[TFRGT] [decimal](13, 2) NOT NULL,
[TSPCH] [decimal](13, 2) NOT NULL,
[SPCST] [decimal](13, 2) NOT NULL,
[IRPFT] [decimal](13, 2) NOT NULL
) ON [PRIMARY]
Automating The Importation Of An Oracle Table Into Sql Server 2000
Aug 25, 2004Hi guys,
I figure this should not be a complex one. I know how to manually pull in data from Oracle 9i into SQL Server 2000 using DTS. However this is my issue.....
I simply want to automate the pulling in of data from 1 table in my ORACLE 9i database into another table in my Sql Server 200. I was hoping I could simple write a stored procedure that would sort of utilize a dblink like in ORACLE and then schedule that procedure. Is this feasible in Sql Server, and how would one go about setting this automated import up????
Thanks in Advance all............
'Wale
SQL 2012 :: Automating Server Patching For More Than 10000 Instances
Nov 4, 2014Is there any Best Practice, 3rd Party Tool for Automating SQL Server Patching for around 10000+ SQL instances.
It is very difficult to do the same manually because each server will consume atleast 1-2 hours and if this is completely done manually then n number of people might be required for accomplishing this task which doesn't looks feasible at all.
SQL Server 2014 :: Automating Random Inserts Into A Memory Optimized Table
Jan 28, 2015I have this table
CREATE TABLE [Sales].[Test_inmem]
(
[c1] [int] NOT NULL,
[c2] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModifiedDate] [datetime2](7) NOT NULL CONSTRAINT [IMDF_Test_ModifiedDate] DEFAULT (sysdatetime()),
[Code] ....
I have to generate 1000000 random records into it. I tried various ways to insert records, but not being a developer could not do it. I hope to make the C1 as a serial number, C2 can be anything, C3 I want to be the timestamp.
Automating Taking Data From Sql Server 2000 Onto Excel 2003 Spreasheet
Jun 18, 2007
I would like to know recommendations on automate the following:
1. I want to know how to take data from various sql server 2000 data queries and load the data onto excel spreadsheets in an automated method. Some of the data loaded into the excel spreadsheets load detail data and some load data into pivot tables.
Is there any way that the data can be taken from sql server to the excel spreadsheets using odbc connections?
2. Some of the data taken from sql server 2000 database is loaded into pdf files. Is there any way this process can be automated?
Thanks!
Automating Using DTS
Mar 14, 2006I am trying to create a process to automate the FTP process and then IMPORT to a table. Once Import is complete, I would like to move the file from the FTP server and rename it. I tested the "commands" via CMD and it works. I then created a store procedure with the DOS commands to rename/move the source file. The command executes and deletes the file from the source, however, the copied files to a different destination disappeared. I did a search in my local hard drive(testing using my local folders), but can't find where the copied/renamed file went.
HEre is the portion of the commands:
SET @cmd ='Copy "C:Documents and SettingsegllareMy DocumentsBoxtestData.txt" "C:Documents and SettingsegllareMy DocumentsArchiveCopybox.%random%.bak"'
EXEC master..xp_cmdshell @cmd
GO
Can you please tell me what's wrong with this,
or direct me to a sample store procedure I can model..
TIA
Willie Llarena
Automating
Mar 17, 2008Hi team
Could anyone tell me if and how can i automate processing in sql server. Example, i would like to automate reading from a source file, populating it into the respective tables and then running a view to process desired tables. I already did all the above manually. I just want to know if there is a way of automating all this process.
Automating Backups With TSM
Apr 28, 2006I was wondering if anyone can share the procedure(s) used in setting up an automated MS SQL database backup through tivoli Storage Manager
( 5.2.7 )..........?
Automating SQL Profiler
Jan 23, 2007The powers at be have decided that they would like to automatically run a trace on one of our analysis servers when it processes a cube in the early hours of the morning. Now I have no problem creating a SQL Profile to run and store the results in a database table for them, but I have no idea how to automate it so that it runs everyday, any help would be greatly appreciated. Apologise for not putting this in the correct forum.
View 1 Replies View RelatedAutomating Query
May 16, 2007I am looking for the best method of automating an INSERT query in SQL Server 2005
Code:
--------------------------------------------------------------------------------
'INSERT INTO SaltInvWhOpen (StockCode, Warehouse, TrnMonth, TrnYear, OpenBalQty, OpenBalCost)SELECT StockCode, Warehouse, Month(GETDATE()) AS TrnMonth, YEAR(GETDATE()) AS TrnYear, OpenBalQty12, OpenBalQty12 * UnitCost AS Expr1FROM InvWarehouse
--------------------------------------------------------------------------------
In older versions I would have used a DTS package (It's been a long time and I'd have had to use a book to guide me through it), but as that is gone now, I am unsure where to even begin looking.
If someone could assist by giving me the current method, and dare I ask, walk me through it, the help would be much appreciated.
Thank you!
Julia
Automating Defrag.exe
Jul 19, 2007Isn't there any way to automate Windows defrag? Isn't there any undocumented parameters for DEFRAG.EXE?
Canada DBA
Automating SQL Setup.
Jul 20, 2005Is there a way that I can Automate SQL Setup via C#, PERL or VB?
View 2 Replies View RelatedAutomating Certain Set Up Tasks?
Apr 29, 2015After SQL Server Express 2012 is installed, is there a way to automate setting up a few options? I need to enable the option for both windows and sql server authentication. Can this be done with Powershell or other tool? This is a situation where the user has absolutely no computer skills and there is no one available in their office that can set the options manually. I thought some sort of script would be useful for this.
View 2 Replies View RelatedAutomating Db Backups
Jul 28, 2006Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....
View 8 Replies View RelatedSQL Server 2008 :: Backup Running Long And Backup Threads Show Suspended
Feb 18, 2015SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
SQL Server Admin 2014 :: Restore DB With Full Backup And Transactional Log Backup
Aug 3, 2015Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
Can Windows 2003 Server Backup Utility Be Used To Backup A SQL 2000 Dbase
Dec 28, 2007Windows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
SQL Server 2008 :: Count Number Of Backup Files Deleted From A Default Backup Location
Mar 13, 2015I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
Automating Ftp Command Line
May 1, 2001hi I would appreciate if someone demonstrate how to automate ftp in a command line from within a batch file. I do want to move certain files from one server to another via ftp command line in an automatic fashion via running the batch periodically.
thanks
Ali
Automating Export Using Dts (urgent)
Feb 15, 2001i want to automate the exporting of data from an excel database to sql using dts.i don't want this to do manually.can u help me out in solving this.what are the things i have to do for this ?expecting u'r earliest reply
View 2 Replies View RelatedAutomating Table Defrag
May 26, 1999I'd like to build a process that will identify all the tables in a database with a scan density less than 100% and generate 'dbcc dbreindex' statements for them. The 'dbcc showcontig' command displays the information I want but I don't know how to access this information from within a script. Any ideas out there?
View 5 Replies View RelatedAutomating Transaction Log Dumps
May 5, 1999I am testing a procedure to automate the transaction log dump. I am following the steps located in Chapter 22 of the Microsoft SQL Server Administrator's Companion ("Automatic Transaction Log Dumps Using Performance Monitor"). The alert in Performance Monitor appears to be starting when the log is 75% full, but the alert is not firing off the file that contains the dump transaction sql command. For the 'Run Program on Alert' box this is what I have: isql -Ssvrname -Usa -P -id:appsmssqlinndump.sql
The dump.sql file contains: 'dump transaction pubs with no_log'
I have also tried the following 4 steps: 1) Created a SQL Alert Messsage, 2) Created an NT Performance Monitor Threshold Alert to run sqlalrtr to issue a certain error when the pubs log is 75% full, 3) Created a TSQL Task, and 4) Created a SQL Server Alert to run the Task created in step 3. This appears to do the same thing. The Alert is fired off, but the Task is never executed. Note: I am able to execute the task from within the Schedule Tasks Window.
I am using Standard Security with SQL Server 6.5 (sp5a) running on NT4.
Thanks for you help in advance.
Automating Data Entry Into TM.PTA.00 Or Any Other
Apr 10, 2007question... i heard a bit about Control Macro Generator but have not found much reading material on it. here at our company we have employees fill out a weekely timesheet and typically have clerks enter the information into the TM.PTA.00 (Project Timesheet with Rate/Amount Entry) and submit it that way. say we have this timesheet in a nice little excel sheet with columns that match the input boxes on the form, how can we get it to automatically input the data instead of having a person sit there and type it all in?
View 3 Replies View RelatedAutomating Manual Process
Nov 28, 2007I would like to automate a simple process which involves trucating a table and importing records back in. What would be the simplest solution to automate this kind of process? I'd like to run this process twice a day.
Thanks!
How Hard Would This Be - Automating With Vbscript ?
Oct 5, 2007How can I do this with vbscript, or C# ?
- Copy backup files down from a network share, into the data directory of my local sql 2005 instance
- perform a restore using the files copied from above
- Execute a dts package
More info:
Our databases are scripted and exist on the typical development, and testing enviroments. So as I get ready to start a new application, I want my local sql instance to be updated based on structure changes as well as data. So I have to apply the changes from the scripted sources and pull over the data. I would naturally like to automate this.
ideas / suggestions welcome
How SQL SERVER 2005 Backup && ArcServe Backup Coexist ??
Apr 15, 2008I have only one tape drive.
My server is using the SQL server 2005 backup tool and arcserve backup tool to backup different files .
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use SQL server 2005 backup tool.
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use arcserve backup tool.
It seems that the tape device resource is held by softwares even the tape drive is not in use.
What should I do to make the tape drive shared by different softwares?
Regards,
Manuel
Automating A Custom Stored Procedure
Jul 13, 2000Can I automate a stored procedure I made myself?
If so could someone kindly tell me how as I have tried to no avail.
Thanks in advnace
Automating Stored Procedure Execution
Jun 6, 20001) Is it possible to run stored procedures at specified intervals without
using the job system (through T-SQL)? I want the schedule to be
independent of the MSDB database in case of temporary failures, etc.
2) Would extended stored procedures be helpful in this scenario?
Thanks
ziggy